LINQ Breaking changes in EF Core 3.0. How can I compare strings without getting the warning CA1308?

Question

I had the following code, which was running well with EF Core 2.1:

.FirstOrDefault(a => (a.Name.Equals(b, StringComparison.InvariantCultureIgnoreCase).

(Ok, running well means I got the right results even if it was being evaluated in the client side and I didn't know it).

I updagred to EF Core 3.0 and I didn't get any error, but this code was not giving the expected results.

I saw here a solution. I tried a.Name.ToLower() == b.ToLower() but then I got the the error:

Error CA1304 The behavior of 'string.ToLower()' could vary based on the current user's locale settings. Replace this call in 'MyFunction(string, string)' with a call to 'string.ToLower(CultureInfo)'

If I use a ToLower(CultureInfo.InvariantCulture) I get the message:

Error CA1308 In method 'MyFunction', replace the call to 'ToLower' with 'ToUpperInvariant'.

If I use ToUpperInvariant(), then I get the error (I'm already aware of the LINQ breaking changes in EF Core 3.0):

The LINQ expression (... all the expression...) could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

So, I am the starting point.

Is there a way to both comply with CA1304 and run the query in the DB and not in the client side?

Dallon

You had a serious performance bug that was covered up by client-side evaluation. Don't use Equals like this at all and the bug and warning will go away. String comparision in SQL is controlled by the column's collation. Indexes are created based on the column collation too. In most cases, tables are created using case-invariant collation which means you don't have to modify the case at all. – Headdress

What happened before EF Core 3.0, is that EF Core was unable to generate SQL statements from the (unnecessary) string transformations so it pulled all data on the client side without warning and tried to filter it there, without any indexes. That's very slow – Headdress

If Name is covered by an index, by changing your code to just .FirstOrDefault(a => a.Name. == b) you could see N times faster execution, where N the number of rows in the table. The previous code locked the entire table too, which harms scalability – Headdress

Why did you use StringComparison.InvariantCultureIgnoreCase or ToLower() in the first place? Did you use a case-sensitive collation? Why not change it to case-insensitive instead? – Headdress

Well, I have to say that in reality I had .FirstOrDefault(a => (a.Name.Equals(b))), without the StringComparison.InvariantCultureIgnoreCase and I just added it a couple of days ago to comply with CA1304. Should I just ignore this warning? – Dallon

No, you should use a.Name == b. – Headdress

OMG. So simple. O_O It works!!! Please, write the answer and I'll accept it. Thank you!!! – Dallon

@PanagiotisKanavos: I think you were not notified with my answer. If you write your comment as an answer, I'll accept it (why do you write useful answers, even if they are trivial, as comments? :-O ). – Dallon

Answer 1

The solution, as PanagiotisKanavos commented, was to simply use a.Name == b. Easy and it works!

Dallon

This fix is applicable for Case Insensitive or Case sensitive? – Philosophical

I think it was case insensitive, but I'm not 100% sure... – Dallon

It's case insensitive and trims the end. – Quadricycle

It would be a good idea to include in this answer that it depends on the collation of the DB / column. Eg if you use SQL_Latin1_General_CP1_CS_AS, this solution will NOT work! – Irrigate