Entity Framework LINQ Contains Case Insensitivity

Question

I have “tear off my hair” - a problem with the Entity Framework, and I just can't find a solution.

What I want to do is compare the strings for the search function that I run on the server. This is basically: collection.Where(c => c.Name.Contains(searchTerm)); where searchTerm is the string passed by the user.

Everywhere I watch it:

• Change both lines using toUpper () or

• Set case-insensitive.

However, none of them relates to my case. Here's a similar question that has no answer: Entity Framework - case insensitive Contains?

Using the first alternative will result in each row in the database, and then execute toUpper () to see if it matches. This is unacceptable in performance.

The second approach, most likely, is the right decision, but for some reason does not work. I have two databases. One local and one remote. The remote MSSQL database is configured to sort: Finnish_Swedish_CI_AS, which means it is case insensitive? The local database is an automatically generated localDB for which the Case Sensitive property is set to False.

Regardless of which of these two databases I use, it is still always case sensitive for users.

Can someone explain why this is happening so that I can continue my miserable life?

Regards, Robin Dorbell

Answer 1

From the comments, it seems that the OP is leading the IQueryable list in ICollection, which means that any subsequent LINQ is launched "locally", and does not have the ability 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 could help debug the problem: How to view the SQL generated by the entity framework?

Answer 2

It was never case sensitive for me, but I think that's how I set up my database. You can definitely use your first option of converting both of them to uppercase, EF does not pull them into memory for this, it simply informs the SQL server about 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 very similar):

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

Answer 3

Use string.Equals

 collection.Where(c => string.Equals(c.Name, searchTerm, StringComparison.CurrentCultureIgnoreCase)); 

In addition, you do not need to worry about null and return only the information you need.

Use StringComparision.CurrentCulture for case sensitivity.

 collection.Where(c => string.Equals(c.Name, searchTerm, StringComparison.CurrentCulture));