Combining LINQ Queries (or, When to Call ToList)
LINQ with Entity Framework has become so common that, when I'm looking at a client's code I'm finding some pretty scary-looking LINQ queries running against Entity Framework. Since I'm a big fan of Really Obvious Code (ROC) -- ROC Rocks! -- I hate having to parse out these queries to try and figure out what the query is doing (and what's wrong with it).
You might be tempted to write a comment to explain the query (but I don't like that, either).
The right answer is to break down your complicated query into several smaller, easily readable queries that build on each other. While this isn't a particularly complicated example, these two queries find all of the people whose last name is "Vogel" and work for PH&VIS:
Dim persVogel = From p In db.People
Where p.LastName = "Vogel"
Select p
Dim persVogelPHVIS = From pp In persVogel
Where pp.Company.Name = "PHVIS"
Select pp
While this looks inefficient, it's not. It's important to remember that a LINQ query doesn't result in any data retrieval: At this point in my sample code, my persVogel and persVogelPHVIS variables are just holding LINQ queries; the variables aren't holding the results of those LINQ queries (at least, not holding the results yet).
It isn't until you start working with the individual objects that Entity Framework will finally issue a SQL statement against your database. When that SQL query is finally issued, Entity Framework will take care of collapsing those two LINQ queries into one SQL statement for you.
One way to trigger that retrieval is to use the ToList method. If my LINQ queries were in a method, then I might finish the method with code like this:
Return persVogelPHVIS.ToList()
At this point, when the ToList method executes, Entity Framework will finally retrieve the data and create a collection of objects from that data.
Besides, those collections of objects are more generally useful than the LINQ queries. I've had numerous clients call me up to ask why they're getting messages like "Unable to cast object of type..." when passing a LINQ query to some function. The usual solution is to add a call to ToList to force Entity Framework to retrieve the data.
Posted by Peter Vogel on 01/19/2016