86

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 ToList 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.

13
  • 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. Mar 26, 2013 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 Mar 26, 2013 at 21:07
  • 8
    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. Mar 26, 2013 at 21:18
  • 3
    If you are using repository pattern - no, you should not return IQueryable. Here's a nice why. Mar 26, 2013 at 23:07
  • 2
    @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. Mar 27, 2013 at 12:55

9 Answers 9

59

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 :-\

8
  • 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, 2013 at 22:11
  • 1
    @psr Could you explain what you mean by this? Mar 27, 2013 at 13:03
  • 2
    @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, 2013 at 16:31
  • 4
    Note that even when you don't return IQueryables, you can still prevent having to make many methods (GetAllActiveItems, GetAllNonActiveItems, etc) by simply passing an Expression<Func<Foo,bool>> to your method. This parameters can be passed to the Where method directly, thus enabling the consumer to choose their filter without needing direct access to the IQueryable<Foo>.
    – Flater
    Dec 12, 2018 at 7:14
  • 2
    @hanzolo: Depends on what you mean with refactoring. It's true that changing the design (e.g. adding new fields or changing the relations) is more of a pain when you have a layered and loosely coupled codebase; but refactoring is less of a pain when you only need to change one layer. It all depends on whether you expect to redesign the application, or whether you're simply refactoring your implementation of the same basic architecture. I would still advocate loose coupling in either case but you're not wrong that it adds to the refactoring when changing core domain concepts.
    – Flater
    Dec 20, 2018 at 14:02
36

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)

2
  • 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. Dec 17, 2013 at 13:08
  • 1
    Thank you @LarsViklund, the answer is updated with examples and problem description
    – Artur A
    Dec 17, 2013 at 22:21
21

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.

12
  • 4
    So you'd build a query API to substitute for .NET's built in query API? Mar 26, 2013 at 21:34
  • 7
    @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? Mar 27, 2013 at 11:17
  • 2
    That's a very good tautology. You haven't explained why you want to avoid that. Heck WCF Data Services exposes IQueryable across the wire. I'm not trying to pick on you, I'm just not understanding this aversion to IQueryable people seem to have. Mar 27, 2013 at 12:28
  • 2
    If you're just going to use an IQueryable, then why would you even use a repository? Repositories are intended to hide implementation details. (Also, WCF Data Services is quite a bit newer than most of the solutions I've worked on in the past 4 years that used a repository like this... So it's not likely that they'll be updated anytime soon just to make use of a shiny new toy.) Mar 27, 2013 at 20:32
  • 2
    My point being, not all design patterns and lengths to go to are appropriate for all scenarios, project scales or team-sizes. GetCustomersInAlaskaWithChildren while leaving the connection open to lazy-load on the consumer end IS very cool - but you still run into the possibility of the above scenario. Someone can potentially pull the whole database down ala joins for a what should have been quick lookup... Dec 23, 2015 at 1:43
9

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.

1
  • 7
    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.
    – Lou
    Nov 13, 2014 at 13:00
8
 > 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.

1
  • But what should they then return?
    – JHBonarius
    Jan 23, 2021 at 9:46
7

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.

1
  • I've seen this elsewhere. Does this help?
    – Chalky
    Jun 17, 2023 at 8:53
6

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.

6

I've faced such choice as well.

So, let's summarize positive and negative sides:

Positives:

  • Flexibility. It's definitely flexible and convenient to allow client side to build custom queries with just one repository method

Negatives:

  • Untestable. (you will be actually testing the underlying IQueryable implementation, which usually your ORM. But you should test your logic instead)
  • Blurs responsibility. It's impossible to say, what that particular method of your repository does. Actually, it is a god-method, that can return anything you like in your entire database
  • Unsafe. With no restrictions on what can be queried by this repository method, in some cases such implementations can be unsafe from security point.
  • Caching problems (or, to be generic, any pre- or post-processing problems). It is generally unwise to, for example, cache everything in your application. You will try to cache something, that cause your database significant load. But how to do that, if you have only one repository method, that clients use to issue virtually any query against the database?
  • Logging problems. Logging something at the repository layer will make you inspect IQueryable first to check should this particular call be logged or not.

I would recommend against using this approach. Consider instead creating several Specifications and implement repository methods, that can query database using those. Specification pattern is a great way to encapsulate a set of conditions.

5

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.

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