Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have been seeing a lot of projects that have repositories that return instances of IQueryable. This allows additional filters and sorting can be performed on the IQueryable by other code, which translates to different SQL being generated. I am curious where this pattern came from and whether it is a good idea.

My biggest concern is that an IQueryable is a promise to hit the database some time later, when it is enumerated. This means that an error would be thrown outside of the repository. This could mean an Entity Framework exception is thrown in a different layer of the application.

I have also run into issues with Multiple Active Result Sets (MARS) in the past (especially when using transactions) and this approach sounds like it would lead to this happening more often.

I have always called AsEnumerable or ToArray at the end of each of my LINQ expressions to make sure the database is hit before leaving the repository code.

I am wondering if returning IQueryable could be useful as a building block for a data layer. I have seen some pretty extravagant code with one repository calling another repository to build an even bigger IQueryable.

share|improve this question
What would be the difference if the db was not available at the time of the deferred query execution vs the time of query construction? Consuming code would have to deal with that situation regardless. –  Steve Evers Mar 26 '13 at 21:05
Interesting question, but it will probably be hard to answer. A simple search shows a fairly heated debate about your question: duckduckgo.com/?q=repository+return+iqueryable –  Corbin March Mar 26 '13 at 21:07
It all comes down to whether you want to allow your clients to add Linq clauses that could benefit from deferred execution. If they add a where clause to a deferred IQueryable, you only have to send that data over the wire, not the entire result set. –  Robert Harvey Mar 26 '13 at 21:18
If you are using repository pattern - no, you should not return IQueryable. Here's a nice why. –  Arnis L. Mar 26 '13 at 23:07
@SteveEvers There is a huge difference. If an exception is thrown in my repository, I can wrap it with a data-layer-specific exception type. If it happens later on, I have to capture the original exception type there. The closer I am to the source of the exception, the more likely I'll know what caused it. This is important for creating meaningful error messages. IMHO, allowing an EF-specific exception to leave my repository is a violation of encapsulation. –  Travis Parks Mar 27 '13 at 12:55

8 Answers 8

Returning IQueryable will definitely afford more flexibility to the consumers of the repository. It puts the responsibility of narrowing results off to the client, which naturally can both be a benefit and a crutch.

On the good side, you won't need to be creating tons of repository methods (at least on this layer) — GetAllActiveItems, GetAllNonActiveItems, etc — to get the data you want. Depending on your preference, again, this could be good or bad. You will (/should) need to define behavioral contracts which your implementations adhere to, but where that goes is up to you.

So you could put the gritty retrieval logic outside the repository and let it be used however the user wants. So exposing IQueryable gives the most flexibility and allows for efficient querying as opposed to in-memory filtering, etc, and could reduce the need for making a ton of specific data fetching methods.

On the other hand, now you have given your users a shotgun. They can do things which you may not have intended (overusing .include(), doing heavy heavy queries and doing in-memory filtering in their respective implementations, etc), which would basically side-step the layering and behavioral controls because you have given full access.

So depending on the team, their experience, the size of the app, the overall layering and architecture … it depends :-\

share|improve this answer
Note that you could, if you wanted to work for it, return your IQueryable, which in turn calls the DB, but adds layering and behavioral controls. –  psr Mar 26 '13 at 22:11
@psr Could you explain what you mean by this? –  Travis Parks Mar 27 '13 at 13:03
@TravisParks - IQueryable doesn't have to be implemented by the DB, you can write IQueryable classes yourself - it's just pretty hard. So you could write an IQueryable wrapper around a database IQueryable, and have your IQueryable limit full access to the underlying DB. But this is hard enough that I wouldn't expect it to be widely done. –  psr Mar 27 '13 at 16:31

Realistically, you've got three alternatives if you want deferred execution:

  • Do it this way - expose an IQueryable.
  • Implement a structure that exposes specific methods for specific filters or "questions". (GetCustomersInAlaskaWithChildren, below)
  • Implement a structure that exposes a strongly-typed filter/sort/page API and builds the IQueryable internally.

I prefer the third (though I've helped colleagues implement the second as well), but there's obviously some setup and maintenance involved. (T4 to the rescue!)

Edit: To clear up the confusion surrounding what I'm talking about, consider the following example stolen from IQueryable Can Kill Your Dog, Steal Your Wife, Kill Your Will to Live, etc.

In the scenario where you would expose something like this:

public class CustomerRepo : IRepo 
     private DataContext ct; 
     public Customer GetCustomerById(int id) { ... } 
     public Customer[] GetCustomersInAlaskaWithChildren() { ... } 

the API I'm talking about would let you expose a method that would let you express GetCustomersInAlaskaWithChildren (or any other combination of criteria) in a flexible manner, and the repo would execute it as an IQueryable and return the results to you. But the important thing is that it runs inside the repository layer, so it still takes advantage of deferred execution. Once you get the results back, you can still LINQ-to-objects on it to your heart's content.

Another advantage of an approach like this is that, because they're POCO classes, this repository can live behind a web or WCF service; it can be exposed to AJAX or other callers that don't know the first thing about LINQ.

share|improve this answer
So you'd build a query API to substitute for .NET's built in query API? –  Mike Brown Mar 26 '13 at 21:34
If I didn't want to expose the built-in query API, yes. –  GalacticCowboy Mar 26 '13 at 21:35
Sounds pretty pointless to me –  jmo21 Mar 26 '13 at 21:45
I wouldn't go so far as what Ozz said...that was pretty harsh. But to me IQueryable is part of the framework. Implementing an alternative to avoid using what the framework provides seems excessive to me. –  Mike Brown Mar 27 '13 at 1:43
@MikeBrown The point of this question - and my answer - is that you want to expose the behavior or the advantages of IQueryable without exposing the IQueryable itself. How are you going to do that, with the built-in API? –  GalacticCowboy Mar 27 '13 at 11:17

There really is only one legitimate answer: it depends on how the repository is to be used.

At one extreme your repository is a very thin wrapper around a DBContext so you can inject a veneer of testability around a database-driven app. There really is no real world expectation that this might be used in a disconnected manner without a LINQ friendly DB behind it because your CRUD app ain't ever gonna need that. Then sure, why not use IQueryable? I would probably prefer IEnumarable as you get most of the benefits [read: delayed execution] and it don't feel as dirty.

Unless you are sitting at that extreme I would try hard to take advantage of the spirit of the repository pattern and return appropriate materialized collections that don't have an implication of an underlying database connection.

share|improve this answer
Regarding returning IEnumerable, it's important to note that ((IEnumerable<T>)query).LastOrDefault() is very different from query.LastOrDefault() (presuming query is an IQueryable). So, it only makes sense to return IEnumerable if you're going to iterate through all elements anyway. –  Dilbert Nov 13 '14 at 13:00

I think that exposing the IQueryable on your repository is perfectly acceptable during the initial phases of development. There are UI tools that can work with IQueryable directly and handle sorting, filtering, grouping, etc.

That being said, I think that just exposing crud on the repository and calling it a day is irresponsible. Having useful operations and query helpers for common queries allows you to centralize the logic for a query while also exposing a smaller interface surface to consumers of the repository.

For the first few iterations of a project, exposing IQueryable publicly on the repository allows for quicker growth. Before the first full release, I'd recommend making the query operation private or protected and bringing the wild queries under one roof.

share|improve this answer

Exposing IQueryable to public interfaces is not a good practice. The reason is fundamental: you cannot provide IQueryable realisation as it is said to be.

Public interface is a contract between provider and clients. And in most cases there is an expectation of full implementation. IQueryable is a cheat:

  1. IQueryable is nearly impossible to implement. And currently there is no proper solution. Even Entity Framework has lots of UnsupportedExceptions.
  2. Today Queryable providers have large dependance on infrastructure. Sharepoint has its own partial implementation and My SQL provider has distinct partial implementation.

Repository pattern gives us clear representation by layering, and, most important, independence of realisation. So we can change in future data source.

The question is "Should there be possibility of data source substitution?".

If tomorrow part of the data can be moved to SAP services, NoSQL data base, or simply to text files, can we guaranty proper implementation of IQueryable interface?

(more good points about why this is bad practice)

share|improve this answer
This answer does not stand on its own without consulting the volatile external link. Consider summarizing at least the key points made by the external resource, and try to keep personal opinion out of your answer ("worst idea I've heard"). Also consider removing the code snippet that seems unrelated to anything IQueryable. –  Lars Viklund Dec 17 '13 at 13:08
Thank you @LarsViklund, the answer is updated with examples and problem description –  Artru Dec 17 '13 at 22:21

What I've seen done (and what I'm implementing myself) is the following:

public interface IEntity<TKey>
    TKey Id { get; set; }

public interface IRepository<TEntity, in TKey> where TEntity : IEntity<TKey>
    void Create(TEntity entity);
    TEntity Get(TKey key);
    IEnumerable<TEntity> GetAll();
    IEnumerable<TEntity> GetAll(Expression<Func<TEntity, bool>> expression);
    void Update(TEntity entity);
    void Delete(TKey id);

What this allows you to do is to have the flexibility of doing an IQueryable.Where(a => a.SomeFilter) query on your repo by doing concreteRepo.GetAll(a => a.SomeFilter) without exposing any LINQy business.

share|improve this answer

From a pure architecture standpoint, IQueryable is a leaky abstraction. As a generality, it provides the user with too much power. That being said, there are places where it makes sense. If you are using OData, IQueryable makes it extremely easy to provide an endpoint that is easily filterable, sortable, groupable... etc. I actually prefer to create DTO's that my entities map onto and the IQueryable returns the DTO instead. That way I pre-filter my data and really only use the IQueryable method to allow client customization of the results.

share|improve this answer
 > Should Repositories return IQueryable?

NO if you want to do testdriven development/unittesting because there is no easy way to create a mock repository to test your businesslogic (= repository-consumer) in isolation from database or other IQueryable provider.

share|improve this answer

protected by gnat Jun 17 at 20:40

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.