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<Entity>
. 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<IQueryable<URL>> GetAllUrlsAsync()
{
var urls = await context.Urls.ToListAsync();
return urls.AsQueryable();
}
vs
public IQueryable<URL> 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?
The problem seems to be that you have misunderstood how async/await work with Entity Framework.
So, let's look at this code:
public IQueryable<URL> GetAllUrls()
{
return context.Urls.AsQueryable();
}
and example of it usage:
repo.GetAllUrls().Where(u => <condition>).Take(10).ToList()
What happens there?
IQueryable
object (not accessing database yet) using repo.GetAllUrls()
IQueryable
object with specified condition using .Where(u => <condition>
IQueryable
object with specified paging limit using .Take(10)
.ToList()
. Our IQueryable
object is compiled to sql (like select top 10 * from Urls where <condition>
). 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<IQueryable<URL>> GetAllUrlsAsync()
{
var urls = await context.Urls.ToListAsync();
return urls.AsQueryable();
}
With the same example of usage we got:
await context.Urls.ToListAsync();
.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?
var stuff1 = ...
userId
var stuff2 = ...
userId
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?
So good code here:
using System.Data.Entity;
public IQueryable<URL> GetAllUrls()
{
return context.Urls.AsQueryable();
}
public async Task<List<URL>> GetAllUrlsByUser(int userId) {
return await GetAllUrls().Where(u => 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<Url>
.