LINQ – Case Sensitivity in LINQ Queries

linqlinq-to-entitiessql

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

Best Answer

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.

Related Solutions

C# – LINQ to Entities Case Sensitive Comparison

That's because you are using LINQ To Entities which is ultimately convert your Lambda expressions into SQL statements. That means the case sensitivity is at the mercy of your SQL Server which by default has SQL_Latin1_General_CP1_CI_AS Collation and that is NOT case sensitive.

Using ObjectQuery.ToTraceString to see the generated SQL query that has been actually submitted to SQL Server reveals the mystery:

string sqlQuery = ((ObjectQuery)context.Thingies
        .Where(t => t.Name == "ThingamaBob")).ToTraceString();

When you create a LINQ to Entities query, LINQ to Entities leverages the LINQ parser to begin processing the query and converts it into a LINQ expression tree. The LINQ expression tree is then passed to Object Services API, which converts the expression tree to a command tree. It is then sent to the store provider (e.g. SqlClient), which convert the command tree into the native database command text. Query get executed on the data store and the results are Materialized into Entity Objects by Object Services. No logic has been put in between to take case sensitivity into account. So no matter what case you put in your predicate, it will always treat as the same by your SQL Server unless you change your SQL Server Collates for that column.

Server side solution:

Therefore, the best solution would be to change the collation of the Name column in the Thingies table to COLLATE Latin1_General_CS_AS which is case sensitive by running this on your SQL Server:

ALTER TABLE Thingies
ALTER COLUMN Name VARCHAR(25)
COLLATE Latin1_General_CS_AS

For more information on the SQL Server Collates, take a a look at SQL SERVER Collate Case Sensitive SQL Query Search

Client-side solution:

The only solution that you can apply on client side is to use LINQ to Objects to do yet another comparison which doesn't seem to be very elegant:

Thingies.Where(t => t.Name == "ThingamaBob")
        .AsEnumerable()
        .First(t => t.Name == "ThingamaBob");

LINQ – Making LINQ Case Sensitive in SQL Server

That's not a point for a linq query.

It's a setting within the sql server 2008. Microsoft Sql Server 2008 handles string comparisons by default case invariant.

Have a look at: http://msdn.microsoft.com/en-us/library/ms141038.aspx