Architecture – Should Repositories return IQueryable

Architectureentity-frameworkrepository-pattern

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.

Best Answer

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