Entity Framework LINQ contains not case insensitive

Question

I'm having some 'tear my hair out'-problem with Entity Framework and I just can't find a solution.

What I want to do is compare strings for a search function I'm running on the server. It's basically: collection.Where(c => c.Name.Contains(searchTerm)); where searchTerm is a string passed by the user.

Everywhere I look it's either:

1. Change both string with toUpper(), or

2. Set collation to a Case Insensitive one.

However neither of these apply to my case. Here's a similar question which doesn't have an answer: Entity Framework - case insensitive Contains?

Using the first alternative would result in getting every row in the database, and then perform toUpper(), to see if it's a match. This is unacceptable performance-wise.

The second approach seems more likely to be a valid solution, but does for some reason not work. I have two databases. One local and one remote. The remote MSSQL database is set to collation: Finnish_Swedish_CI_AS, which means it's case insensitive? The local database is an auto-generated localDB with the property "Case Sensitive" set to False.

No matter which of these two databases I use it's still always Case Sensitive for the users.

Can someone please explain why this is happening so I can go on with my miserable life?

Kind regards, Robin Dorbell

Answer 1

It's never been case sensitive for me, but I guess that is just how I set my database up. You can definitely use your first option of converting them both to upper case, EF doesn't pull them into memory to do that, just informs SQL server to do it. For example:

string searchTerm = "Some Text";

dbcontext.Table.Where (t => t.Column.ToLower().Contains(searchTerm.ToLower()));

Produces the following SQL (ish, i did this with linqtosql but EF should be pretty similar):

-- Region Parameters
DECLARE @p0 NVarChar(1000) = '%some text%'
-- EndRegion
SELECT *
FROM [Table] AS [t0]
WHERE LOWER([t0].[Column]) LIKE @p0

Answer 2

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?