.Net Core web API Paged List - The provider for the source 'IQueryable' doesn't implement 'IAsyncQueryProvider'

Question

After got no answers for my other question here, I've made a different implementation, this time a Union of two Lists instead of the Automapper mapping projection.

public async Task<ActionResult<IEnumerable<MemberDto>>> GetMembersList([FromQuery] UserParams userParams)
{
    var members = await _unitOfWork.UserRepository.GetMembersAsync(userParams);

    Response.AddPaginationHeader(members.CurrentPage, members.PageSize,
        members.TotalCount, members.TotalPages);

    return Ok(members);
}

The repository:

    public async Task<PagedList<MemberDto>> GetMembersAsync(UserParams userParams)
    {
        var members1 = await _context.Users
        .Where(x => x.Photos.Any(y => y.Url.Substring(0, 4) == "http"))
            .Select(m => new MemberDto
            {
                Id = m.Id,
                UserName = m.UserName,
            }).ToListAsync();
    
        var member2 = await _context.Users
        .Where(x => x.Photos.Any(y => y.Url.Substring(0, 4) != "http"))
            .Select(m => new MemberDto
            {
                Id = m.Id,
                UserName = m.UserName,
            }).ToListAsync();
    
        var members = members1.Union(member2).OrderBy(x => x.DisplayName).ToList();
        var query = members.AsQueryable();
query = query.Where(u => u.UserName == "John");
        return await PagedList<MemberDto>.CreateAsync(query, userParams.PageNumber, userParams.PageSize);
    }

And the paged list class:

public static async Task<PagedList<T>> CreateAsync(IQueryable<T> source, int pageNumber, 
    int pageSize)
{
    var count = await source.CountAsync();
    var items = await source.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToListAsync();
    return new PagedList<T>(items, count, pageNumber, pageSize);
}

But when I send the request I get the following error:

System.InvalidOperationException: The provider for the source 'IQueryable' doesn't implement 'IAsyncQueryProvider'. Only providers that implement 'IAsyncQueryProvider' can be used for Entity Framework asynchronous operations.

Some answers in Stackoverflow provide a workaround using View() which is used for only for MVC.

`ToList().AsQueryable()` - this is no more EF Core queryable, hence the error message. Try removing all `ToList` / `ToListAsync` calls from the code. — Ivan Stoev, Oct 23 '21 at 11:09
If it can't be translated, then it can't be efficiently paginated (all the data is already loaded in memory). To do pagination in memory, create and use another non async `Create` method which takes `IEnumerable` and uses sync LINQ to Objects methods (e.g. `source.Count()`) inside. — Ivan Stoev, Oct 23 '21 at 11:31
I've created the non async method, but it still cannot be translated. — Sami-L, Oct 23 '21 at 11:41

Ivan Stoev · Accepted Answer · 2021-10-23T12:38:54.937

6

All EF Core async queryable extensions work only with EF Core IQueryable implementations. While ToList() loads all data in memory, and the AsQueryable() is LINQ to Objects IQueryable implementation which cannot be used with EF Core extenson methods.

So the solution is to remove all ToList / await ToListAsync() calls, thus keeping the result EF Core IQueryable.

The problem as I understand is that EF Core is unable to translate the query, so you are executing it and putting the result in memory. So this is a workaround, but then the query cannot be efficiently paginated server side, thus no need of async processing. What you should do is to create and use synchronous Create method overload like this

public static PagedList<T> Create(IEnumerable<T> source, int pageNumber, 
    int pageSize)
{
    var count = source.Count();
    var items = source.Skip((pageNumber - 1) * pageSize).Take(pageSize);
    return new PagedList<T>(items, count, pageNumber, pageSize);
}

and modify the code to use it instead of CreateAsync

// original code...
var members = members1.Union(member2).OrderBy(x => x.DisplayName).ToList();
var query = members //.AsQueryable() not needed anymore
    .Where(u => u.UserName == "John");
return PagedList<MemberDto>.Create(query, userParams.PageNumber, userParams.PageSize);

But this should be used only as last resort. Before doing that it's better to try make the EF Core query translatable. In this particular case I guess the problem is with Union operator (or something inside its parts), which with the sample could easily be eliminated (the Union looks unnecessary), but I guess that's not the real case. If you provide a more realistic example and well as the target EF Core version, we can take a look if something can be done (unfortunately EF Core query translation is still trial-and-error game).

edited Oct 23 '21 at 12:38

answered Oct 23 '21 at 11:47

Ivan Stoev

195,425
15
312
343

I tried exactly what you suggested but the query still could not be translated, error message: "could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'." – Sami-L Oct 23 '21 at 11:55
Which query cannot be translated? The answer is based on your original code in the question where `members1` and `members2` are lists, i.e. already executed and materialized queries. Hence there is no query. – Ivan Stoev Oct 23 '21 at 12:04
It is needed to keepToList / await ToListAsync() calls for members1 and members2 and then use the non async method to make it work. – Sami-L Oct 23 '21 at 12:06
Of course. I thought that was obvious - either keep all parts queryable and use `CreateAsync`, either use lists (keep the original approach) and use `Create` at the end. – Ivan Stoev Oct 23 '21 at 12:08
only using lists for parts work, then in my original code I have "var query = members.AsQueryable();" to use it for e.g. "query = query.Where(u => u.UserName == "John");" if I use "return PagedList.Create(query, userParams.PageNumber, userParams.PageSize);" it returns an empty result. – Sami-L Oct 23 '21 at 12:23
Look at the answer. `//...` means the original code up to the snippet starting with `var members = ...` and ending with `return ...`, which is the modification needed. There is no `query` variable there. I could have easily copy/pasting the whole original code and modify it, but that way the modification will be hidden inside. – Ivan Stoev Oct 23 '21 at 12:30
I have updated the question to highlight the query variable which was in the original code. Thanks for your patience. – Sami-L Oct 23 '21 at 12:33
Ah, so you just have `Where` which you skipped in the original post. No problem, just use LINQ to Objects (no need `AsQueryable()` anymore after getting the `members` list). – Ivan Stoev Oct 23 '21 at 12:36
But note that it is better to apply such filters in advance, e.g. declare something like `var users = _context.Users.Where(u => u.UserName == "John");` and use it in the original code in place of `_context.Users`. It would just make the operation more performant (will load less data in memory). – Ivan Stoev Oct 23 '21 at 12:43
You're right, applying such filter in advance, and using ListAsync (the original approach), and use the non async "Create" method made it work fine, I guess this is the optimal solution. – Sami-L Oct 24 '21 at 00:14

.Net Core web API Paged List - The provider for the source 'IQueryable' doesn't implement 'IAsyncQueryProvider'

1 Answers1