Entity Framework and differences between Contains between SQL and objects using ToLower

Question

I have run into an "issue" I am not quite sure I understand with Entity Framework. I am using Entity Framework 4 and have tried to utilize a TDD approach. As a result, I recently implemented a search feature using a Repository pattern. For my test project, I am implementing my repository interface and have a set of "fake" object data I am using for test purposes.

I ran into an issue trying to get the Contains clause to work for case invariant search. My code snippet for both my test and the repository class used against the database is as follows:

if (!string.IsNullOrEmpty(Description))
{
     items = items.Where(r => r.Description.ToLower().Contains(Description.ToLower()));
}

However, when I ran my test cases the results where not populated if my case did not match the underlying data. I tried looking into what I thought was an issue for a while. To clear my mind, I went for a run and wondered if the same code with EF would work against a SQL back end database, since SQL will explicitly support the like command and it executed as I expected, using the same logic.

I understand why EF against the database back end supports the Contains clause. However, I was surprised that my unit tests did not. Any ideas why other than the SQL server support of the like clause when I use objects I populate in a collection instead of against the database server?

Thanks!

John

Answer 1

LINQ to Entities and LINQ to Objects have different rules. It's that simple. For example, in LINQ to Entities, I can run a query like this:

var foo = Context.Foos.Where(f => f.Bar.Something == bar);

... And if f.Bar happens to be a null reference, this statement will still work just fine, because f.Bar.Something will coalesce to null. If you think about how SQL works, with a LEFT JOIN, this should not be surprising. In LINQ to objects, on the other hand, the same Where expression would throw a null reference exception.

As you've found, there are other areas of difference. Case sensitivity is one. When a LINQ to Entities query is executed, it is transformed to SQL. Equality comparisons are performed based on the collation rules defined in the database, as well as in the SQL. To customize a collation for a database, you generally select a specific collation for a column. On the other hand, to customize collations for objects, you generally pass a comparison function, which would never be accepted in LINQ to Entities, as functions (as opposed to expressions) cannot be transformed into SQL.

There is, however, a way to do case-insensitive comparisons which works in both LINQ providers:

var foo = Context.Foos.Where(f => f.SomeString.Equals(someValue, StringComparison.OrdinalIgnoreCase));