Using OData syntax with WebAPI and DAL

Question

I currently have a Data Layer, housing EF, and a repository pattern on a separate project from my main project. I'm at a point where I want to implement paging and filtering on my queries, and discovered the WebAPI has OData query support.

This question may just be because I am new to this, but if I use OData queries from my project's controllers, doesn't it make my DAL project almost unnecessary? The reason I think so is because the repository's interfaces would have to expose IQueryable, instead of List of Collections.

The reason I have a separate data layer is that I'm trying to create a intranet system for my company, and would have many users accessing the database at once. The layer (as I come to believe) would help with expanding the system in the future as well as database requests, as well as eliminate duplicate requests in my controllers by creating a library of methods.

Is it not ideal to expose IQueryables in the repository? Or am I worrying over nothing?

Answer 1

You should totally go with exposing your entity sets as IQueryable from your DAL.

From what I understood your DAL wraps EF context and using repository pattern you expose your Entities as IEnumerable/ICollection ? If this is the case then performance of your queries will increase significally when you expose your Entities as IQueryable.

if I use OData queries from my project's controllers, doesn't it make my DAL project almost unnecessary?

You should not keep the logic responsible for receiving/sending data from/to the database in your controller, so your repository from your DAL project is still useful.

Explaination: In terms of performance, you should keep in mind that as long as you're operating on IQueryable you are in fact operating on an SQL query, once you materialize it using ToList() you perform a request to the database and start operating on data from in the system memory.

Regarding exposing IQueryable from repositories: Not only you gain improved performance compared to ICollection, you also get rid of many specific data fetching methods (like GetAllActiveUsers, GetAllInactiveUsers etc) giving the availability to query for specific data to the consumer of repository, and this in some cases might be a problem, because it might get abused by them. However, I do not belive it is a problem in your case, because I assume you're not making a big application in a big team of developers.

Example: So, let's say you have an Entity "User" and UserRepository class. You want to receive a collection of users with not null value in Email property, so you come up with following code:

var users = _userRepository.Users.Where(x => x.Email != null).ToList();

1. In the case of exposing Users as IEnumerable/ICollection you return all users from your database, and then having the collection in the system memory you search it for users with not null value of Email property. The query generated by EF and sent to database in this case looks like SELECT * FROM [schema].[User]

2. In the case of exposing Users as IQueryable you return all users with not null value in Email property already from the database. In this case the query generated BY EF and sent to database looks like SELECT * FROM [schema].[User] WHERE [Email] IS NOT NULL