Entity Framework Queryable async

Question

I am working on some web API materials using Entity Framework 6, and one of my controller methods is Get All, which expects to get the contents of a table from my database as IQueryable<Entity> . In my repository, I wonder if there is any good reason to do this asynchronously, since I'm new to using EF with asynchronous access.

It basically boils down to

  public async Task<IQueryable<URL>> GetAllUrlsAsync() { var urls = await context.Urls.ToListAsync(); return urls.AsQueryable(); } 

against

  public IQueryable<URL> GetAllUrls() { return context.Urls.AsQueryable(); } 

Will the version of the asynchronous version actually provide performance benefits here, or am I finding unnecessary overhead by projecting the list first (using async mind you) and THEN go into IQueryable?

Answer 1

The problem seems to be that you misunderstood how async / await works with the Entity Framework.

About Entity Framework

So let's take a look at this code:

 public IQueryable<URL> GetAllUrls() { return context.Urls.AsQueryable(); } 

and an example of its use:

 repo.GetAllUrls().Where(u => <condition>).Take(10).ToList() 

What is happening there?

1. We get an IQueryable object (not yet accessing the database) using repo.GetAllUrls()
2. We create a new IQueryable with the specified condition using .Where(u => <condition>
3. We create a new IQueryable with the specified swap limit using .Take(10)
4. We get the results from the database using .ToList() . Our IQueryable compiled in sql (e.g. select top 10 * from Urls where <condition> ). And the database can use indexes, SQL Server sends you only 10 objects from your database (not all billions of URLs stored in the database)

Ok, let's look at the first code:

 public async Task<IQueryable<URL>> GetAllUrlsAsync() { var urls = await context.Urls.ToListAsync(); return urls.AsQueryable(); } 

With the same usage example, we got:

1. We load into the memory all the billions of URLs stored in your database using await context.Urls.ToListAsync(); ,
2. We have a memory overflow. The right way to kill your server

About Async / Pending

Why is async / await preferable 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's going on here?

1. Starting at line 1 var stuff1 =...
2. We send a request to sql server that we want to get some things1 for userId
3. Waiting (current thread is blocked)
4. Waiting (current thread is blocked)
5. ...
6. Sql server send us an answer
7. var stuff2 =... to line 2 var stuff2 =...
8. We send a request to the sql server that we want to get some things2 for userId
9. Waiting (current thread is blocked)
10. And again
11. ...
12. Sql server send us an answer
13. We pretend

So let's look at the asynchronous version:

 var stuff1Task = repo.GetStuff1ForUserAsync(userId); var stuff2Task = repo.GetStuff2ForUserAsync(userId); await Task.WhenAll(stuff1Task, stuff2Task); return View(new Model(stuff1Task.Result, stuff2Task.Result)); 

What's going on here?

1. We send a request to the sql server to get things1 (line 1)
2. We send a request to the sql server to get things2 (line 2)
3. We are waiting for responses from the SQL server, but the current thread is not blocked, it can handle requests from other users
4. We pretend

The right way to do it

So the good code is 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 that you must add using System.Data.Entity in order to use the ToListAsync() method for IQueryable.

Please note that if you do not need filtering, paging, etc., you do not need to work with IQueryable . You can simply use await context.Urls.ToListAsync() and work with the materialized List<Url> .

Answer 2

In the example you posted, there is a big difference, the first version:

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

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

The second method will not actually get into the database until the IQueryable query is IQueryable (possibly using the linq .Where().Select() style operation), which will only return db values ​​matching the query.

If your examples were comparable, the async version will usually be slightly slower for each request, since there are more overheads that the compiler generates to provide async in the destination machine.

However, the main difference (and benefit) is that the async version allows more parallel requests, since it does not block the processing flow while it waits for I / O to complete (db request, file access, web request, etc.), .