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.