web api and odata performance : perform query at database level

Question

I am using oData for a web api project. This is my code :

[HttpGet]        
[EnableQuery]
[Route("sessions")]
public IQueryable<Session> Get()
{
    var sessions = this.sessionRepository.GetAllSessions();
    return sessions.AsQueryable();        
}

public class Session
{
    public long Id {get;set;}

    public string Name {get;set;}
}

This is working well :

When I call the api like this mydomain/api/sessions, I get all the sessions When I call the api like this mydomain/api/sessions?$filter=Name eq 'session test', I get only the sessions which has the name equals to 'session test'.

The problem with this code, is that whatever the request performed by the user (no filter, filter, top, or anything else), a first "select *" is performed in db and then with the QueryableAttribute from oData, a filter is performed on the results got from the first big request "select *". My question is the following : is it possible to perform the query given by the user directly in the db and not doing first the select * and then performs the filter on this result.

Thanks in advance for your help.

How do you know it filters client side? What is your backing storage / database? Do you use Entity Framework or another ORM? — Patrick Hofman
I am using entity framework and sql server, And when I debug my code, the only request that goes to my db is a select * into my sessions table — user2443476
it's basic ef, its "return this._commonDataContext.Sessions" — user2443476
Can you use SQL Server Profiler (I presume you are using SQL Server) to see which queries are hitting the database? I suspected some of my code was doing client side filtering but on using the Profiler I could see that it was working as expected. — Andy Sinclair

Chiricescu Mihai Chiricescu Mihai · Accepted Answer · 2017-05-12T13:20:42

It Really depends on the return type of your method. In your case the query is getting serialized before applying the filters from Odata. This means that the return type for method GetAllSessions should be IQueryable. In this case the OData framework will make the call to db via Entity Framework.

Repository code should look like:

public IQueryable<Session> GetAllSessions()
{
   return  this._commonDataContext.Sessions.AsQueryable();
}

web api and odata performance : perform query at database level

1 Answers