Case-Insensitive String Comparison not working in C#?

Question

Based on the answer to this question:

How can I do a case insensitive string comparison?

I'm trying to do a case-insensitive comparison without using Compare or ToLower:

var user = db.Users.FirstOrDefault(s => String.Equals(s.Username, username, StringComparison.OrdinalIgnoreCase));

However I get an error:

Incorrect number of arguments supplied for call to method 'Boolean Equals(System.String, System.String, System.StringComparison)'

What am I doing wrong?

Answer 1

The string comparison with StringComparison.OrdinalIgnoreCase works in memory or with IEnumerable<T>. You are trying to use it with IQueryable<T>, but the provider of your queryable does not understand it.

In Linq-to-Sql you should be able to use SqlMethods.Like(s.UserName, userName), like this:

var user = db.Users.FirstOrDefault(s => SqlMethods.Like(s.UserName, userName));

SqlMethods is in the System.Data.Linq.SqlClient namespace.

The Like method is case-insensitive, so you should get the expected result.

EDIT : I tried and get "LINQ to Entities does not recognize the method Boolean Like(System.String, System.String) method, and this method cannot be translated into a store expression."

This appears to be a known issue with EF (link).

This works for me:

db.Users.FirstOrDefault(
     s => s.Username.Equals(username, StringComparison.OrdinalIgnoreCase)
);

It appears that although EF has hard time translating the static Equals to SQL, it has no problem translating the instance Equals. This is a very good find - it makes for an easy to read, performant solution.

You could also use a simpler method with ToUpperCase or ToLowerCase, but that may prevent query optimizers from using indexes:

// Warning: this may not perform well.
var user = db.Users.FirstOrDefault(s => s.UserName.ToUpperCase() == userName.ToUpperCase());