18

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?

Community
  • 1
  • 1
SB2055
  • 12,272
  • 32
  • 97
  • 202

1 Answers1

39

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());
Community
  • 1
  • 1
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • 1
    Thanks for this. 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." - thoughts? – SB2055 Oct 26 '13 at 15:02
  • 1
    This works for me: db.Users.FirstOrDefault(s => s.Username.Equals(username, StringComparison.OrdinalIgnoreCase)); I don't know what to do in terms of selecting an answer - feel free to make note of this and I can check yours off? – SB2055 Oct 26 '13 at 15:12
  • Would you say there's anything wrong with the solution I posted in the comment? I was looking for a simple, performant solution that follows best practices. – SB2055 Oct 26 '13 at 15:16
  • 1
    @SB2055 Your solution is the best one: it's a little strange that EF has problems translating the static `Equals`, but takes the instance `Equals` well, but since the two functions are processed separately, it makes sense that one would work while the other would fail. – Sergey Kalinichenko Oct 26 '13 at 15:40