Connect using Entity Framework Core

Note

This feature is only supported in SingleStore clusters running SingleStore 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 SingleStore databases. This provider is built on top of the SingleStore Connector for .NET and .NET Core.

Connect with SingleStore using EF Core

To connect with SingleStore:

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

  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:

    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:

    dotnet restore
  3. Update the placeholders in the connection string in the appsettings.Development.json file with the connection configuration for your SingleStore 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"
    }
    }
  4. Run the commands in the init.sql file in your SingleStore 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 SingleStore database.

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

    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:

    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();

References

Last modified: April 19, 2023

Was this article helpful?