Case and accent sensitivity

DBMS support

This configuration is available for SQL Server, MySQL v8+, MariaDB v10+, Oracle and PostgreSQL v11+. SQLite does not support changing sensitivity.

It is best to set sensitivity when the database is created. It is possible to set it after creation, but this only affects new tables or columns. A new migration is required after the change.

About

Collation configures the database's case and accent sensitivity. This sensitivity applies to searches and sorting. In Lagoon, this is configured in the EF Core model.

Default DBMS sensitivity

DBMS	Default sensitivity
SQL Server	Accent-sensitive and case-insensitive.
Oracle / PostgreSQL	Accent-sensitive and case-sensitive.
MySQL / MariaDB	Accent-insensitive and case-insensitive.

Server-side configuration

French collation

The French collation is configured by passing a CollationType to the DbContext constructor. By default, the historical Lagoon documentation indicates a case- and accent-insensitive configuration.

Replace a simple constructor:

ApplicationDbContext.cs

public ApplicationDbContext(DbContextOptions options)
    : base(options)
{ }

with a constructor that specifies a collation:

ApplicationDbContext.cs

public ApplicationDbContext(DbContextOptions options)
    : base(options, CollationType.IgnoreCaseAndAccent)
{ }

The available values are:

Value	Effect
null argument or constructor without collation	Default DBMS configuration.
CollationType.IgnoreCaseAndAccent	Case- and accent-insensitive.
CollationType.IgnoreCase	Case-insensitive but accent-sensitive.
CollationType.CaseSensitive	Case- and accent-sensitive.

Migration required

After changing the collation, create a new EF Core migration.

Other languages

It is possible to build a specific collation with UseCollation(...) on ModelBuilder. The argument is the name of the collation in the database; it must exist in the DBMS.

Disable the French collation if you manually configure another collation. The GetCollationName(...) method retrieves the name based on the collation type, the language and the DBMS.

MySQL, MariaDB and PostgreSQL

MariaDB, MySQL and PostgreSQL databases do not need the language to be specified for this case: they use the Unicode standard. You can therefore use the French collation for all languages.

ApplicationDbContext.cs

protected override void OnModelCreating(ModelBuilder builder)
{
    // Applique d'abord la configuration héritée Lagoon/Identity.
    base.OnModelCreating(builder);

    // Configure une collation SQL Server basée sur Latin1_General.
    builder.UseCollation(GetCollationName(CollationType.IgnoreCaseAndAccent, "Latin1_General"));
}

Collation for a specific object

PostgreSQL and Oracle

PostgreSQL and Oracle do not support this kind of per-table or per-column configuration in this Lagoon documentation.

Table

It is possible to configure sensitivity for a table with UseCollation(...).

ApplicationDbContext.cs

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    // Configure la collation sur une table précise.
    builder.Entity<Firm>().UseCollation(GetCollationName(CollationType.IgnoreCaseAndAccent, "Latin1_General"));
}

Column

It is possible to configure sensitivity for a column with UseCollation(...).

ApplicationDbContext.cs

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    // Configure la collation sur une colonne précise.
    builder.Entity<Firm>()
        .Property(f => f.Name)
        .UseCollation(GetCollationName(CollationType.IgnoreCaseAndAccent, "Latin1_General"));
}

Query

To use a collation at the query level only, use EF.Functions.Collate(...).

ApplicationDbContext.cs

IQueryable<Firm> firms = _db.Firms.Where(f => EF.Functions.Like(
    EF.Functions.Collate(f.Name, GetCollationName(CollationType.IgnoreCaseAndAccent, "Latin1_General")),
    "%ray%"));

Client-side configuration

Client/server consistency

To keep searches consistent between client and server, set the same sensitivity on both sides.

On the client side, use the global TextFilterCollation setting. It drives the UI's text filters, notably the dropdown list and grid filters.

The available values are those of CollationType:

Value	Effect
CollationType.CaseSensitive	Case- and accent-sensitive.
CollationType.IgnoreCase	Case-insensitive but accent-sensitive.
CollationType.IgnoreCaseAndAccent	Case- and accent-insensitive.

Main.cs

protected override void OnConfigureBehavior(ApplicationBehavior behavior)
{
    // Aligne les filtres texte côté client avec la collation serveur.
    behavior.TextFilterCollation = CollationType.IgnoreCaseAndAccent;
}