String.Equals() not working as intended

Solution 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.

This works for me:

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

Solution 2:

When using LINQ to Entities, it will automatically convert it to LINQ to SQL. And if the database field you are doing a .Equals on does not have a collate of NOCASE (SQLite in my example) then it will always be case-sensitive. In otherwords, the database defines how to do the string comparison rather than code.

Solution 3:

Use the String.Compare() as it can be translated to Sql.

Here are some examples of string matching in Linq, with the Sql translation as well.

Solution 4:

Made some research. You can't do. The collation (the type of comparison) is defined at the column level of the table. You can't modify it through EF. If it's defined as case insensitive, then all the searches will be case-insensitive. If it's defined as case sensitive, then your only hope is ToUpper() the strings.

http://connect.microsoft.com/VisualStudio/feedback/details/435783/entity-framework-conceptual-model-doesnt-support-string-equals-via-linq

http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/3810aa67-f6fe-4624-a14b-eaaa0e05ddcd

EF4 Linq Oracle11g making queries none case-sensitive

LINQ to Entities case sensitive comparison