Documentation Home
MySQL Connector/NET Developer Guide
Related Documentation Download this Manual

MySQL Connector/NET Developer Guide  /  ...  /  Configuring Character Sets and Collations in EF Core

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

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.