Clever Geek Handbook

Using OData syntax with WebAPI and DAL

I currently have a data layer, an EF corpus and a storage template in a separate project from my main project. I'm at the point where I want to implement paging and filtering in my requests, and found that WebAPI has support for OData requests.

This question might just be because I'm new to this, but if I use OData requests from my project controllers, doesn't that make my DAL project nearly unnecessary? The reason I think so is because the repository interfaces would have to expose IQueryable instead of a collection list.

The reason I have a separate data tier is because I am trying to create an intranet system for my company and many users will have access to the database right away. A layer (as I believe) would help expand the system in the future, as well as database queries, and also eliminate duplicate queries in my controllers by creating a method library.

Isn't it ideal to expose IQueryables in a repository? Or am I not worried about anything?

+3


source to share


1 answer


You should fully expose your entities as IQueryable from your DAL.

From what I understood, your DAL wraps the EF context and uses the repository pattern, are you opening your objects as IEnumerable / ICollection? If so, then the performance of your queries will improve significantly when you expose your objects as IQueryable.

if I use OData requests from my project controllers, doesn't that make my DAL project almost unnecessary?

You don't have to keep the logic in charge of receiving / sending data from / to your controller's database, so your repository from your DAL project is still useful.

Explaination: As far as performance goes, you should keep in mind that as long as you are working with an IQueryable, you are actually working with a SQL query, once you materialize it with ToList (), you execute a database query and start work with data from system memory.



Regarding expanding IQueryable from repositories: You not only get better performance over ICollection, but you also get rid of many data collection methods (like GetAllActiveUsers, GetAllInactiveUsers, etc.) by providing the ability to query specific data for the consumer of the repository , and that in some cases it can be a problem because they might be offended by them. However, I don't believe this is a problem in your case, because I am assuming that you are not making a large application with a large development team.

Example : So let's say you have a User object and a Custom Class. You want to get a collection of invalid users in the email property, so you can find the following code:

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

  • In the case of giving Users an IEnumerable / ICollection, you return all users from your database, and then, having the collection in system memory, you look for it for non-nullable email users. The query generated by EF and sent to the database in this case looks likeSELECT * FROM [schema].[User]

  • In the case of publishing users as IQueryable, you are returning all users with a null value in the email property already from the database . In this case, the query generated by BY EF and sent to the database looks likeSELECT * FROM [schema].[User] WHERE [Email] IS NOT NULL

+5


source






More articles:


All Articles