Connect with SingleStore using EF Core

To connect with SingleStore:

1. Install the provider: Run the following command:

   Shell

   Copy

   dotnet add package EntityFrameworkCore.SingleStore

   The following reference is added to the .csproj file in your project:

   XML

   Copy

   <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.

2. Configure the service: Add EntityFrameworkCore.SingleStore to the services configuration in the Startup.cs file of your .NET Core project. Here's a sample:

   C#

   Copy

   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 SingleStore 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

• SingleStore 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 SingleStore database.

• Additional unsupported features:

  • Savepoints

  • Unique indexes/constraints

  • Check constraints

  • Spatial data types

  • Full-text indexes

  • JSON

• AUTO_INCREMENT can only be used on a BIGINT column.

• By default, max key length is set to 3072 (same as MySQL). Contact SingleStore Support to increase the length. SingleStore 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 SingleStore using EF Core:

1. Clone the following GitHub repository: https://github.com/singlestore-labs/start-with-singlestore-csharp-orm.git.

2. Run the following command in the command-line:

   Shell

   Copy

   dotnet restore

3. Update the placeholders in the connection string in the appsettings.Development.json file with the connection configuration for your SingleStore database:

   JSON

   Copy

   "DefaultConnection": "Server=<hostname-or-ip-address>;Port=3306;Uid=<user>;Pwd=<password>;database=<database-name>"

   Here's a sample:

   JSON

   Copy

   {
     "Logging": {
       "LogLevel": {
         "Default": "Debug",
         "System": "Information",
         "Microsoft": "Information"
       }
     },
     "ConnectionStrings": {
       "DefaultConnection": "Server=svchost;Port=3306;Uid=root;Pwd=passw0rd;database=acme"
     }
   }

4. Run the commands in the init.sql file in your SingleStore database:

   Shell

   Copy

   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 SingleStore database.

5. Run the following command to build the project and all of its dependencies:

   Shell

   Copy

   dotnet build

6. The Runner.cs file in this repository defines basic CRUD operations using EF Core. Run the following command to perform these operations:

   Shell

   Copy

   dotnet run

   Here are snippets from the output that highlight each operation:

   Create Operation:

   Shell

   Copy

   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:

   Shell

   Copy

   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:

   Shell

   Copy

   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:

   Shell

   Copy

   Executed DbCommand (451ms) [Parameters=[@p0='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30']
         DELETE FROM `messages`
         WHERE `Id` = @p0;
         SELECT ROW_COUNT();

References

• Overview of Entity Framework Core

• Entity Framework Core Tools Reference