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<T> implements IEnumerable<T>, 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<T> 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

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 2

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.