Yes.. this ist just another blog post about IEnumerable<> and IQueryable<>. But the topic is quite important and I think it worth discussing about both approaches.
Recently I had a discussion with a software architect about the implementation of the repository pattern. The question was: Should the repository return IEnumerable<> or IQueryable<>?
So let’s do a rough comparison between both interfaces.
| IEnumerable<> | IQueryable<> |
|---|---|
|
Used for in-memory data sources Filtering in-memory No lazy loading No custom queries |
Inherits from IEnumerable<> Used for out of memory data soures (DB etc) Filtering out of memory (SQL WHERE etc.) Lazy loading supported Custom query support |
Back to the repository pattern. We were mainly discussion about two approaches. Writing classes like:
public class PersonRepository
{
public IEnumerable<Person> GetByFirstName()
{ }
public IEnumerable<Person> GetByAge()
{ }
}
Or like:
public class PersonRepository
{
public IQueryable<Person> GetAll()
{ }
}
The first implementation uses IEnumerable<>. Each “Get” function is explicitly defined, which means the repository can be pretty big, but there will be less duplicate code.
And as a developer you get a good insight which functionality is provided by this repository.
The queries are executed either in the Repository class itself (by calling ToList() etc.) or by accessing the IEnumerable<> return object for the first time.
In the second repository has just one function which returns a IQueryable<> object. (There are no filter functions like “GetByAge” in the repository). The caller (business logic) is responsible for the filtering. Once the filter is defined, the business code can execute the filter by calling ToList(), First() etc. The whole IQueryable<> object including the filter criteria will be converted to SQL.
The advantages of the two approaches are:
| IEnumerable<> | IQueryable<> |
|---|---|
| It is easy to see which code is already implemented. This leads to less code duplication. | Your filtering is there where it should be: In your business code |
| The queries are executed in the repository class (by calling ToList()) which allows you to handle the EF/SQL specific exceptions in the data access layer. | Filter can be unit tested. |
| Query is executed when the data is needed. |
The disadvantages are:
| IEnumerable<> | IQueryable<> |
|---|---|
| The filtering logic is hard to unit test. | SQL/EF specific (technical) exceptions in you business code. |
| The repositories can get quite big. | It “could” lead to code duplication. (Same filter logic written multiple times in different business logic) |
| You almost “force” to load the data. | Changing the underlying LINQ provider could have impact on your business code. In worst case your new data source does not provide LINQ support. |
There is one more interesting advantage with IQueryable<> which I never tested: Let’s say you query a huge amount of objects from the DB. When you do just a simple SELECT (without ordering etc) then the DB starts returning the rows even before the SELECT statement has finished. This is interesting because when you send the data to the client through WCF the serializer starts serializing the incoming DB rows and sends them to the client while the DB is continuously delivering the data.
In the end, I think the IQueryable<> approach is better. Even though it is maybe a bit more complex solution. Because the developers must understand the concept of IQueryable<> (making sure that queries are fired only once etc.) and it requires some discipline to prevent code duplication.