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.

Harald Coppoolse · Accepted Answer · 2020-07-14 11:45:36Z

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

I believe you wanted to write static IQueryable<Address> QueryAddresses(this IQueryable<Customer> customers) instead of static IQueryable<Address> QueryAddresses(this IQueryable<Address> customers), right beneath the statement Examples. No async needed: no query executed:

Community · Accepted Answer · 2018-08-29 09:16:37Z

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

Eldho · Accepted Answer · 2018-08-29 06:27:49Z

0

IMHO

Keep this method as it is.

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

Use this method like this. Whenever you use GetAll() add the async/await at that point.

public async Task<List<Profile>> GetAllProfiles(int userId) {
 return await GetAll().Where(u => u.User.Id == userId).ToListAsync();
}

IQueryable doesn't access database unless a ToList(),FirstOrDefault() ... some operation is performed.
You cannot add async/await to it.
Instead the GetAllProfiles() method is an I/O operation which performs a db operation so you can Await on it.

Comments

Ahmed Yousif · Accepted Answer · 2018-08-29 05:40:28Z

-2

you can select async using following sample code

public async System.Threading.Tasks.Task<List<Profile>> GetAll()
    {
        return await  db.Profiles.AsNoTracking().Where(i => i.IsDeleted == 0).ToListAsync();
    }

but this implementation will resolve the SQL statement and execute it when call

There is not any reason to await what you are trying to do. A queryable is an expression of a query, meaning nothing that actually do work against the database only when you call ToList() that mean the query is exectued on the database.

edited Aug 29, 2018 at 5:40

answered Aug 29, 2018 at 5:34

Ahmed Yousif

2,3481 gold badge13 silver badges17 bronze badges

5 Comments

Matthiee Over a year ago

Don't call ToList this will force all the profiles to be loaded in memory at once

Abhimanyu Over a year ago

Can't use ToList(), it will become like 1 second to 5 second in terms of call duration.

Ahmed Yousif Over a year ago

@Matthiee I use ToListAsync(); because As I understand from question He wnat o make async call for select all but I He want to use Iqueryable I mean nothing because It won't executed untill call ToListAsync(); or ToList();

Abhimanyu Over a year ago

ToListAsync() also loads data into memory, that's what I know so I can't.

Ahmed Yousif Over a year ago

yes I mentioned that in the answer but it executed in async call in another thread but if he want to use Iqueryable no need to use async call

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related