11

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

Community
  • 1
  • 1
Robin Dorbell
  • 1,569
  • 1
  • 13
  • 26
  • Your similar question is a dupe of [LINQ Contains Case Insensitive](http://stackoverflow.com/questions/3360772/linq-contains-case-insensitive) It has an IndexOf solution. You might want to try that one – Conrad Frix Aug 14 '14 at 14:40
  • So you mean the IndexOf solution wouldn't tell entity framework to retrieve every record before comparing? – Robin Dorbell Aug 14 '14 at 14:41
  • Did you read this answer about different languages? https://stackoverflow.com/questions/444798/case-insensitive-containsstring it implies you should use CultureInfo class instead. – Dave C Aug 14 '14 at 14:42
  • @JiggsJedi well if IndexOf is used, one can pass in `StringComparison .InvariantCultureIgnoreCase` so a CultureInfo class doesn't need to be used directly – Conrad Frix Aug 14 '14 at 14:45
  • @JiggsJedi I have read it, yes. It does not solve my problem however, since the problem I'm having is that Entity Framework somehow compares with case sensitivity when my database is case insensitive. – Robin Dorbell Aug 14 '14 at 14:47
  • 2
    Are you certain the search is executing on the server? Check the type of "_collection"? If it is `IEnumerable>` the query will execute in the C# code, not on the database server (and be case sensitive). If it is `IQueryable>` then it MAY be executing on the database server. – Grax32 Aug 14 '14 at 14:52
  • 1
    @Grax That might be it. The collections type is `ICollection`. – Robin Dorbell Aug 14 '14 at 14:55

3 Answers3

14

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
Ben
  • 5,525
  • 8
  • 42
  • 66
  • Alright! Thanks for a thorough answer. Very interesting to know. This however was never my problem apparently. The problem being the use of ICollection on my collection, resulting in a comparison on the server, which is Case Sensitive. – Robin Dorbell Aug 14 '14 at 15:18
  • Ah, well where did your collection come from then? If it is a collection then it's already in memory so calling `.ToUpper()` on things won't make mush difference. – Ben Aug 14 '14 at 15:31
11

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?

Community
  • 1
  • 1
Dunc
  • 18,404
  • 6
  • 86
  • 103
-1

Use string.Equals 

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

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

Use StringComparision.CurrentCulture for Case Sensitive.

collection.Where(c => string.Equals(c.Name, searchTerm, StringComparison.CurrentCulture));
Darshan Joshi
  • 11
  • 3
  • `Equals` would try to match the word exactly - which is not what I wanted. The problem was as Dunc wrote, that the query was done after fetching everything from the server. – Robin Dorbell Feb 04 '16 at 16:08
  • Entity Framework 5.0 and later throw an exception at string.Equals with the StringComparison overload. https://learn.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity – Gregor A. Lamche May 24 '21 at 13:46