C# LINQ – Write EF to Include Comparison by Lower Case

c++entity-frameworklinq

I have this query: select lower(Name) from User

How to make the same with linq to EF avoiding to user linq to object.

Best Answer

var query = context.Users.Select(u => u.Name.ToLower());

Entity Framework can translate String.ToLower into SQL. This query will be translated as:

SELECT 
LOWER([Extent1].[Name]) AS [C1]
FROM [dbo].[Users] AS [Extent1]

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

C# – Can LINQ Expression Be Case Insensitive?

You could convert both values to lowercase before doing the comparison. Here's an example for Contains:

case WhereOperation.Contains:
    var toLower = Expression.Call(memberAccessToString,
                                  typeof(string).GetMethod("ToLower", System.Type.EmptyTypes));
    condition = Expression.Call(toLower,
                                typeof(string).GetMethod("Contains"),
                                Expression.Constant(value.ToString().ToLower()));
    lambda = Expression.Lambda(condition, parameter);
    break;

Note, however, that this won't pass the Turkey test.