LINQ to Entities – Case Insensitive Searching with Contains

asp.netc++linqlinq-to-entities

i am trying to query my resultset like this in linq to entities;

var categoriesList = _catRepo.GetAllCategories();


 filteredCategories = categoriesList.Where(c=> c.CategoryName.Contains("for"));

However, i don't get any result becuase the CategoryName is For(Upper Case) in the database. I have also checked the sql server collation and it is set to _CI_AS. I have no idea how to use contains to filter case insensitive string?
I want basically if someone type like;

 filteredCategories = categoriesList.Where(c=> c.CategoryName.Contains("for"));

filteredCategories = categoriesList.Where(c=> c.CategoryName.Contains("For"));

The result should be the same

Best Answer

Try this

filteredCategories = categoriesList.Where(c=>
 c.CategoryName.IndexOf("for", StringComparison.OrdinalIgnoreCase) >= 0)

Contains method works as shown below

public bool Contains(string value)
{
   return this.IndexOf(value, StringComparison.Ordinal) >= 0;
}

Related Solutions

C# – LINQ Contains Case Insensitive

fi => fi.DESCRIPTION.ToLower().Contains(description.ToLower())

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");