Linq to Entities Case Sensitive Comparison

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 case sensitive

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.

Entity Framework LINQ contains not case insensitive

From the comments, it sounds like the OP is casting the IQueryable list to an ICollection first, meaning that any subsequent LINQ is running "locally" rather than having the chance to be converted to SQL.

For example,

    // Should be IQueryable<T>
    ICollection<User> users = context.Users;

    // This is executed in code rather than SQL, and so is case SENSITIVE
    users = users.Where(c => c.Name.Contains(searchTerm));

This may have helped debug the issue: How do I view the SQL generated by the entity framework?

Entity Framework core - Contains is case sensitive or case insensitive?

It used to be the case for older versions of EF core. Now string.Contains is case sensitive, and for exemple for sqlite it maps to sqlite function `instr()' ( I don't know for postgresql).

If you want to compare strings in a case-insensitive way, you have DbFunctions to do the jobs.

context.Counties.Where(x => EF.Functions.Like(x.Name, $"%{keyword}%")).ToList();

UPDATE to @Gert:

A part of the assumption in the question is incorrect. string.Contains does NOT convert into a LIKE expression even though it USED to be the case in ef core versions <= 1.0 (I think).

• In SQLServer string.contains converts into CHARINDEX(), in oracle and sqlite into instr() which are case sensitive by default UNLESS db or column collation is defined otherwise ( Again, I don't know for postgresql ).
• In all cases EF.Functions.Like() converts into a SQL LIKE expression which is case-insensitive by default unless db or column collation is defined otherwise.

So yes it all goes down to collation but - correct me if I'm wrong - in a way the code can have an influence on the case-sensitive/insensitive search depending on which one of the above method you use.

Now, I might not be completely up to date but I don't think EF core migrations deal with DB collation naturally and unless you've already created the table manually you will end up with the default collation (case-sensitive for sqlite and I honestly don't know for the others).

Getting back to the original question you have at least 2 options to perform this case-insensitive search if not 3 in a future release :

1. Specify the column collation on creation using DbContext.OnModelCreating() using this trick
2. Replace your string.Contains by EF.Functions.Like()
3. Or wait for a promising feature still in discussion : EF.Functions.Collate() function

How to compare case sensitive string in EF?

You should not query on the password. You should retrieve the User object and do a password compare locally, because SQL server will do a case insensitive compare for you by default (unless you change your database settings, which is not something you should take lightly).

var r = db.ST_Users.SingleOrDefault(x => x.Username == username);

return r != null && r.Password == password;

Besides, you seem to be storing plain passwords in your database. Depending on the type of application, this might not be a good idea. Try hashing them with a salt. Lots of good information to find about that here on Stackoverflow. For instance, take a look at this question and this website.

Entity Framework Case Sensitive Query

I Solve this issue with same change:

1. change Key data type to varchar

2. and execute SQL Query with SqlQuery<T>:

   var post = await _uow.Database
               .SqlQuery<PostUrlDto>(
                   "SELECT Id , Title FROM Posts WHERE [Key] = @postkey COLLATE SQL_Latin1_General_CP1_CS_AS",
                   new SqlParameter("postkey", postkey)).SingleOrDefaultAsync()
   

---

Related Topics