Chapter 9 Connector/NET for Entity Framework

Table of Contents

9.1 Entity Framework 6 Support
9.2 Entity Framework Core Support
9.2.1 Creating a Database with Code First in EF Core
9.2.2 Scaffolding an Existing Database in EF Core
9.2.3 Configuring Character Sets and Collations in EF Core

Entity Framework is the name given to a set of technologies that support the development of data-oriented software applications. MySQL Connector/NET supports Entity Framework 6.x (EF6) and Entity Framework Core (EF Core), which is the most recent framework available to .NET developers who work with MySQL data using .NET objects.

The following table shows the set of Connector/NET versions that support Entity Framework features.

Table 9.1 Connector/NET Versions and Entity Framework Support

Connector/NET VersionEF6EF Core
8.0Full supportEF Core 2.1: Full support in 8.0.13 and higher.

EF Core 2.0: Partial support in 8.0.8 to 8.0.12 (No scaffolding).

6.10Full supportEF Core 2.0: Full support in 6.10.8 and higher. Partial support in 6.10.5 to 6.10.7 (No scaffolding).

EF Core 1.1: Full support in 6.10.4 and higher; otherwise, partially supported.


9.1 Entity Framework 6 Support

MySQL Connector/NET integrates support for Entity Framework 6.0 (EF6). This chapter describes how to configure and use the EF6 features that are implemented in Connector/NET.

In this section:

Requirements for EF6

  • Connector/NET 6.10.x or 8.0.x

  • MySQL Server 5.5 or higher

  • Entity Framework 6 assemblies

  • .NET Framework 4.0 or higher (.NET Framework 4.5.1 or higher is required for Connector/NET 6.10 and 8.0)

Configuration

To configure Connector/NET support for EF6:

  1. Edit the configuration sections in the app.config file to add the connection string and the Connector/NET provider for EF6:

    <connectionStrings>
        <add name="MyContext" providerName="MySql.Data.MySqlClient"
            connectionString="server=localhost;port=3306;database=mycontext;uid=root;password=********"/>
    </connectionStrings>
    <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>
        <providers>
            <provider invariantName="MySql.Data.MySqlClient"
                type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/>
            <provider invariantName="System.Data.SqlClient"
                type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
        </providers>
    </entityFramework>
    
  2. Apply the reference for MySql.Data.Entity automatically or manually as follows:

    • Install the MySql.Data.Entity NuGet package to add this reference automatically within app.config or web.config file during the installation. For more information about the MySql.Data.Entity NuGet package and its uses, see https://www.nuget.org/packages/MySql.Data.Entity/.

      Proceed to step 3.

    • Otherwise, add a reference for the MySql.Data.Entity.EF6 assembly to your project. Depending on the .NET Framework version used, the assembly is taken from either the v4.0 or the v4.5 folder).

      Unless Connector/NET was installed with the standalone MSI or MySQL Installer, which adds the reference, insert the following data provider information into the app.config or web.config file:

      <system.data>
         <DbProviderFactories>
           <remove invariant="MySql.Data.MySqlClient" />
           <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" 
                type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=8.0.10.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
         </DbProviderFactories>
      </system.data>
      
      Important

      Always update the version number to match the one in the MySql.Data.dll assembly.

  3. Set the new DbConfiguration class for MySQL. This step is optional but highly recommended, because it adds all the dependency resolvers for MySQL classes. This can be done in three ways:

    • Adding the DbConfigurationTypeAttribute on the context class:

      [DbConfigurationType(typeof(MySqlEFConfiguration))]
      
    • Calling DbConfiguration.SetConfiguration(new MySqlEFConfiguration()) at the application start up.

    • Set the DbConfiguration type in the configuration file:

      <entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
      

    It is also possible to create a custom DbConfiguration class and add the dependency resolvers needed.

EF6 Features

Following are the new features in Entity Framework 6 implemented in Connector/NET:

  • Async Query and Save adds support for the task-based asynchronous patterns that have been introduced since .NET 4.5. The new asynchronous methods supported by Connector/NET are:

    • ExecuteNonQueryAsync

    • ExecuteScalarAsync

    • PrepareAsync

  • Connection Resiliency / Retry Logic enables automatic recovery from transient connection failures. To use this feature, add to the OnCreateModel method:

    SetExecutionStrategy(MySqlProviderInvariantName.ProviderName, () => new MySqlExecutionStrategy());
    
  • Code-Based Configuration gives you the option of performing configuration in code, instead of performing it in a configuration file, as it has been done traditionally.

  • Dependency Resolution introduces support for the Service Locator. Some pieces of functionality that can be replaced with custom implementations have been factored out. To add a dependency resolver, use:

    AddDependencyResolver(new MySqlDependencyResolver());
    

    The following resolvers can be added:

    • DbProviderFactory -> MySqlClientFactory

    • IDbConnectionFactory -> MySqlConnectionFactory

    • MigrationSqlGenerator -> MySqlMigrationSqlGenerator

    • DbProviderServices -> MySqlProviderServices

    • IProviderInvariantName -> MySqlProviderInvariantName

    • IDbProviderFactoryResolver -> MySqlProviderFactoryResolver

    • IManifestTokenResolver -> MySqlManifestTokenResolver

    • IDbModelCacheKey -> MySqlModelCacheKeyFactory

    • IDbExecutionStrategy -> MySqlExecutionStrategy

  • Interception/SQL logging provides low-level building blocks for interception of Entity Framework operations with simple SQL logging built on top:

    myContext.Database.Log = delegate(string message) { Console.Write(message); };
    
  • DbContext can now be created with a DbConnection that is already opened, which enables scenarios where it would be helpful if the connection could be open when creating the context (such as sharing a connection between components when you cannot guarantee the state of the connection)

      [DbConfigurationType(typeof(MySqlEFConfiguration))]
      class JourneyContext : DbContext
      {
        public DbSet<MyPlace> MyPlaces { get; set; }
    
        public JourneyContext()
          : base()
        {
    
        }
    
        public JourneyContext(DbConnection existingConnection, bool contextOwnsConnection)
          : base(existingConnection, contextOwnsConnection)
        {
    
        }
      }
    
      using (MySqlConnection conn = new MySqlConnection("<connectionString>"))
      {
        conn.Open();
        ...
    
        using (var context = new JourneyContext(conn, false))
        {
          ...
        }
      }
    
  • Improved Transaction Support provides support for a transaction external to the framework as well as improved ways of creating a transaction within the Entity Framework. Starting with Entity Framework 6, Database.ExecuteSqlCommand() will wrap by default the command in a transaction if one was not already present. There are overloads of this method that allow users to override this behavior if wished. Execution of stored procedures included in the model through APIs such as ObjectContext.ExecuteFunction() does the same. It is also possible to pass an existing transaction to the context.

  • DbSet.AddRange/RemoveRange provides an optimized way to add or remove multiple entities from a set.

Code First Features

Following are new Code First features supported by Connector/NET:

  • Code First Mapping to Insert/Update/Delete Stored Procedures supported:

    modelBuilder.Entity<EntityType>().MapToStoredProcedures();
    
  • Idempotent migrations scripts allow you to generate an SQL script that can upgrade a database at any version up to the latest version. To do so, run the Update-Database -Script -SourceMigration: $InitialDatabase command in Package Manager Console.

  • Configurable Migrations History Table allows you to customize the definition of the migrations history table.

Example for Using EF6

The following C# code example represents the structure of an Entity Framework 6 model.

using MySql.Data.Entity;
using System.Data.Common;
using System.Data.Entity;

namespace EF6
{
  // Code-Based Configuration and Dependency resolution
  [DbConfigurationType(typeof(MySqlEFConfiguration))]
  public class Parking : DbContext
  {
    public DbSet<Car> Cars { get; set; }

    public Parking()
      : base()
    {

    }

    // Constructor to use on a DbConnection that is already opened
    public Parking(DbConnection existingConnection, bool contextOwnsConnection)
      : base(existingConnection, contextOwnsConnection)
    {

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      base.OnModelCreating(modelBuilder);
      modelBuilder.Entity<Car>().MapToStoredProcedures();
    }
  }

  public class Car
  {
    public int CarId { get; set; }

    public string Model { get; set; }

    public int Year { get; set; }

    public string Manufacturer { get; set; }
  }
}

The C# code example that follows shows how to use the entities from the previous model in an application that stores the data within a MySQL table.

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;

namespace EF6
{
  class Example
  {
    public static void ExecuteExample()
    {
      string connectionString = "server=localhost;port=3305;database=parking;uid=root";

      using (MySqlConnection connection = new MySqlConnection(connectionString))
      {
        // Create database if not exists
        using (Parking contextDB = new Parking(connection, false))
        {
          contextDB.Database.CreateIfNotExists();
        }

        connection.Open();
        MySqlTransaction transaction = connection.BeginTransaction();

        try
        {
          // DbConnection that is already opened
          using (Parking context = new Parking(connection, false))
          {

            // Interception/SQL logging
            context.Database.Log = (string message) => { Console.WriteLine(message); };

            // Passing an existing transaction to the context
            context.Database.UseTransaction(transaction);

            // DbSet.AddRange
            List<Car> cars = new List<Car>();

            cars.Add(new Car { Manufacturer = "Nissan", Model = "370Z", Year = 2012 });
            cars.Add(new Car { Manufacturer = "Ford", Model = "Mustang", Year = 2013 });
            cars.Add(new Car { Manufacturer = "Chevrolet", Model = "Camaro", Year = 2012 });
            cars.Add(new Car { Manufacturer = "Dodge", Model = "Charger", Year = 2013 });

            context.Cars.AddRange(cars);

            context.SaveChanges();
          }

          transaction.Commit();
        }
        catch
        {
          transaction.Rollback();
          throw;
        }
      }
    }
  }
}

9.2 Entity Framework Core Support

MySQL Connector/NET integrates support for Entity Framework Core (EF Core). The requirements and configuration of EF Core depend on the version of Connector/NET installed and the features that you require. Use the table that follows to evaluate the requirements.

Table 9.2 Supported versions of Entity Framework Core

Connector/NET EF Core 1.1 EF Core 2.0 EF Core 2.1
6.10.4 .NET Standard 1.3 or .NET Framework 4.5.2 (and later) Not supported Not supported
6.10.5 to 6.10.7 .NET Standard 1.3 or .NET Framework 4.5.2 (and later) .NET Standard 2.0 only (.NET Framework is not supported)

Scaffolding is not supported

Not supported
6.10.8 .NET Standard 1.3 or .NET Framework 4.5.2 .NET Standard 2.0 or .NET Framework 4.6.1 (and later) Not supported
8.0.11 to 8.0.12 .NET Standard 1.6 or .NET Framework 4.5.2 (and later) .NET Standard 2.0 only (.NET Framework is not supported)

Scaffolding is not supported

Not supported
8.0.13 .NET Standard 1.6 or .NET Framework 4.5.2 Not supported .NET Standard 2.0 or .NET Framework 4.6.1 (and later)

In this section:

Minimum Requirements for EF Core Support

Configuration with MySQL

To use Entity Framework Core with a MySQL database, do the following:

  1. Install the MySql.Data.EntityFrameworkCore NuGet package.

    For EF Core 1.1 only: If you plan to scaffold a database, install the MySql.Data.EntityFrameworkCore.Design NuGet package as well.

    All packages will install the additional packages required to run your application. For instructions on adding a NuGet package, see the relevant Microsoft documentation.

  2. In the class that derives from the DbContext class, override the OnConfiguring method to set the MySQL data provider with UseMySQL. The following example shows how to set the provider using a generic connection string in C#.

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
         #warning To protect potentially sensitive information in your connection string, 
         you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 
         for guidance on storing connection strings.
         
       optionsBuilder.UseMySQL("server=localhost;database=library;user=user;password=password");
    }
    

Limitations

The Connector/NET implementation of EF Core has the following limitations:

  • Memory-Optimized Tables is not supported.

Maximum String Length

The following table shows the maximum length of string types supported by the Connector/NET implementation of EF Core. Length values are in bytes for nonbinary and binary string types, depending on the character set used.

Table 9.3 Maximum Length of strings used with EF Core

Data TypeMaximum Length.NET Type
CHAR255string
BINARY255byte[]
VARCHAR, VARBINARY65,535string, byte[]
TINYBLOB, TINYTEXT255byte[]
BLOB, TEXT65,535byte[]
MEDIUMBLOB, MEDIUMTEXT16,777,215byte[]
LONGBLOB, LONGTEXT4,294,967,295byte[]
ENUM65,535string
SET65,535string

For additional information about the storage requirements of the string types, see String Type Storage Requirements.

9.2.1 Creating a Database with Code First in EF Core

The Code First approach enables you to define an entity model in code, create a database from the model, and then add data to the database. The data added by the application is also retrieved by the application using MySQL Connector/NET.

The following example shows the process of creating a database from existing code. Although this example uses the C# language, you can execute it on Windows, macOS, or Linux.

  1. Create a console application for this example.

    1. Initialize a valid .NET Core project and console application using the .NET Core command-line interface (CLI) and then switch to the newly created folder (mysqlefcore).

      dotnet new console –o mysqlefcore        
      
      cd mysqlefcore          
      
    2. Add the MySql.Data.EntityFrameworkCore package to the application using the CLI as follows:

      dotnet add package MySql.Data.EntityFrameworkCore -Version 6.10.8
      

      Alternatively, you can use the Package Manager Console in Visual Studio to add the package.

      Install-Package MySql.Data.EntityFrameworkCore -Version 6.10.8         
      
      Note

      The version (for example, -Version 6.10.8) must match the actual Connector/NET version you are using. For current version information, see Table 9.2, “Supported versions of Entity Framework Core”.

    3. Restore dependencies and project-specific tools that are specified in the project file as follows:

      dotnet restore
      
  2. Create the model and run the application.

    The model in this EF Core example will be used by the console application. It consists of two entities related to a book library, which will be configured in the LibraryContext class (or database context).

    1. Create a new file named LibraryModel.cs and then add the following Book and Publisher classes to the mysqlefcore namespace.

      namespace mysqlefcore
      {
        public class Book
        {
          public string ISBN { get; set; }
          public string Title { get; set; }
          public string Author { get; set; }
          public string Language { get; set; }   
          public int Pages { get; set; }
          public virtual Publisher Publisher { get; set; }
        }
      
        public class Publisher
        {
          public int ID { get; set; }
          public string Name { get; set; }
          public virtual ICollection<Book> Books { get; set; }
        }
      }
      
    2. Create a new file named LibraryContext.cs and add the code that follows. Replace the generic connection string with one that is appropriate for your MySQL server configuration.

      The LibraryContext class contains the entities to use and it enables the configuration of specific attributes of the model, such as Key, required columns, references, and so on.

      using Microsoft.EntityFrameworkCore;
      using MySQL.Data.EntityFrameworkCore.Extensions;
      
      namespace mysqlefcore
      {
        public class LibraryContext : DbContext
        {
          public DbSet<Book> Book { get; set; }
      
          public DbSet<Publisher> Publisher { get; set; }
      
          protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
          {
            optionsBuilder.UseMySQL("server=localhost;database=library;user=user;password=password");
          }
      
          protected override void OnModelCreating(ModelBuilder modelBuilder)
          {
            base.OnModelCreating(modelBuilder);
      
            modelBuilder.Entity<Publisher>(entity =>
            {
              entity.HasKey(e => e.ID);
              entity.Property(e => e.Name).IsRequired();
            });
      
            modelBuilder.Entity<Book>(entity =>
            {
              entity.HasKey(e => e.ISBN);
              entity.Property(e => e.Title).IsRequired();
              entity.HasOne(d => d.Publisher)
                .WithMany(p => p.Books);
            });
          }
        }
      }       
      
    3. Insert the following code into the existing Program.cs file, replacing the default C# code.

      using Microsoft.EntityFrameworkCore;
      using System;
      using System.Text;
      
      namespace mysqlefcore
      {
        class Program
        {
          static void Main(string[] args)
          {
            InsertData();
            PrintData();
          }
      
          private static void InsertData()
          {
            using(var context = new LibraryContext())
            {
              // Creates the database if not exists
              context.Database.EnsureCreated();
      
              // Adds a publisher
              var publisher = new Publisher
              {
                Name = "Mariner Books"
              };
              context.Publisher.Add(publisher);
      
              // Adds some books
              context.Book.Add(new Book
              {
                ISBN = "978-0544003415",
                Title = "The Lord of the Rings",
                Author = "J.R.R. Tolkien",
                Language = "English",
                Pages = 1216,
                Publisher = publisher
              });
              context.Book.Add(new Book
              {
                ISBN = "978-0547247762",
                Title = "The Sealed Letter",
                Author = "Emma Donoghue",
                Language = "English",
                Pages = 416,
                Publisher = publisher
              });
      
              // Saves changes
              context.SaveChanges();
            }
          }
      
          private static void PrintData()
          {
            // Gets and prints all books in database
            using (var context = new LibraryContext())
            {
              var books = context.Book
                .Include(p => p.Publisher);
              foreach(var book in books)
              {
                var data = new StringBuilder();
                data.AppendLine($"ISBN: {book.ISBN}");
                data.AppendLine($"Title: {book.Title}");
                data.AppendLine($"Publisher: {book.Publisher.Name}");
                Console.WriteLine(data.ToString());
              }
            }
          }
        }
      }              
      
    4. Use the following CLI commands to restore the dependencies and then run the application.

      dotnet restore
      
      dotnet run            
      

The output from running the application is represented by the following example:

ISBN: 978-0544003415
Title: The Lord of the Rings
Publisher: Mariner Books

ISBN: 978-0547247762
Title: The Sealed Letter
Publisher: Mariner Books

9.2.2 Scaffolding an Existing Database in EF Core

Scaffolding a database produces an Entity Framework model from an existing database. The resulting entities are created and mapped to the tables in the specified database. This feature was introduced in MySQL Connector/NET 6.10.2-beta and 8.0.8-dmr; however, scaffolding is not supported with all versions of Connector/NET (see Table 9.2, “Supported versions of Entity Framework Core”).

Note

The Design package for scaffolding a database is part of the main package in EF Core 2.0 (or later) and no longer separate. If you are upgrading from EF Core 1.1 to EF Core 2.0 or 2.1, you must remove the MySql.Data.EntityFrameworkCore.Design package manually.

The MySql.Data.EntityFrameworkCore.Design package remains available for EF Core 1.1 projects.

NuGet packages have the ability to select the best target for a project, which means that NuGet will install the libraries related to that specific framework version.

There are two different ways to scaffold an existing database:

This section shows how to scaffold the sakila database using both approaches.

Minimum Prerequisites

Note

Applications targeting previous versions of ASP.NET Core must upgrade to ASP.NET Core 2.1 to use EF Core 2.1.

Scaffolding a Database Using .NET Core CLI

  1. Initialize a valid .NET Core project and console application using the .NET Core command-line interface (CLI) and then change to the newly created folder (sakilaConsole).

    dotnet new console –o sakilaConsole          
    
    cd sakilaConsole          
    
  2. Add the MySQL NuGet package for EF Core using the CLI. For example, use the following command to add the MySql.Data.EntityFrameworkCore v8.0.13 package:

    dotnet add package MySql.Data.EntityFrameworkCore -Version 8.0.13         
    
    Note

    The version (for example, -Version 8.0.13) must match the actual Connector/NET version you are using. For current version information, see Table 9.2, “Supported versions of Entity Framework Core”.

  3. Add the following Microsoft.EntityFrameworkCore.Design Nuget package:

    dotnet add package Microsoft.EntityFrameworkCore.Design
    

    EF Core 1.1 only: Also add the MySql.Data.EntityFrameworkCore.Design package.

  4. Add a reference to Microsoft.EntityFrameworkCore.Tools.DotNet as a DotNetCliToolReference entry in the sakilaConsole.csproj file as follows:

    EF Core 1.1

    <ItemGroup>
       <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.1.6"/>
    </ItemGroup>
    

    EF Core 2.0

    <ItemGroup>
       <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.3"/>
    </ItemGroup>
    
    Note

    The .NET tools are included in the .NET Core 2.1 SDK and not required or supported for EF Core 2.1. If this is an upgrade, remove the following reference to that package from the .csproj file (version 2.0.3 in this example) :

    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.3" />
    
  5. Restore dependencies and project-specific tools that are specified in the project file as follows:

    dotnet restore
    
  6. Create the Entity Framework Core model by executing the following command (adjust the connection-string values to match your settings for the user= and password= options):

    dotnet ef dbcontext scaffold "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -o sakila -f
    

    To validate that the model has been created, open the new sakila folder. You should see files corresponding to all tables mapped to entities. In addition, look for the sakilaContext.cs file, which contains the DbContext for this database.

Scaffolding a Database Using Package Manager Console in Visual Studio

  1. Open Visual Studio and create a new Console App (.NET Core) for C#.

  2. Add the MySQL NuGet package for EF Core using the Package Manager Console. For example, use the following command to add the MySql.Data.EntityFrameworkCore v8.0.13 package:

    Install-Package MySql.Data.EntityFrameworkCore -Version 8.0.13         
    
    Note

    The version (for example, -v 8.0.13) must match the actual Connector/NET version you are using. For current version information, see Table 9.2, “Supported versions of Entity Framework Core”.

  3. Install the following NuGet packages by selecting either Package Manager Console or Manage NuGet Packages for Solution from the Tools and then NuGet Package Manager menu:

    • Microsoft.EntityFrameworkCore.Design

      EF Core 1.1 only: Also add the MySql.Data.EntityFrameworkCore.Design package.

    • Microsoft.EntityFrameworkCore.Tools version 1.1.6 (for EF Core 1.1) and Microsoft.EntityFrameworkCore.Tools version 2.0.3 (for EF Core 2.0)

      Note

      The .NET tools are included in the .NET Core 2.1 SDK and not required or supported for EF Core 2.1. If this is an upgrade, remove the reference to that package from the .csproj file (version 2.0.3 in this example) :

      <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.3" />
      
  4. Open Package Manager Console and enter the following command at the prompt to create the entities and DbContext for the sakila database (adjust the connection-string values to match your settings for the user= and password= options):

    Scaffold-DbContext "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -OutputDir sakila -f
    

    Visual Studio creates a new sakila folder inside the project, which contains all the tables mapped to entities and the sakilaContext.cs file.

Scaffolding a Database by Filtering Tables

It is possible to specify the exact tables in a schema to use when scaffolding database and to omit the rest. The command-line examples that follow show the parameters needed for filtering tables.

.NET Core CLI:

dotnet ef dbcontext scaffold "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -o sakila -t actor -t film -t film_actor -t language -f      

Package Manager Console in Visual Studio:

Scaffold-DbContext "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -OutputDir Sakila -Tables actor,film,film_actor,language -f      

Scaffolding with Multiple Schemas

When scaffolding a database, you can use more than one schema or database. Note that the account used to connect to the MySQL server must have access to each schema to be included within the context. Multiple-schema functionality was introduced in Connector/NET 6.10.3-rc and 8.0.9-dmr releases.

The following command-line examples show how to incorporate the sakila and world schemas within a single context.

.NET Core CLI:

dotnet ef dbcontext scaffold "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -o sakila --schema sakila --schema world -f      

Package Manager Console in Visual Studio:

Scaffold-DbContext "server=localhost;port=3306;user=root;password=mypass;database=sakila" MySql.Data.EntityFrameworkCore -OutputDir Sakila -Schemas sakila,world -f      

9.2.3 Configuring Character Sets and Collations in EF Core

This section describes how to change the character set, collation, or both at the entity and entity-property level in an Entity Framework (EF) Core model. Modifications made to the model affect the tables and columns generated from your code.

Starting with MySQL Connector/NET 6.10.4, you have two distinct approaches available for configuring character sets and collations in code-first scenarios. Data annotation enables you to apply attributes directly to your EF Core model. Alternatively, you can override the OnModelCreating method on your derived DbContext class and use the code-first fluent API to configure specific characteristics of the model. An example of each approach follows.

For more information about supported character sets and collations, see Character Sets and Collations in MySQL.

Using Data Annotation

Before you can annotate an EF Core model with character set and collation attributes, add a reference to the following namespace in the file that contains your entity model:

using MySql.Data.EntityFrameworkCore.DataAnnotations;

Add one or more [MySqlCharset] attributes to store data using a variety of character sets and one or more [MySqlCollation] attributes to perform comparisons according to a variety of collations. In the following example, the ComplexKey class represents an entity (or table) and Key1, Key2, and CollationColumn represent entity properties (or columns).

[MySqlCharset("utf8")]
public class ComplexKey
{
  [MySqlCharset("latin1")
  public string Key1 { get; set; }

  [MySqlCharset("latin1")]
  public string Key2 { get; set; }

  [MySqlCollation("latin1_spanish_ci")]
  public string CollationColumn { get; set; }
}

Using the Code-First Fluent API

Add the following directive to reference the methods related to character set and collation configuration:

using MySQL.Data.EntityFrameworkCore.Extensions; 

When using the fluent API approach, the EF Core model remains unchanged. Fluent API overrides any rule set by an attribute.

public class ComplexKey
{
  public string Key1 { get; set; }

  public string Key2 { get; set; }

  public string CollationColumn { get; set; }
}

In this example, the entity and various entity properties are reconfigured, including the conventional mappings to character sets and collations. This approach uses the ForMySQLHasCharset and ForMySQLHasCollation methods.

public class MyContext : DbContext
{
  public DbSet<ComplexKey> ComplexKeys { get; set; }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<ComplexKey>(e =>
    {
      e.HasKey(p => new { p.Key1, p.Key2 });
      e.ForMySQLHasCollation("ascii_bin"); // defining collation at Entity level
      e.Property(p => p.Key1).ForMySQLHasCharset("latin1"); // defining charset in a property
      e.Property(p => p.CollationColumnFA).ForMySQLHasCollation("utf8_bin"); // defining collation in a property
    });
  }
}