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

Answer 1

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