Ignoring accents while searching the database using Entity Framework

Question

I have a database table that contains names with accented characters. Like ä and so on.

I need to get all records using EF4 from a table that contains some substring regardless of accents.

So the following code:

myEntities.Items.Where(i => i.Name.Contains("a")); 

should return all items with a name containing a, but also all items containing ä, â and so on. Is this possible?

Answer 1

If you set an accent-insensitive collation order on the Name column then the queries should work as required.

Answer 2

Setting an accent-insensitive collation will fix the problem.

You can change the collation for a column in SQL Server and Azure database with the next query.

ALTER TABLE TableName
ALTER COLUMN ColumnName NVARCHAR (100)
COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT NULL

SQL_LATIN1_GENERAL_CP1_CI_AI is the collation where LATIN1_GENERAL is English (United States), CP1 is code page 1252, CI is case-insensitive, and AI is accent-insensitive.

Answer 3

I know that is not so clean solution, but after reading this I tried something like this:

var query = this.DataContext.Users.SqlQuery(string.Format("SELECT *  FROM dbo.Users WHERE LastName like '%{0}%' COLLATE Latin1_general_CI_AI", parameters.SearchTerm));

After that you are still able to call methods on 'query' object like Count, OrderBy, Skip etc.

Answer 4

You could create an SQL Function to remove the diacritics, by applying to the input string the collation SQL_Latin1_General_CP1253_CI_AI, like so:

CREATE FUNCTION [dbo].[RemoveDiacritics] (
@input varchar(max)
)   RETURNS varchar(max)

AS BEGIN
DECLARE @result VARCHAR(max);

select @result = @input collate SQL_Latin1_General_CP1253_CI_AI

return @result
END

Then add it in the DB context (in this case ApplicationDbContext) by mapping it with the attribute DbFunction:

public class ApplicationDbContext : IdentityDbContext<CustomIdentityUser>
    {
        [DbFunction("RemoveDiacritics", "dbo")]
        public static string RemoveDiacritics(string input)
        {
            throw new NotImplementedException("This method can only be used with LINQ.");
        }

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

And Use it in LINQ query, for example:

var query = await db.Users.Where(a => ApplicationDbContext.RemoveDiacritics(a.Name).Contains(ApplicationDbContext.RemoveDiacritics(filter))).tolListAsync();

Answer 5

Accent-insensitive Collation as Stuart Dunkeld suggested is definitely the best solution ...

But maybe good to know:

Michael Kaplan once posted about stripping diacritics:

static string RemoveDiacritics(string stIn)
{
    string stFormD = stIn.Normalize(NormalizationForm.FormD);
    StringBuilder sb = new StringBuilder();

    for(int ich = 0; ich < stFormD.Length; ich++)
    {
        UnicodeCategory uc = CharUnicodeInfo.GetUnicodeCategory(stFormD[ich]);
        if(uc != UnicodeCategory.NonSpacingMark)
        {
            sb.Append(stFormD[ich]);
        }
    }

    return(sb.ToString().Normalize(NormalizationForm.FormC));
}

Source

So your code would be:

myEntities.Items.Where(i => RemoveDiacritics(i.Name).Contains("a"));