How to implement context string comparison ignoring case in C# (Linq)

Question

var foundName = await (the Context)
                      .Search<MyEntity>(x => x.Name.Equals(data.Name, StringComparison.OrdinalIgnoreCase))
                      .AsNoTracking()
                      .FirstOrDefaultAsync();

I got this error:

The LINQ expression 'DbSet() .Where(s => s.Name.Equals( value: __data_Name_0, comparisonType: OrdinalIgnoreCase))' could not be translated. Additional information: Translation of the 'string.Equals' overload with a 'StringComparison' parameter is not supported. See https://go.microsoft.com/fwlink/?linkid=2129535 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

How to implement this query?

Answer 1

You can only do a normal C# == or string1.Equals(string2) type comparison, anything else isn't understood by Entity Framework and is why you are seeing that exception.

In fact, it is converted to SQL and will ultimately rely on the collation you have set for that database. So if you want case insensitive comparisons, make sure you use a case insensitive collation such as SQL_Latin1_General_CP1_CI_AS.

You could possibly run some raw SQL, but I wouldn't recommend this. For example:

var sql = "SELECT * FROM dbo.Zombies WHERE Name = 'BrainEater' COLLATE SQL_Latin1_General_CP1_CI_AS";

var blogs = await context.Zombies
    .FromSqlRaw(sql)
    .ToListAsync();