Linq to SQL Deferred Loading Gotcha

Posted by Tom on 2012-01-17 09:10

So here's a good one. Say we have some data in a table:

Id | Name
---+-------
 1 | Steve
 2 | Claire
 3 | Sarah 
 4 | Dave
 5 | Katie

And we run a LINQ-to-SQL query against it.

var results = dataContext.Persons.Where(x => x.Name.Contains("dave"));

It does what you'd expect it to do. Our database is case insensitive, so it matches as such.

Func<Persons, bool> predicate = x => x.Name.Contains("dave"));
var results = dataContext.Persons.Where(predicate);

Just when you think you have it licked, the above returns no rows. The problem is that the fact that we're using the predicate through a variable means that the data is grabbed from the DB before the Where() and then is filtered on the C#-side of the fence, which means your string comparisons are now case sensitive.

It's a slightly odd example (just a specific one that bit me on the arse), but it could have more serious ramifications. While this one fails pretty obviously, how about this: rather than getting 10 rows from the database you could be getting a million and then processing the lot of them in code.

Between enigmatic stuff like this, DataContext lifetime, detached objects and the eternally fun connection string pinata game, I still don't trust Linq to SQL completely.