12

How to make LINQ case sensitive and NOT case sensitive depending on the situation?

I'm using sql server 2008 and Entity Framework 4.0.

I changed the COLLATION to make SQL Server case sensitive. so that for scenarios like these:

 query = query.Where(x => x.Username == username);

it works great. However I need to be able to pull out data from db ignoring case when searching by subject (or name or similar) like so:

query = query.Where(x => (x.Name.Contains(Name)));

which doesn't work when record is "TestString" and i'm looking for "test" or "Test" or similar. How would i make it so that when it would find a text or part of a string in a text? thanks

Snowbear
  • 16,924
  • 3
  • 43
  • 67
ShaneKm
  • 20,823
  • 43
  • 167
  • 296
  • Possibly related: http://stackoverflow.com/questions/3843060/linq-to-entities-case-sensitive-comparison – user Feb 24 '11 at 12:31
  • Use the ToUpper/Lower solution or write a SQL query like this instead:`SELECT * FROM tblUser WHERE tblUser.userName = 'test' COLLATE Finnish_Swedish_CI_AS` – Magnus Feb 24 '11 at 12:39

4 Answers4

19

LINQ has no concept of case sensitivity, it only cares about boolean evaluation. So if you want to ignore case, you should do something like:

query = query.Where(x => (x.Name.ToLower().Contains(Name.ToLower())));

Chances are you will want to pass a CultureInfo to ToLower() (or use ToLowerInvariant()), and you might want to cache the result of Name.ToLower() so as to not have to perform that operation a potentially large number of times, but this should get you started.

user
  • 6,897
  • 8
  • 43
  • 79
7
query = query.Where(x => string.Equals(x.Name, Name, StringComparison.CurrentCultureIgnoreCase));
Ivan Farkas
  • 351
  • 1
  • 4
  • 10
2

Read my reply to this:

String.Equals() not working as intended

It isn't the reply you wanted, probably :-)

Ah... and if you have to convert to the same case to make comparisons, ToUpper is better than ToLower. Don't ask me why. But you can read here: Case insensitive string compare in LINQ-to-SQL

Community
  • 1
  • 1
xanatos
  • 109,618
  • 12
  • 197
  • 280
2

Queryable.Contains has an overload taking an IEqualityComparer<T> used for comparision. See msdn. If you supply a case insensitive comparer, this should work - I'm quite sure there is one in the framework already.

Femaref
  • 60,705
  • 7
  • 138
  • 176