Entity Framework Queryable async

Question

I m working on some some Web API stuff using Entity Framework 6 and one of my controller methods is a  Get All  that expects to receive the contents of a table from my database as IQueryable lt Entity gt   In my repository I m wondering if there is any advantageous reason to do this asynchronously as I m new to using EF with async   Basically it boils down to   public async Task lt IQueryable lt URL gt  gt  GetAllUrlsAsync          var urls   await context Urls ToListAsync        return urls AsQueryable         vs   public IQueryable lt URL gt  GetAllUrls          return context Urls AsQueryable         Will the async version actually yield performance benefits here or am I incurring unnecessary overhead by projecting to a List first  using async mind you  and THEN going to IQueryable

User · Answer

There is a massive difference in the example you have posted, the first version:

var urls = await context.Urls.ToListAsync();

This is bad, it basically does select * from table, returns all results into memory and then applies the where against that in memory collection rather than doing select * from table where... against the database.

The second method will not actually hit the database until a query is applied to the IQueryable (probably via a linq .Where().Select() style operation which will only return the db values which match the query.

If your examples were comparable, the async version will usually be slightly slower per request as there is more overhead in the state machine which the compiler generates to allow the async functionality.

However the major difference (and benefit) is that the async version allows more concurrent requests as it doesn't block the processing thread whilst it is waiting for IO to complete (db query, file access, web request etc).

User · Answer

Long story short      IQueryable is designed to postpone RUN process and firstly build the expression in conjunction with other IQueryable expressions  and then interprets and runs the expression as a whole      But ToList   method  or a few sort of methods like that   are ment to run the expression instantly  as is       Your first method  GetAllUrlsAsync   will run imediately  because it is IQueryable followed by ToListAsync   method  hence it runs instantly  asynchronous   and returns a bunch of IEnumerables      Meanwhile your second method  GetAllUrls   won t get run  Instead  it returns an expression and CALLER of this method is responsible to run the expression

User · Answer

The problem seems to be that you have misunderstood how async await work with Entity Framework   About Entity Framework  So  let s look at this code   public IQueryable lt URL gt  GetAllUrls         return context Urls AsQueryable        and example of it usage   repo GetAllUrls   Where u   gt   lt condition gt   Take 10  ToList     What happens there     We are getting IQueryable object  not accessing database yet  using repo GetAllUrls   We create a new IQueryable object with specified condition using  Where u   gt    lt condition gt  We create a new IQueryable object with specified paging limit using  Take 10  We retrieve results from database using  ToList    Our IQueryable object is compiled to sql  like select top 10   from Urls where  lt condition gt    And database can use indexes  sql server send you only 10 objects from your database  not all billion urls stored in database    Okay  let s look at first code   public async Task lt IQueryable lt URL gt  gt  GetAllUrlsAsync         var urls   await context Urls ToListAsync        return urls AsQueryable        With the same example of usage we got    We are loading in memory all billion urls stored in your database using await context Urls ToListAsync     We got memory overflow  Right way to kill your server   About async await  Why async await is preferred to use  Let s look at this code   var stuff1   repo GetStuff1ForUser userId   var stuff2   repo GetStuff2ForUser userId   return View new Model stuff1  stuff2      What happens here    Starting on line 1 var stuff1       We send request to sql server that we want to get some stuff1 for userId We wait  current thread is blocked  We wait  current thread is blocked        Sql server send to us response We move to line 2 var stuff2       We send request to sql server that we want to get some stuff2 for userId We wait  current thread is blocked  And again       Sql server send to us response We render view   So let s look to an async version of it   var stuff1Task   repo GetStuff1ForUserAsync userId   var stuff2Task   repo GetStuff2ForUserAsync userId   await Task WhenAll stuff1Task  stuff2Task   return View new Model stuff1Task Result  stuff2Task Result      What happens here    We send request to sql server to get stuff1  line 1  We send request to sql server to get stuff2  line 2  We wait for responses from sql server  but current thread isn t blocked  he can handle queries from another users We render view   Right way to do it  So good code here   using System Data Entity   public IQueryable lt URL gt  GetAllUrls        return context Urls AsQueryable       public async Task lt List lt URL gt  gt  GetAllUrlsByUser int userId       return await GetAllUrls   Where u   gt  u User Id    userId  ToListAsync        Note  than you must add using System Data Entity in order to use method ToListAsync   for IQueryable    Note  that if you don t need filtering and paging and stuff  you don t need to work with IQueryable  You can just use await context Urls ToListAsync   and work with materialized List lt Url gt

[c#] Entity Framework Queryable async

Examples related to c#

Examples related to entity-framework

Examples related to async-await