Should I return IEnumerable<T> or IQueryable<T> from my DAL?

Question

I know this could be opinion, but I'm looking for best practices.

As I understand, IQueryable implements IEnumerable, so in my DAL, I currently have method signatures like the following:

IEnumerable<Product> GetProducts();
IEnumerable<Product> GetProductsByCategory(int cateogoryId);
Product GetProduct(int productId);

Should I be using IQueryable here?

What are the pros and cons of either approach?

Note that I am planning on using the Repository pattern so I will have a class like so:

public class ProductRepository {

    DBDataContext db = new DBDataContext(<!-- connection string -->);

    public IEnumerable<Product> GetProductsNew(int daysOld) {
        return db.GetProducts()
          .Where(p => p.AddedDateTime > DateTime.Now.AddDays(-daysOld ));
    }
}

Should I change my IEnumerable<T> to IQueryable<T>? What advantages/disadvantages are there to one or the other?

Answer 1

HUUUUGGGE difference. I see this quite a bit.

You build up an IQueryable before it hits the database. The IQueryable only hits the DB once an eager function is called (.ToList() for example) or you actually try to pull values out. IQueryable = lazy.

An IEnumerable will execute your lambda against the DB right away. IEnumerable = eager.

As for which to use with the Repository pattern, I believe it's eager. I usually see ILists being passed but someone else will need to iron that out for you. EDIT - You usually see IEnumerable instead of IQueryable because you don't want layers past your Repository A) determining when the database hit will happen or B) Adding any logic to the joins outside the Repository

There is a very good LINQ video that I enjoy a lot- it hits more than just IEnumerable v IQueryable, but it really has some fantastic insight.

http://channel9.msdn.com/posts/matthijs/LINQ-Tips-Tricks-and-Optimizations-by-Scott-Allen/

Answer 2

It depends on what behavior you want.

• Returning an IList<T> tells the caller that they've received all of the data they've requested
• Returning an IEnumerable<T> tells the caller that they'll need to iterate over the result and it might be lazily loaded.
• Returning an IQueryable<T> tells the caller that the result is backed by a Linq provider that can handle certain classes of queries, putting the burden on the caller to form a performant query.

While the latter gives the caller a lot of flexibility (assuming your repository fully supports it), it's the hardest to test and, arguably, the least deterministic.

Answer 3

You can use IQueryable and accept that someone could create a scenario where a SELECT N+1 could happen. This is a disadvantage, along with the fact that you may end up with code that is specific to your repository implementation in the layers above your repository. The advantage of this is that you are allowing the delegation common operations like paging and sorting to be expressed outside of your respository, therefore alleviating it of such concerns. It is also more flexible if you need to join the data with other database tables, as the query will remain an expression, so can be added to before its resolved into a query and hits the database.

The alternative is to lock down your repository so that it returns materialised lists by calling ToList(). With the example of paging and sorting, you will need to pass in skip, take and a sort expression as parameters to the methods of your repository, and use the parameters to return only a window of results. This means that the repository is taking on the responsibility of paging and sorting, and all of the projection of your data.

This is a bit of a judgement call, do you give your application the power of linq, and have less complexity in the repository, or do you control your data access. For me it depends on the number of queries associated with each entity, and combinations of entities, and where I want to manage that complexity.

Answer 4

One more thing to think about: where is your paging/sorting support? If you are providing paging support within your repository, returning IEnumerable<T> is fine. If you are paging outside of your repository (like in the controller or service layer) then you really want to use IQueryable<T> because you don't want to load the entire dataset into memory before it's paged.