IEnumerable vs IQueryable for Business Logic or DAL return Types

I know these questions have been asked before, I'll start by listing a few of them (the ones I've read so far):

• IEnumerable vs IQueryable
• List, IList, IEnumerable, IQueryable, ICollection, which is most flexible return type?
• Returning IEnumerable<T> vs. IQueryable<T>
• IEnumerable<T> as return type
• https://stackoverflow.com/questions/2712253/ienumerable-and-iqueryable
• Views with business logic vs code
• WPF IEnumerable<T> vs IQueryable<T> as DataSource
• IEnumerable<T> VS IList<T> VS IQueryable<T>
• What interface should my service return? IQueryable, IList, IEnumerable?
• Should I return IEnumerable<T> or IQueryable<T> from my DAL?

As 开发者_开发百科you can see, there's some great resources on SO alone on the subject, but there is one question/section of the question I'm still not sure about having read these all through.

I'm primarily concerned with the IEnumerable vs IQueryable question, and more specifically the coupling between the DAL and it's consumers.

I've found varying opinions suggested regarding the two interfaces, which have been great. However, I'm concerned with the implications of a DAL returning IQueryable. As I understand it IQueryable suggest/implies that there is a Linq Provider under the hood. That's concern number one - what if the DAL suddenly requires data from a non-Linq provided source? The following would work, but is it more of a hack?

public static IQueryable<Product> GetAll()
{
    // this function used to use a L2S context or similar to return data 
    // from a database, however, now it uses a non linq provider

    // simulate the non linq provider...
    List<Product> results = new List<Product> { new Product() };
    return results.AsQueryable();
}

So I can use the AsQueryable() extension though I don't admit to knowing exactly what this does? I always imagine IQueryables as being the underlying expression trees which we can append as necessary until we're ready to perform our query and fetch the results.

I could rectify this by changing the return type of the function to IEnumerable. I can then return IQueryable from the function because it inherits IEnumerable, and I get to keep the deferred loading. What I lose is the ability to append to the query expression:

var results = SomeClass.GetAll().Where(x => x.ProductTypeId == 5);

When returning IQueryable, as I understand it, this would simply append the expression. When returning IEnumerable, despite maintaining the deferred loading, the expression has to be evaluated so the results will be brought to memory and enumerated through to filter out incorrect ProductTypeIds.

How do other people get round this?

• Provide more functions in the DAL - GetAllByProductType, GetAllByStartDate,... etc

• Provide an overload that accepts predicates? i.e.

  public static IEnumerable<Product> GetAll(Predicate<Product> predicate)
  {
  
      List<Product> results = new List<Product> { new Product() };
      return results.Where(x => predicate(x));
  }
  

One last part (Sorry, I know, really long question!).

I found IEnumerable to be the most recommended across all the questions I checked, but what about the deferred loadings' requirement for a datacontext to be available? As I understand it, if your function returns IEnumerable, but you return IQueryable, the IQueryable is reliant on an underlying datacontext. Because the result at this stage is actually an expression and nothing has been brought to memory, you cannot guarantee that the DAL's/function's consumer is going to perform the query, nor when. So do I have to keep the instance of the context that the results were derived from available somehow? Is this how/why the Unit of Work pattern comes into play?

Summary of the questions for clarity ( did a search for "?"...):

1. If using IQueryable as a return type, are you too tightly coupling your UI/Business Logic to Linq Providers?
2. Is using the AsQueryable() extension a good idea if you suddenly need to return data from a non-Linq Provided source?
3. Anyone have a good link describing how for example converting a standard list to AsQueryable works, what it actually does?
4. How do you handle additional filtering requirements supplied by business logic to your DAL?
5. It seems the deferred loading of both IEnumerable and IQueryable are subject to maintaining the underlying provider, should I be using a Unit of Work pattern or something else to handle this?

Thanks a lot in advance!

1. well, you aren't strictly coupled to any specific provider, but as a re-phrasing of that: you can't easily test the code, since each provider has different supported features (meaning: what works for one might not work for another - even something like .Single())
2. I don't think so, if there is any question in your mind about ever changing provider - see above
3. it just provides a decorated wrapper that uses .Compile() on any lambdas, and uses LINQ-to-Objects instead. Note LINQ-to-Objects has more support than any other provider, so this won't be an issue - except that it means that any "mocks" using this approach don't really test your actual code at all and are largely pointless (IMO)
4. yeah, tricky - see below
5. yeah, tricky - see below

Personally, I'd prefer well defined APIs here that take known parameters and return a loaded List<T> or IList<T> (or similar) of results; this gives you a testable/mockable API, and doesn't leave you at the mercy of deferred execution (closed connection hell, etc). It also means that any differences between providers is handled internally to the implementation of your data layer. It also makes a much closer fit for calling scenarios such as web-services, etc.

In short; given a choice between IEnumerable<T> and IQueryable<T>, I choose neither - opting instead to use IList<T> or List<T>. If I need additional filtering, then either:

1. I'll add that to the existing API via parameters, and do the filtering inside my data layer
2. I'll accept that oversized data is coming back, which I then need to filter out at the caller