Should Repositories return IQueryable?

Question

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.

Answer 1

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

Answer 2

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)

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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.