LINQ to Entities case sensitive comparison

Question

This isn't a case-sensitive comparison in LINQ to Entities:

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

How can I achieve case sensitive comparison with LINQ to Entities?

Purpleness

@Ronnie: are you sure about that? Do you mean case insensitive comparison? – Expander

Absolutely sure. No I don't mean that. – Purpleness

It's already case-sensitive, that's why we believe that you're confusing "case-sensitive" with "case-insensitive". – Rottweiler

No, on my computer running EF 4.0 w/ SQL Server 2008 R2, the above is case insensitive. I know lots of places say that EF is default case sensitive, but that's not what I have experienced. – Dumpy

EF should have nothing to do with it. What you have is a method (in lambda form) that compares two System.String objects with the == operator. This is, and had better always be a case-sensitive comparison. Something else is going on in your case. – Rottweiler

Have a look at the last answer on This page. It applies to L2Sql but i believe it will also work in L2E although I have not tried it yet. – Felonry

Won't that depend on the underlying database? – Chloechloette

@codymanix: That's a good question! Does Linq to EF translate the lambda expression for a DB query? I don't know the answer. – Rottweiler

@MassoodKhaari -- No that's a horrible suggestion. There are plenty of cultures where that doesn't work; it would only work for, e.g. "en-US". – Attemper

Also @RonnieOverby I think everyone is confused about your wording. Saying "This isn't a case-sensitive comparison in LINQ to Entities" also led me to believe at first that you actually meant the opposite because I interpreted it to mean that "This isn't a case-sensitive comparison in C# (i.e. L2E)" and not "This doesn't result in a case sensitive comparison when translated into a query and run against the database." – Attemper

@Attemper You're right. I'd misinterpreted the question. I've deleted my comment now. – Inkblot

Answer 1

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

Byrle

I am generating the database schema with Entity Framework, so a solution using my calling code would be best. I guess I will do a check after the results have come back. Thanks. – Purpleness

No problem. Yes, that is correct and I've updated my answer with a client side solution, however it isn't very elegant and I still recommend to use the data store solution. – Byrle

+1 good answer. For anyone stopping by, the client side solution is only for tiny sets of data (it's retrieving all the data & then filtering at the client side). – Visitor

@Visitor This is not completely true: It does not fetch ALL data, it fetches only the data that matches case insensitively, and after that it gets filtered again on the client case sensitively. Of course, if you happen to have thousands of entries that match case insensitive, but only one of them is the correct one case sensitive, then it's a lot of overhead. But I don't think that reality will present such scenarios... :) – Monotonous

@MassoodKhaari That solution you posted would make the it Case Insensitive because you're lower casing both sides of the comparison. The OP needs a case sensitive comparison. – Greeley

@Greeley Oh, you're right. Really sorry about my naive mistake. Wondering why I haven't noticed. – Inkblot

I LOVE Linq but this one bit me for a day or two now. Finally narrowed it down to this. The elegance LINQ embodies hides the nitty gritty which sometimes bites ya! But at least there is another "client-sideish" solution for Entity Framework using Migrations. It essentially says do a migration that executes the SQL in this answer (and it worked for me). talksharp.com/…. Don't forget to add the inverse in the .Down() method! – Dickey

I don't have ToTraceString() extension method. It's not recognized. ? – Gordan

you could send a hash of your string to match with a hash of the DB string, chances of different strings to match hashes are very small – Kernel

"Therefore, the best solution would be to change the collation of the Name column in the Thingies table to COLLATE Latin1_General_CS_AS" - I don't think that this is the best. Most of the time I need case insensitive LIKE filter (.Contains()) but sometimes it should be case sensitive. I'll try your "Client-side solution" - it's much more elegant for my use case I think ( would be nice to understand what it does but you can't have it all :) ). – Myrtamyrtaceous

Server side solution was by far the easiest and ensured that any sprocs also benefit from the change. Thank you! – Macrography

Answer 2

WHERE conditions in SQL Server are case insensitive by default. Make it case sensitive by changing the column's default collations (SQL_Latin1_General_CP1_CI_AS) to SQL_Latin1_General_CP1_CS_AS.

The fragile way to do this is with code. Add a new migration file and then add this inside the Up method:

public override void Up()
{
   Sql("ALTER TABLE Thingies ALTER COLUMN Name VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL");
}

But

You can create custom annotation called "CaseSensitive" using the new EF6 features and you can decorate your properties like this:

[CaseSensitive]
public string Name { get; set; }

This blog post explains how to do that.

Cleisthenes

In that article have a bug – Liebknecht

Answer 3

You can add [CaseSensitive] annotation for EF6+ Code-first

Add this classes

[AttributeUsage(AttributeTargets.Property, AllowMultiple = true)]
public class CaseSensitiveAttribute : Attribute
{
    public CaseSensitiveAttribute()
    {
        IsEnabled = true;
    }
    public bool IsEnabled { get; set; }
}

public class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(AlterColumnOperation alterColumnOperation)
    {
        base.Generate(alterColumnOperation);
        AnnotationValues values;
        if (alterColumnOperation.Column.Annotations.TryGetValue("CaseSensitive", out values))
        {
            if (values.NewValue != null && values.NewValue.ToString() == "True")
            {
                using (var writer = Writer())
                {
                    //if (System.Diagnostics.Debugger.IsAttached == false) System.Diagnostics.Debugger.Launch();

                    // https://github.com/mono/entityframework/blob/master/src/EntityFramework.SqlServer/SqlServerMigrationSqlGenerator.cs
                    var columnSQL = BuildColumnType(alterColumnOperation.Column); //[nvarchar](100)
                    writer.WriteLine(
                        "ALTER TABLE {0} ALTER COLUMN {1} {2} COLLATE SQL_Latin1_General_CP1_CS_AS {3}",
                        alterColumnOperation.Table,
                        alterColumnOperation.Column.Name,
                        columnSQL,
                        alterColumnOperation.Column.IsNullable.HasValue == false || alterColumnOperation.Column.IsNullable.Value == true ? " NULL" : "NOT NULL" //todo not tested for DefaultValue
                        );
                    Statement(writer);
                }
            }
        }
    }
}

public class CustomApplicationDbConfiguration : DbConfiguration
{
    public CustomApplicationDbConfiguration()
    {
        SetMigrationSqlGenerator(
            SqlProviderServices.ProviderInvariantName,
            () => new CustomSqlServerMigrationSqlGenerator());
    }
}

Modify your DbContext, add

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Add(new AttributeToColumnAnnotationConvention<CaseSensitiveAttribute, bool>(
                "CaseSensitive",
                (property, attributes) => attributes.Single().IsEnabled));
        base.OnModelCreating(modelBuilder);
    }

Then do

Add-Migration CaseSensitive

Update-Database

based on article https://milinaudara.wordpress.com/2015/02/04/case-sensitive-search-using-entity-framework-with-custom-annotation/ with some bug fix

Liebknecht

Answer 4

The answer given by @Morteza Manavi solves the problem. Still, for a client-side solution, an elegant way would be the following (adding a double check).

var firstCheck = Thingies.Where(t => t.Name == "ThingamaBob")
    .FirstOrDefault();
var doubleCheck = (firstCheck?.Name == model.Name) ? Thingies : null;

Greenburg

It’s not so elegant because what if the single record returned is not an exact case sensitive match? Do we go back to the database? There may be a case-sensitive match we did not retrieve. – Duclos

This is quite the hack, but in some cases it could actually be a easy workaround (still a hack tho) that does not require changing database collation for a single case-sensitive query. Thanks for the idea :) – Workingman

Answer 5

Yes, it is possible and well documented.

https://learn.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity

Option 1 - It is possible to specify collation of a column:

modelBuilder.Entity<Customer>().Property(c => c.Name)
    .UseCollation("SQL_Latin1_General_CP1_CS_AS");

Option 2 - or of the whole database:

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Option 3 - Another option is to specify the collation for a single query:

var customers = context.Customers
    .Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John")
    .ToList();

All the options use the SQL_Latin1_General_CP1_CS_AS collation, which is case sensitive.

Begotten

Thank you, I have updated the answer with a case sensitive collation. – Begotten

Answer 6

I liked Morteza's answer, and would normally prefer to fix on server side. For client-side I normally use:

Dim bLogin As Boolean = False

    Dim oUser As User = (From c In db.Users Where c.Username = UserName AndAlso c.Password = Password Select c).SingleOrDefault()
    If oUser IsNot Nothing Then
        If oUser.Password = Password Then
            bLogin = True
        End If
    End If

Basically, first checking if there is a user with required criteria, then check if the password is the same. A little bit long-winded, but I feel it is easier to read when there may be a whole bunch of criteria involved.

Toadeater

This answer implies that you are storing passwords as plain text in your database which is a huge security vulnerability. – Clash

@JasonCoyne The password he is comparing with could already be hashed – Dealing

Answer 7

Use string.Equals

Thingies.First(t => string.Equals(t.Name, "ThingamaBob", StringComparison.CurrentCulture);

Also, you don't have to worry about null and get back only the information you want.

Use StringComparision.CurrentCultureIgnoreCase for Case Insensitive.

Thingies.First(t => string.Equals(t.Name, "ThingamaBob", StringComparison.CurrentCultureIgnoreCase);

Calf

Equals() cannot be converted to SQL... Also if you try and use the instance method, the StringComparison is ignored. – Airy

Have you tried this solution? I tried this at my end at working fine with EF. – Calf

Answer 8

Not sure about EF4, but EF5 supports this:

Thingies
    .First(t => t.Name.Equals(
        "ThingamaBob",
        System.StringComparison.InvariantCultureIgnoreCase)

Armidaarmiger

Curious what sql that generates. – Purpleness

I checked this with EF5, it simply generated a WHERE ... = ... in SQL. So again, this is dependent on the collation settings on the SQL server side. – Monotonous

Even with a case-sensitive collation in the DB I couldn't get this or any of the other StringComparison enums to make a difference. I've seen enough people suggesting this kind of thing should work to think the problem is somewhere in the EDMX file (db-first), although #841726 – Preconceive

Answer 9

Neither of the StringComparison.IgnoreCase worked for me. But this did:

context.MyEntities.Where(p => p.Email.ToUpper().Equals(muser.Email.ToUpper()));

Rangel

This wouldn't help with the question that was asked, which is, How can I achieve case sensitive comparison – Epilate