Using async with Entity Framework select list of type IQueryable<T>

Question

I'm trying to convert data access synchronous query to asynchronous, so far I converted everything except selecting list which returns IQueryable<T>.

Here's what I have done so far:

    [Dependency]
    public SampleContext db { get; set; }

    public async System.Threading.Tasks.Task<Profile> Add(Profile item)
    {
        db.Profiles.Add(item);
        await db.SaveChangesAsync();
        return item;
    }

    public async System.Threading.Tasks.Task<Profile> Get(string id)
    {
        return await db.Profiles.AsNoTracking().Where(i => i.Id == id).FirstOrDefaultAsync();
    }

    public async System.Threading.Tasks.Task Remove(string id)
    {
        Profile item = db.Profiles.Find(id);
        item.IsDeleted = 1;
        db.Entry(item).State = EntityState.Modified;
        await db.SaveChangesAsync();
    }

    public async System.Threading.Tasks.Task<bool> Update(Profile item)
    {
        db.Set<Profile>().AddOrUpdate(item);
        await db.SaveChangesAsync();
        return true;
    }

Above code works well, I'm stuck at converting this piece of code:

    public IQueryable<Profile> GetAll()
    {
        return db.Profiles.AsNoTracking().Where(i => i.IsDeleted == 0);
    }

How do I convert above code to asynchronous? I tried this sample code by Stephen Cleary but can't figure out what is ProcessEventAsync and how do I apply this to my code. Also, I can't use .ToList(), this will be too expensive to load all the data in memory.

Solution 1

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);
}

Solution 2

You want to read one record at a time from the database without loading all records in to memory. Sync, that would just be a foreach. To do the same thing but using an async connection method:

1) Keep your signature, and consume it using ForeachAsync

public IQueryable<Profile> GetAll()

and then consuming it like this:

await repository.GetAll().ForeachAsync(record => DoThingsWithRecord(record));

Do note that the action passed here is not actually awaited if you make it async, see referenced question below how to handle this if you go with this method

2) Change signature, and implement it like ForeachAsync does (I borrowed the example from this question, as it provides a proper await.)

public async Task WithAll(Func<Profile, Task> profileAsync, CancellationToken cancellationToken) {
    var asyncEnumerable = (IDbAsyncEnumerable<Profile>)db.Profiles.AsNoTracking()
                            .Where(i => i.IsDeleted == 0);
    using (var enumerator = asyncEnumerable.GetAsyncEnumerator())
    {

        if (await enumerator.MoveNextAsync(cancellationToken)
                .ConfigureAwait(continueOnCapturedContext: false))
        {
            Task<bool> moveNextTask;
            do
            {
                var current = enumerator.Current;
                moveNextTask = enumerator.MoveNextAsync(cancellationToken);
                await profileAsync(current); //now with await
            }
            while (await moveNextTask.ConfigureAwait(continueOnCapturedContext: false));
        }
    }
}