Connect using Entity Framework Core
Note
This feature is only supported in SingleStoreDB clusters running SingleStoreDB version 7.8.
Entity Framework (EF) Core serves as an object-relational mapper (O/RM) that provides an automated mechanism to the developers to access and store data in a database using .NET objects. The EntityFrameworkCore.SingleStore provider allows you to use EF Core with your SingleStoreDB databases. This provider is built on top of the SingleStore Connector for .NET and .NET Core.
Connect with SingleStoreDB using EF Core
To connect with SingleStoreDB:
Install the provider: Run the following command:
dotnet add package EntityFrameworkCore.SingleStore
The following reference is added to the .csproj file in your project:
<PackageReference Include="EntityFrameworkCore.SingleStore" Version="6.0.2-beta" />
If the package reference is not present in the .csproj file in your project, add it.
Configure the service: Add
EntityFrameworkCore.SingleStore
to the services configuration in the Startup.cs file of your .NET Core project. Here's a sample:public class Startup { public void ConfigureServices(IServiceCollection services) { // Replace with your connection string var connectionString = "server=svchost;user=root;password=passw0rd;database=dbTest"; /* Replace with your server version and type. Alternatively, you can use the 'ServerVersion.AutoDetect(connectionString)' function. */ var serverVersion = new SingleStoreServerVersion(new Version(7, 8, 0)); // Replace 'DbContext' with the name of your own DbContext derived class. services.AddDbContext<DbContext>( dbContextOptions => dbContextOptions .UseSingleStore(connectionString, serverVersion)); } }
For more information, refer to Configuration Options.
Scaffolding (Reverse Engineering)
You can scaffold (reverse engineer) entity type classes and a DbContext
class based on a database using the dotnet ef dbcontext scaffold
command. To run scaffolding commands, you must have .NET Core CLI tools and the EntityFrameworkCore.SingleStore
package. Use the following command to install dotnet ef
:
dotnet tool install --global dotnet-ef
To verify the installation, run the following command:
dotnet ef
To generate code for a DbContext
for a SingleStoreDB database using EF Core, run the following command:
dotnet ef dbcontext scaffold <connection-string> "EntityFrameworkCore.SingleStore"
Here's an example:
dotnet ef dbcontext scaffold "Server=svchost;Port=3306;Uid=root;Pwd=passw0rd;database=acme" "EntityFrameworkCore.SingleStore"
Unsupported Features/Limitations
SingleStoreDB does not support foreign keys and referential integrity. Therefore, while performing EF Core migrations, foreign keys are not created between the entities.
EF.Functions.Collate
is not supported, because you cannot change a column's collation in a SingleStoreDB database.Additional unsupported features:
Savepoints
Unique indexes/constraints
Check constraints
Spatial data types
Full-text indexes
JSON
AUTO_INCREMENT
can only be used on aBIGINT
column.By default, max key length is set to
3072
(same as MySQL). Contact SingleStore Support to increase the length. SingleStoreDB does not impose any limitations on the key length.
Refer to Unsupported MySQL Features for more information.
Example
The following example performs CRUD operations in SingleStoreDB using EF Core:
Clone the following GitHub repository: https://github.com/singlestore-labs/start-with-singlestore-csharp-orm.git.
Run the following command in the command-line:
dotnet restore
Update the placeholders in the connection string in the appsettings.Development.json file with the connection configuration for your SingleStoreDB database:
"DefaultConnection": "Server=<hostname-or-ip-address>;Port=3306;Uid=<user>;Pwd=<password>;database=<database-name>"
Here's a sample:
{ "Logging": { "LogLevel": { "Default": "Debug", "System": "Information", "Microsoft": "Information" } }, "ConnectionStrings": { "DefaultConnection": "Server=svchost;Port=3306;Uid=root;Pwd=passw0rd;database=acme" } }
Run the commands in the init.sql file in your SingleStoreDB database:
singlestore -h svchost -P 3306 -u admin -p passw0rd -D acme < init.sql
You can also run the commands in the init.sql file directly in your SingleStoreDB database.
Run the following command to build the project and all of its dependencies:
dotnet build
The Runner.cs file in this repository defines basic CRUD operations using EF Core. Run the following command to perform these operations:
dotnet run
Here are snippets from the output that highlight each operation:
Create Operation:
Executed DbCommand (515ms) [Parameters=[@p0='?' (Size = 4000), @p1='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='30'] INSERT INTO `messages` (`Content`, `CreateDate`) VALUES (@p0, @p1); SELECT `Id` FROM `messages` WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID(); Inserted row id 3377699720527873
Read Operation:
Executed DbCommand (390ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT `m`.`Id`, `m`.`Content`, `m`.`CreateDate` FROM `messages` AS `m` ORDER BY `m`.`Id` Read all rows: 3377699720527873, Updated row
Update Operation:
Executed DbCommand (519ms) [Parameters=[@p1='?' (DbType = Int64), @p0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30'] UPDATE `messages` SET `Content` = @p0 WHERE `Id` = @p1; SELECT ROW_COUNT(); Updated row id 3377699720527873
Delete Operation:
Executed DbCommand (451ms) [Parameters=[@p0='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30'] DELETE FROM `messages` WHERE `Id` = @p0; SELECT ROW_COUNT();