Changing the SQL Collation using an Entity Framework Query

by Eamon Keane

Not so long ago I ran into an issue where I wanted to query a SQL database but ignore the character accents and cases e.g. searching for Irish names.

I didn't want to alter the collation in the database and so I looked for another solution.

Thankfully, EF Core 5 introduced the ability to specify the collation explicitly in a query.

var users = context.Users
    .Where(c => EF.Functions.Collate(u.Name, "Latin1_General_CI_AI") == "Sean")
    .ToList();

The above query would match columns where the user's name was equal to "Seán" (The Irish spelling).

Using this approach, I was unable use the LINQ contains keyword in the search so instead I needed to use another Entity Framework Function "Like".

The final query looked something like this:

var filter = "Sea"

var users = context.Users
    .Where(c => EF.Functions.Like(EF.Functions.Collate(u.Name, "Latin1_General_CI_AI"),$"%{filter}%"))
    .ToList();
    
    // Returns Seán as a user from the database