Should repositories expose IQueryable to service layer or perform filtering in the implementation?

I'm trying to decide on the best pattern for data access in my MVC application. Currently, having followed the MVC storefront series, I am using repositories, exposing IQueryable to a service layer, w开发者_运维问答hich then applies filters. Initially I have been using LINQtoSQL e.g.

public interface IMyRepository
{
  IQueryable<MyClass> GetAll();
}

Implemented in:

public class LINQtoSQLRepository : IMyRepository
{
   public IQueryable<MyClass> GetAll()
   {
      return from table in dbContext.table
             select new MyClass
             {
                Field1 = table.field1,
                ... etc.
             }
   }
}

Filter for IDs:

public static class TableFilters 
{       
   public static MyClass WithID(this IQueryable<MyClass> qry, string id)
   {
      return (from t in qry
              where t.ID == id
              select t).SingleOrDefault();
   }     
}

Called from service:

public class TableService
{
   public MyClass RecordsByID(string id)
   {
      return _repository.GetAll()
                        .WithID(id);
   }
}

I ran into a problem when I experimented with implementing the repository using Entity Framework with LINQ to Entities. The filters class in my project contains some more complex operations than the "WHERE ... == ..." in the example above, which I believe require different implementations depending on the LINQ provider. Specifically I have a requirement to perform a SQL "WHERE ... IN ..." clause. I am able to implement this in the filter class using:

string[] aParams = // array of IDs
qry = qry.Where(t => aParams.Contains(t.ID));

However, in order to perform this against Entity Framework, I need to provide a solution such as the BuildContainsExpression which is tied to the Entity Framework. This means I have to have 2 different implementations of this particular filter, depending on the underlying provider.

I'd appreciate any advice on how I should proceed from here. It seemed to me that exposing an IQueryable from my repository, would allow me to perform filters on it regardless of the underlying provider, enabling me to switch between providers if and when required. However the problem I describe above makes me think I should be performing all my filtering within the repositories and returning IEnumerable, IList or single classes.

Many thanks, Matt

This is a very popular question. One that I constantly ask myself. I've always felt it best to return IEnumerable rather than IQueryable from a repository.

The purpose of a repository is to encapsulate the database infrastructure so the client need not worry about the data source. However, if you return IQueryable you are at the mercy of the consumer as to what kind of query will get run against your db, and whether they will do something that the LINQ provider doesn't support.

Take paging for example. Lets say you have a Customer entity and your database could have hundreds of thousands of customers. Which code would you rather have your client write?

var customers = repos.GetCustomers().Skip(skipCount).Take(pageSize).ToList();

OR

var customers = repos.GetCustomers(pageIndex, pageSize);

In the first approach you make it impossible for the repository to restrict the number of records retrieved from the data source. Also, your consumer has to calculate the skipCount.

In the second approach you provide a more coarse grained interface to your client. Now your repository can enforce some constraints on the pageSize in order to optimize the query. You also encapsulate the calculation of the skipCount.

However, that being said, in your situation your client is your service. So I suppose the question really comes down to a separation of concerns. Where is it better to perform such validation logic? Well that answer may very well be "in the service". But what about the answer to "Where is it better to contain query logic?". To me the answer is clearly "The Repository". That is its intended area of expertise.