IQueryable doesn't implement IAsyncQueryProvider when convert Enumerable to IQueryable

Question

I use ReflectionIT.Mvc.Paging Nuget package for paging data but when I want to use CreateAsync method it return this exception :

The provider for the source 'IQueryable' doesn't implement'IAsyncQueryProvider'.

Only providers that implement'IAsyncQueryProvider' can be used for Entity Framework asynchronous operations.

This a simple Code that can create exception

Note : I use unit of work pattern to call data

1. This is my Entity

public class Entity
{
    [Key]
    public int Id { get; set; }
    
    public string Name { get; set; } = string.Empty;
    
    public string LastName { get; set; } = string.Empty;
}

2. This is my view Model

public class ViewModel
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public string LastName { get; set; } = string.Empty;
}

3. This my Context class

public class DataContext : DbContext
{
    public DataContext() : base() { }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseSqlServer("Connection-String");
    }

    public DbSet<Entity> Entities { get; set; }
}

4. This my Repository example

public class Repository<TEntity> : IRepository<TEntity> where TEntity : class
{
    private readonly DbSet<TEntity> dbSet;

    public Repository(DataContext context) => dbSet = context.Set<TEntity>();

    public async Task<IEnumerable<TEntity>> ReadAllAsync() => await dbSet.ToListAsync();
}

public interface IRepository<TEntity>
{
    Task<IEnumerable<TEntity>> ReadAllAsync();
}

5. This is UnitOfWork Example

public class UnitOfWork : IUnitOfWork
{
    private readonly DataContext context;

    public UnitOfWork() => context = new();

    public IRepository<TEntity> RepositoryBase<TEntity>() where TEntity :  class
    {    
        return new Repository<TEntity>(context);
    }
}

public interface IUnitOfWork
{
    IRepository<TEntity> RepositoryBase<TEntity>() where TEntity :  class;
}

6. In Program file add its service

builder.Services.AddTransient<IUnitOfWork, UnitOfWork>();

7. Now add it to controller and use it in action like this

public class HomeController : Controller
{
    private readonly IUnitOfWork _unitOfWork;

    public HomeController(IUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    public async Task<IActionResult> Index()
    {
        var model = (await _unitOfWork.RepositoryBase<Entity>().ReadAllAsync())
                    .Select(i => new ViewModel() 
                                 { 
                                     Id = i.Id, 
                                     Name = i.Name, 
                                     LastName = i.LastName 
                                 }).AsQueryable();

        var PagingModel = await PagingList.CreateAsync(model, 10, 10, "Name", "Name"); //! Error Line

        PagingModel.RouteValue = new RouteValueDictionary
        {
            {"row", 10 }
        };

        return View(PagingModel);
    }
}

Note : These code just a sample from main project.

Answer 1

Your exception is clear: you cannot apply async functions to IQueryable made from IEnumerable. IQueryable should start from DbSet

Your repository implementation has big drawback it has method ReadAllAsync() which loads whole table into the memory.

Change realization to return IQueryable:

public class Repository<TEntity> : IRepository<TEntity> where TEntity : class
{
    private readonly DbSet<TEntity> dbSet;

    public Repository(DataContext context) => dbSet = context.Set<TEntity>();

    public IQueryable<TEntity> GetQuery() => dbSet;
}

public interface IRepository<TEntity>
{
    IQueryable<TEntity> GetQuery();
}

Then just use in proper way:

public async Task<IActionResult> Index()
{
    var model = _unitOfWork.RepositoryBase<Entity>().GetQuery()
                .Select(i => new ViewModel() 
                { 
                    Id = i.Id, 
                    Name = i.Name, 
                    LastName = i.LastName 
                });

    var PagingModel = await PagingList.CreateAsync(model, 10, 10, "Name", "Name"); //! Error Line

    PagingModel.RouteValue = new RouteValueDictionary
    {
        {"row", 10 }
    };

    return View(PagingModel);
}

BUT, if you start learning EF Core, consider do not use Generic Repository pattern. It is anti-pattern for EF Core, DbSet is already repository, DbContext is already multi-repository UnitOfWork. You just add another not needed abstraction.