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

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

Note

The MySQL.EntityFrameworkCore.DataAnnotations namespace applies to Connector/NET 8.0.23 and later. Earlier connector versions require the MySQL.Data.EntityFrameworkCore.DataAnnotations namespace.

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

Note

The MySQL.EntityFrameworkCore.Extensions namespace applies to Connector/NET 8.0.23 and later. Earlier connector versions require the MySQL.Data.EntityFrameworkCore.Extensions namespace.

using MySQL.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
    });
  }
}