LINQ to SQL - Am I Hitting The Database?
(
Aug 04 2008 - 10:42:25 PM by
Timothy Khouri) - [
print blog
post]
Like most other developers who have been using LINQ for a while (and in this particular case, LINQ to SQL) - I've unknowingly made a few 'bad queries' that performed great at first, but eventually made some pages crawl. This post isn't necessarily about performance, but if you don't know how to answer that question ("am I hitting the database"), then you'll likely find yourself in a world of problems down the road when you're using LINQ to SQL.
The Scenario - "Method ... has no supported translation to SQL"
If you've seen this error before, then you probably already know where I'm going with this post. But, for those of you who haven't run across this little gem of an error, I'll fill you in.
When you write LINQ statements that act against a LINQ to SQL data context - eventually your expression will be translated into SQL statements and executed against the database. Example:
this.MyGridView.DataSource = context.Persons
.Where(dude => dude.FirstName.StartsWith("T"));
will translate into...
SELECT [t0].[ID], [t0].[FirstName], [t0].[LastName]
FROM [dbo].[People] AS [t0]
WHERE [t0].[FirstName] LIKE @p0
What you might be taking for granted here is that the developers at Microsoft (the LINQ to SQL team in particular) spent a lot of time making 'translations' for a lot of .NET functions out there (such as "String.StartsWith"). What this means is that you as a developer may be thinking that even your functions will be translated just like magic. This, of course, isn't the case. So, the following LINQ query:
this.ExpensiveOrdersGridView.DataSource = context.Persons
.SelectMany(person => person.GetExpensiveOrders());
will result in the following error:
Where There Be Dragons
You may be wondering, "what's the problem... if my method doesn't work, then so what?"... and that's a great question. The 'problem' here is that at first you may not fully understand that LINQ to SQL isn't able to translate this method at all, so you may tinker around a bit and find a way to "make it work."
This slight change to the LINQ query will work, and will give you the results you expect:
this.ExpensiveOrdersGridView.DataSource = context.Persons
.ToArray()
.SelectMany(person => person.GetExpensiveOrders());
... but this is a huge sin. Why you might ask? Let's break down what we just told LINQ to do, and you'll see:
this.ExpensiveOrdersGridView.DataSource = context.Persons
.ToArray()
.SelectMany(person => person.GetExpensiveOrders());
Conclusion
There isn't much of a moral here other than "make sure you understand what your query is doing." Queries like these can linger for a while in your code without you seeing a performance problem. Then, as your database continues to grow, your app will eventually crawl to uselessness.
Please, don't be afraid of LINQ, and don't be afraid of LINQ to SQL. Just make sure you understand what you're doing (as with any other new technology).