CODEWITHSUNDEEP
c#linqentity-framework
ygoe
ygoe

Reputation: 20414

Case-insensitive string.Contains for both IEnumerable and IQueryable

I'm writing a method that converts a custom language query into an Expression tree so that it can be used with Where extension methods on arbitrary collections. These collections can be IEnumerable<T> from memory lists, or IQueryable<T> for database queries.

My filter logic involves a lot of string contains checks to check whether the searched string occurs in some property of each object instance. I'm now using the standard call to string.Contains for that, which should translate to SQL LIKE '%...%' for SQL Server (and probably others as well) which should be case-insensitive in most database systems by default. But the .NET implementation of that method does something else: It is case-sensitive and will return other (less) objects from the collection because it's a more restrictive comparison.

I've spent the last couple hours finding and reading about this topic. ToLower and ToUpper won't work "properly" in Turkey. Is ToLowerInvariant supported for SQL generation? IndexOf sounds like a better alternative. string.Contains calls string.IndexOf internally. IndexOf has another overload that accepts a StringComparison value. But will that be supported for SQL generation? How would it translate if I specified Ordinal instead of OrdinalIgnoreCase?

This is all very frustrating. Databases are different from memory objects in .NET. But after all, Entity Framework merges both worlds and brings database queries directly into C#. It will cause all sorts of bugs if the semantics of common operations change depending on how they're executed at runtime.

Upvotes: 2

Views: 1329

Answers (1)

Guvante
Guvante

Reputation: 19221

There is no generally accepted general solution to the problem. You have to be aware of what you are doing and where you are doing it unfortunately.

The core reason why is that SQL Server is not consistent with how it handles these kinds of queries. It is consistent with a consistent configuration, but that configuration isn't known for certain in the .NET environment.

There is no general way to know how SELECT * FROM users WHERE name LIKE '%foo' without actually running that query.

The best solution is to avoid any attempts to run queries like that locally, simply always call the database.

Upvotes: 2

Related Questions