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?

been a while since i worked with EnitiyFramework but generally you can't use programmatic functions in a sql database as it takes too much effort to translate c# code into an sql function so you need to use functions that already exist in sql. so for case insensitive equals cast both strings to upper or lower and then compare with == — MikeT, Oct 14 '21 at 15:52

score 1 · Answer 1 · answered Oct 14 '21 at 16:14

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

score 0 · Answer 2 · edited Oct 14 '21 at 18:28

0

Try this way to get the desired result.

var foundName = await (the Context)
    .Where<MyEntity>(x => x.Name.ToLower() == data.Name.ToLower())
    .AsNoTracking().FirstOrDefaultAsync();

edited Oct 14 '21 at 18:28

marc_s

732,580
175
1,330
1,459

answered Oct 14 '21 at 16:29

Rajesh P

39
1
6

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

2 Answers2