LINQ to SQL and Entity framework allow us to build a query, which gets translated into an expression tree, and executed once the full query is built. The beauty is that we can build up a query using multiple expressions and Lambdas, without actually querying the data. Since these types of queries are delay loaded, why not avoid executing them until the last possible moment? Read on to see why this is usually a bad idea.
First, let’s take a look the code for a repository method that builds a query, executes the query, and returns the results in a list:
public IEnumerable<Cat> FindAllCats()
{
var query = from c in db.Cats
select c;
return query.ToList();
}
The “ToList” is forcing the IQueryable<Cat> query to execute and put the results in a list immediately. However, we know that IQueryable<T> inherits from IEnumerable<T>, so what happens if we avoid the list creation completely?
public IEnumerable<Cat> FindAllCats()
{
var query = from c in db.Cats
select c;
return query;
}
In this scenario, our method is returning the same interface, but the underlying type is now a LINQ database iterator instead of a List<T>.
Delaying execution can lead to multiple executions
If the code is not explicitly putting the results into a list, we’re actually passing back a form of an iterator. This works great if we only need to execute the query once. However, if we iterate through the list more than once, we actually end up executing our query multiple times. This can obviously lead to poor performance.
If you’re writing fast queries, you may not even notice if they’re being called too many times. However, there may be a worse problem lurking in your code. Each time you iterate through the enumerator, you’re getting a different set of objects. The same query is being made with the same results, but the objects are re-built each time. This leads to objects that are equivalent, but not the same. For example, you may get back Cat objects with the names “Bill” and “Ted”, but if you actually check them for equality using “==”, they will not be the same object instance. Correction: Scott points out in the comments that this isn’t necessarily the case. Keep in mind that it can still occur if projecting types and not working with the original objects.
Delaying execution may mean you no longer have a database connection when attempting to execute the query
If you delegate the task of initiating your query to another layer, you better be sure that the database connection is still around, and is in a queryable state. If you’re using the standard repository pattern and a short-lived database connection pattern, you may quickly run into problems when you try to iterate through the results of the enumerator you receive from your repository layer.
Conclusion
If you’re thinking about moving the execution of your queries to another layer, make sure you understand the consequences. You’ll need to weigh those consequences against the tiny benefit that you’ll receive from the delayed execution. There may be cases where delaying the execution or possibly avoiding it completely will improve your application, but those are probably very rare cases.
The query does execute twice, but with Linq to SQL and the Entity Framework, you’ll get the same object instances back (unless your repository is doing some sort of projection to a new type).
Both L2S and EF use an identity map to track the objects each DataContext / ObjectContext instantiates.
See: http://odetocode.com/Blogs/scott/archive/2008/12/07/12372.aspx
Thanks Scott, that makes sense. I believe I was doing a projection into a new type, which would explain the behavior I was seeing.
Also note that you’ve moved the exception handling responsibility when you return the IEnumberable. If there is an exception during the ToList(), such as a SQLException, the proper code layer can either handle that error properly or convert it to an exception that the calling layer can handle properly. If you return the IEnumberable then the exception is thrown during the iteration so the calling code must handle all exceptions that the iteration may throw.
Great point logicalmind. That’s concept is probably enough for its own post!
hmmm… I’m thinking about the following scenario:
I have a table in my database with, lets say 50 000 rows. I´m using a layered design with dal, bll and presentation.
I want to display 20 rows from the database, sorted by i.e. expireDate. I have tested there approaches:
DAL with IQueryble that returns an IQueryble of cmsDocuments:
var res = (from docs in db.cmsDocuments
select docs);
return res;
Then binding this to a grid in the presentation.
gw.DataSource = cmsDocuments.GetAll().OrderBy(x => x.documentUser == 1).OrderBy(x=>x.expireDate).Take(15);
This job gets done in around 0.04 seconds.
But using another approach would be to call the ToList() method on the res-collection in the DAL:
var res = (from docs in db.cmsDocuments
select docs);
return res.ToList();
This will execute the query and get me all the 50 000 rows, then in my presentation (or most likey BLL) I would to the sorting:
gw.DataSource = cmsDocuments.GetAll().OrderBy(x => x.documentUser == 1).OrderBy(x => x.expireDate).Take(15);
This job gets done in around 0.35 seconds. Big difference.
My question – is the best solution to put lots of methods in the DAL, like
GetTopTeen, GetAllActive etc, and then sort dem later on?
OR is it okey to send IQuerybles to the BLL and from there make all these methods?
Using ToList() with Entity Framework or Linq To SQL is wrong. As Markus said, we should create the query with sorting and pagination, followed by projection, so that after execution only the necessary data is taken from the database.
A database table might have millions of records, and yet you are interested in a page of only 100 pages. Trying to do a “ToList()” might result in an OutOfMemoryException and in a huge time to get (download) the data from the database.