Ignoring accents while searching the database using Entity Framework

user_303476 0
Viewed 14767

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?

non-ascii-characters entity-framework c#
5 Answers

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();
user_127696510
Related