Entity Framework Queryable Async

Entity Framework Queryable async

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<URL> GetAllUrls()
{
    return context.Urls.AsQueryable();
}

and example of it usage:

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

What happens there?

1. We are getting IQueryable object (not accessing database yet) using repo.GetAllUrls()
2. We create a new IQueryable object with specified condition using .Where(u => <condition>
3. We create a new IQueryable object with specified paging limit using .Take(10)
4. We retrieve results from database using .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:

1. We are loading in memory all billion urls stored in your database using await context.Urls.ToListAsync();.
2. 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?

1. Starting on line 1 var stuff1 = ...
2. We send request to sql server that we want to get some stuff1 for userId
3. We wait (current thread is blocked)
4. We wait (current thread is blocked)
5. .....
6. Sql server send to us response
7. We move to line 2 var stuff2 = ...
8. We send request to sql server that we want to get some stuff2 for userId
9. We wait (current thread is blocked)
10. And again
11. .....
12. Sql server send to us response
13. 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?

1. We send request to sql server to get stuff1 (line 1)
2. We send request to sql server to get stuff2 (line 2)
3. We wait for responses from sql server, but current thread isn't blocked, he can handle queries from another users
4. We render view

Right way to do it

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>.

Using async with Entity Framework select list of type IQueryableT

You have to be aware between the difference of a query, and the result of the query. An IQueryable holds everything to perform the query. It isn't the query itself, and creating an IQueryable doesn't perform the query.

If you look more closely to LINQ statements, you'll see that there are two types: the ones that return IQueryable (and IEnumerable), and the ones that return List<TResult>, TResults, TKey, etc, anything that are not IQueryable/IEnumerable. If the return value is an IQueryable, then we say that the function uses delayed execution (or lazy execution): the Expression to perform the query is created, but the query is not executed yet.

This has the advantage that you can concatenate LINQ statements, without executing a query per statement.

The query is executed when you ask the IQueryable to get an enumerator and if you start enumerating, either implicitly by using foreach, or explicitly by using IQueryable.GetEnumerator() and IEnumerator.MoveNext() (which are also called by foreach).

So as long as you are creating a query and returning an IQueryable, it is useless to create a Task. Concatenating LINQ statement will only change the Expression of the IQueryable, which is not something that you have to wait for.

Only if you create a function that will actually execute the query you'll need an async version: ToListAsync, FirstOrDefaultAsync, MaxAsync, etc. Internally these functions will GetEnumerator and MoveNextAsync <-- that is the actual async function

Conclusion: all your functions that would normally return
IQueryable<...> don't need an Async version , all functions that
return actual fetched data need an Async version

Examples. No async needed: no query executed:

// Query customer addresses:
static IQueryable<Address> QueryAddresses(this IQueryable<Customer> customers)
{
     return customers.Select(customer => customer.Address);
}

async needed:

static async Task<List<Address>> FetchAddressesAsync (this IQueryable<Customer> customers)
{
     var query = customers.QueryAddresses;   // no query executed yet
     return await query.ToListAsync();       // execute the query
     // could of course be done in one statement
}

static async Task<Address> FetchAddressAsync(this.IQueryable<Customer> customers, int customerId)
{
    var query = customers.Where(customer => customer.Id == customerId)
                         .QueryAddresses();
    // no query executed yet!
    // execute:
    return await query.FirstOrDefaultAsync();
}

Usage:

int customerId = ...
using (var dbContext = new InvoiceContext())
{
     Address fetchedCustomerAddress = await dbContext.Customers
         .FetchAddressAsync(customerId);
}

In the rare case that you'll have to enumerate yourself, you'll await in MoveNextAsync:

IQueryable<Customer> myCustomers = ...
IEnumerator<Customer> customerEnumerator = myCustomers.GetEnumerator();

while (await customerEnumerator.MoveNextAsync())
{
     Customer customer = customerEnumerator.Current;
     Process(customer);
}

Are IQueryable and IQueryableT async?

No, IQueryable and IQueryable<T> are not async, and they don't need to be. These two interfaces provide means to extract some data from a source, but it is up to the source to provide async support. If the source will return all data in memory, using it asynchronously is pointless. If, however, the source is "streamed" from network, then it might make sense to enumerate it asynchronously.

Resolve IQueryable and Async in Method

Although I srsly think that exposing IQueryable<T> in a generic repository interface is a total failure, I see that you cant go against this.

Seems like you could rewrite that into:

public IEnumerable<DepartmentDto> GetAllDepartments()
{
    var departmentPaged = new Paged<Department>(departmentRepository.GetAll());
    return mapper.Map<IEnumerable<Department>, IEnumerable<DepartmentDto>>(departmentPaged.GetPage(1, 3));
}

There is no async code involved in this.

If you still want to force the return type to be wrapped in a task, you can do:

public Task<IEnumerable<DepartmentDto>> GetAllDepartments()
{
    var departmentPaged = new Paged<Department>(departmentRepository.GetAll());
    return Task.FromResult(mapper.Map<IEnumerable<Department>, IEnumerable<DepartmentDto>>(departmentPaged.GetPage(1, 3)));
}

Why Entity Framework Core is showing async methods?

FirstOrDefault is part of a different namespace than FirstOrDefaultAsync.

FirstOrDefaultAsync belongs to the Entity Framework Core package. FirstOrDefault belongs to Linq.

to get the FirstOrDefault method add this using statement:

using System.Linq;

read these for more:

https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.queryableextensions.firstordefaultasync?view=entity-framework-6.2.0

https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.firstordefault?view=netcore-3.1

Is it possible to write Async IQueryableTEntity query?

You should be passing around an Expression rather than a Func, otherwise Entity Framework will execute the query immediately, bringing the entire table into memory and filtering locally. For example:

public virtual IQueryable<TEntity> GetManyQueryable(Expression<Func<TEntity, bool>> where)
{
    return dbSet.Where(where);
}

See here for a good description of the difference.

---

Related Topics