IEnumerable vs IQueryable in OData and Repository Pattern

Question

I watched this video and read this blog post. There is something in this post confused me; The last part of the post. In the last part Mosh emphasized, Repository should never return IQueryable, because it results in performance issue. But I read something that sounds contradictory.

This is the confusing part:

IEnumerable: While querying data from database, IEnumerable executes select query on server side, load data in-memory on client side and then filter data. Hence does more work and becomes slow.

IQueryable: While querying data from database, IQueryable executes select query on server side with all filters. Hence does less work and becomes fast.

this is another answer about IQueryable vs IEnumerable in Repository pattern.

These are opposite of Mosh's advice. If these are true, why we should not use IQueryable instead of IEnumerable.

And something else, What about situations that we want to use OData; As you know it’s better to use IQueryable instead of IEnumerable when querying by OData.

one more thing, is it good or bad to use OData for querying e-Commerce website APIs.

please let me know your opinion.

Thank you

I am encountering something very similar and I've settled on the fact that it's ok. If you have a repository that exposes IQuerable Find(Expression predicate) or something like that - you can pass in your initial set of requirements for the filter and then allow the application to do the rest. If the IQuerable is implemented correctly then the actual execution will be deferred until it's needed. — Travis Sharp, Feb 15 '19 at 15:52
The only real contention I can think of is if you need to select a subset of fields instead of the whole object. You can still do this but with a custom provider this becomes a bit more difficult. — Travis Sharp, Feb 15 '19 at 16:18

jgauffin · Answer 1 · 2021-02-25T13:00:58.083

A repository should never return a IQueryable. But not due to performance. It's due to complexity. A repository is about reducing the complexity in the business layer.

Buy exposing an IQueryable you increase the complexity in two ways:

You leak persistence knowledge to the business domain. There is things that you must know about the underlying Linq to Sql provider to write effective queries.
You must design the business entities so that querying them is possible (i.e. not pure business entities).

Examples:

var blockedUsers = _repository.GetBlockedUsers();

//vs

var blockUsers = _dbContext.Users.Where(x => x.State == 1);

var user = _repos.GetById(1);
//and an enum is used internally in the user class
user.Block(); 
_repos.Update(user);

// vs 
var user = _dbContext.Users.FirstOrDefault(x => x.Id == 1);
user.State = 1;
_dbContext.SaveChanges();

By wrapping everything behind your repository, you design your business entities in a way that make it easy to work with them (child entites, enums, date management etc). And you design the repository so that those entities can be stored in an efficient way. No compromises and code that is more easily maintained.

Regarding OData: Do not use the repository pattern. It doesn't add any value in that case.

If you insist on using IQueryable in your business domain, do not use the repository pattern. It would only complicate things without adding any value.

Finally:

Business logic that uses properly designed repositories is so much easier to test (unit tests). Code where LINQ and business logic is mixed must ALWAYS be integration tests (against a DB) since Linq to Sql differs from Linq to Objects.

IEnumerable vs IQueryable in OData and Repository Pattern

1 Answers1