locked
Problems in implementing Asynchronous Action: The provider for the source IQueryable doesn't implement IAsyncQueryProvider. Only providers that implement IAsyncQueryProvider can be used for Entity Framework asynchronous operations RRS feed

  • Question

  • User1984354752 posted

    Hi out there:

    I got a problem in implementing an Asynchronous Action. Here is my code bit by bit.

    1) The  Repository interface 

    public interface IGenericRepository<T> where T : class
    {

    IQueryable<T> GetAllIncluding(Expression<Func<T, bool>> filter = null,
    Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
    string includeProperties = "");

    }

    2) The Generic Repository 

    public class GenericRepository<T> : IRepository<T> where T : class
    {
    private readonly HousingContext _DbContext;

    private readonly DbSet<T> dbSet;

    public GenericRepository(HousingContext context)
    {
    this._DbContext = context;
    this.dbSet = this._DbContext.Set<T>();
    }

    public virtual IQueryable<T> GetAllIncluding(Expression<Func<T, bool>> filter = null,
    Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
    string includeProperties = "")
    {

    IQueryable<T> query = dbSet;
    if (filter != null)
    {
    query = query.Where(filter);
    }

    foreach (var includeProperty in includeProperties.Split
    (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
    {
    query = query.Include(includeProperty);
    }

    if (orderBy != null)
    {
    return orderBy(query);
    }
    else
    {
    return query;
    }
    }

    To avoid irreverent code, I omitted the unit of works  and the view model class as they work with a synchronous action. So I go straight to the controller action which is the one that triggers the exception.

    3) controller 

    public async Task<IActionResult> Index(string sortField, string currentSortField, string currentSortOrder, string SearchString, int? pageNo, string currentFilter)
    {


    List<InvoiceViewModel> viewmodel = new List<InvoiceViewModel>();
    //eager loading
    var Invoice_List = (from i in this._UoW.InvoicesRepository.GetAllIncluding(includeProperties: "FinancialyearNavigation,IdContractAmountsNavigation.IdContractNavigation.IdCompanyNavigation,IdContractAmountsNavigation.IdContractNavigation.ProjectNavigation")
    select new
    {
    i.Id,
    i.FinancialyearNavigation.Financialyear1,
    i.IdContractAmountsNavigation.IdContractNavigation.IdCompanyNavigation.Companyname,
    i.Financialyear,
    i.IdContractAmountsNavigation.ScopeOfWorks,
    i.IdContractAmountsNavigation.IdContract,
    i.IdContractAmountsNavigation.IdContractNavigation.ProjectNavigation.Project,
    IdProject = i.IdContractAmountsNavigation.IdContractNavigation.ProjectNavigation.Id,
    i.Invoicenumber,
    i.Workdone,
    i.SubmissionDate,
    i.ApprovedDate


    });
    foreach (var item in Invoice_List)
    {
    InvoiceViewModel objcvm = new InvoiceViewModel();

    objcvm.Id = item.Id;
    objcvm.Invoice = item.Invoicenumber;
    objcvm.Id_Contract = (int)item.IdContract;
    objcvm.Financial_year = item.Financialyear1;
    objcvm.Id_Financialyear = (int)item.Financialyear;
    objcvm.Company = item.Companyname;
    objcvm.Project = item.Project;
    objcvm.scopeofworks = item.ScopeOfWorks;

    objcvm.SubmissionDate = item.SubmissionDate;
    objcvm.ApprovedDate = item.ApprovedDate;
    objcvm.WorkDone = (decimal)item.Workdone;
    objcvm.Id_Project = (int)item.IdProject;
    viewmodel.Add(objcvm);

    }
    if (SearchString != null)
    {
    pageNo = 1;
    }
    else
    {
    SearchString = currentFilter;
    }
    ViewData["Invoice"] = sortField;
    ViewBag.CurrentFilter = SearchString;

    if (!String.IsNullOrEmpty(SearchString))
    {
    viewmodel = viewmodel.Where(s => s.Invoice.Contains(SearchString)).ToList();


    }
    viewmodel = this.SortViewModelData(viewmodel, sortField, currentSortField, currentSortOrder);

    int pageSize = 10;


    return View(await PagingList<InvoiceViewModel>.CreateAsync(viewmodel.AsQueryable<InvoiceViewModel>().AsNoTracking(), pageNo ?? 1, pageSize));

    }

    The debugger says:

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

    • Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync<TSource, TResult>(MethodInfo operatorMethodInfo, IQueryable<TSource> source, Expression expression, CancellationToken cancellationToken)

    • Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync<TSource, TResult>(MethodInfo operatorMethodInfo, IQueryable<TSource> source, CancellationToken cancellationToken)

    • Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)

    • Finance_Management.Paging.PagingList<T>.CreateAsync(IQueryable<T> source, int pageIndex, int pageSize) in PagingList.cs

      <button class="expandCollapseButton" data-frameid="frame4">+</button>
      1. var count = await source.CountAsync();
    • Finance_Management.Controllers.InvoicesController.Index(string sortField, string currentSortField, string currentSortOrder, string SearchString, Nullable<int> pageNo, string currentFilter) in InvoicesController.cs

      <button class="expandCollapseButton" data-frameid="frame5">+</button>
      1. return View(await PagingList<InvoiceViewModel>.CreateAsync(viewmodel.AsQueryable<InvoiceViewModel>().AsNoTracking(), pageNo ?? 1, pageSize));
    • Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)

    • System.Threading.Tasks.ValueTask<TResult>.get_Result()

    • System.Runtime.CompilerServices.ValueTaskAwaiter<TResult>.GetResult()

    4)  PagingListClass 

    public class PagingList<T> : List<T>
    {
    public int PageIndex { get; private set; }
    public int TotalPages { get; private set; }

    public PagingList(List<T> items, int count, int pageIndex, int pageSize)
    {
    PageIndex = pageIndex;
    TotalPages = (int)Math.Ceiling(count / (double)pageSize);

    this.AddRange(items);
    }

    public bool HasPreviousPage
    {
    get
    {
    return (PageIndex > 1);
    }
    }

    public bool HasNextPage
    {
    get
    {
    return (PageIndex < TotalPages);
    }
    }

    public int TotalPageNo
    {
    get
    {
    return TotalPages;
    }
    }


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


    }
    }

    Your assistance is much appreciated. 

    Monday, August 24, 2020 8:06 AM

All replies

  • Monday, August 24, 2020 12:53 PM
  • User1984354752 posted

    Hi Dad.

    I don't figure out at all. You advised me to implement a public interface I AsyncQueryProvider but I don't know how this is bound to my repository pattern ???...Can you be more specific please

     public interface IAsyncQueryProvider : IQueryProvider
    Monday, August 24, 2020 2:52 PM
  • User-474980206 posted

    your repository methods should return  IAsyncQueryProvider, not IQueryProvider. As ef core is already a repository pattern, not sure why you wrap it. A dal or Busines object makes sense, 

    also you should never use sync database calls in a mvc action or razor page. These will lead to serious scaling issues.

    Monday, August 24, 2020 3:32 PM
  • User1120430333 posted

    Hi Dad.

    I don't figure out at all. You advised me to implement a public interface I AsyncQueryProvider but I don't know how this is bound to my repository pattern ???...Can you be more specific please

     public interface IAsyncQueryProvider : IQueryProvider

    IMHO, you should abandon the generic repository.  EF is already using UoW and the repositroy patterns.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.dbcontext?view=entity-framework-6.2.0

    <copied>

    A DbContext instance represents a combination of the Unit Of Work and Repository patterns such that it can be used to query from a database and group together changes that will then be written back to the store as a unit. DbContext is conceptually similar to ObjectContext.

    <end>

    I like to use the DAO pattern.

    https://javarevisited.blogspot.com/2013/01/data-access-object-dao-design-pattern-java-tutorial-example.html

    Also,  no database access in the controller.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    https://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

    <copy>

    An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic. For example, if you are using the Microsoft Entity Framework to access your database, then you would create your Entity Framework classes (your .edmx file) in the Models folder.

    A view should contain only logic related to generating the user interface. A controller should only contain the bare minimum of logic required to return the right view or redirect the user to another action (flow control). Everything else should be contained in the model.

    In general, you should strive for fat models and skinny controllers. Your controller methods should contain only a few lines of code. If a controller action gets too fat, then you should consider moving the logic out to a new class in the Models folder.

    <end>

    Monday, August 24, 2020 9:08 PM
  • User1984354752 posted

    Hellow DA924:

    Thanks for your response but my issue remains. I still don't understand how implement IAsyncQueryProvider in my project Interfaces. The link below explains how to to set it up but what's next ? You mentioned that I have to return IAsyncQueryProvider instead of Iquerable but don't know how to do it.

    Can you show me a piece of coding as a lead please? 

    https://apisof.net/catalog/Microsoft.EntityFrameworkCore.Query.Internal.IAsyncQueryProvider

    Monday, September 21, 2020 12:01 PM
  • User-474980206 posted

    You should not sync db calls with asp.net core. An asp,net core repository should have no sync calls. Rewrite your repository to be async and implement IAsyncQueryProvider. 

    you appear to just be copy and pasting code you do not understand, that is not applicable to your current project. If you must copy and paste, find an example for ef core.

    Monday, September 21, 2020 7:10 PM
  • User1984354752 posted

    Bruce:

    As per below, my repository  has both sync and async calls.  In the initial post you can see that I'm using async calls  ( see below) so I don't know what you refer with  sync calls.  As per your advise, I want to implement IAsyncQueryProvider but don't know how to it in my repository as I haven't found a clear examples on the web. 

    I found the copy and pasting code's comment  very subjective to say the least., but is your appreciation. Most of the people who seeks for assistance aren't specialist and have spent a lot of time ( my case)  searching in the web. It may appear to me that you cannot provide proper assistance.......

    Have a nice day 

     public async Task<IActionResult> Index(string sortOrder, string currentFilter, string searchString, int? pageNumber)
            {

               CODE HERE 

            return View(await PagingList<InvoiceViewModel>.CreateAsync(query.AsNoTracking(), pageNumber ?? 1, pageSize));


            }

     public  class GenericRRepository<T> : IGenericRepository<T> where T : class
        {
            protected HousingContext _context;

            
            private readonly IUnitOfWorkAsync _unitOfWork;

            public GenericRRepository(HousingContext context)
            {
                _context = context;

                _unitOfWork = new UnitOfWorkAsc(context);
            }
            public IQueryable<T> Query()
            {
                return _context.Set<T>().AsQueryable();
            }

            public ICollection<T> GetAll()
            {
                return _context.Set<T>().ToList();
            }

            public async Task<ICollection<T>> GetAllAsync()
            {
                return await _context.Set<T>().ToListAsync();
            }

            public T GetById(int id)
            {
                return _context.Set<T>().Find(id);
            }

            public async Task<T> GetByIdAsync(int id)
            {
                return await _context.Set<T>().FindAsync(id);
            }

            public T GetByUniqueId(string id)
            {
                return _context.Set<T>().Find(id);
            }

            public async Task<T> GetByUniqueIdAsync(string id)
            {
                return await _context.Set<T>().FindAsync(id);
            }

            public T Find(Expression<Func<T, bool>> match)
            {
                return _context.Set<T>().SingleOrDefault(match);
            }

            public async Task<T> FindAsync(Expression<Func<T, bool>> match)
            {
                return await _context.Set<T>().SingleOrDefaultAsync(match);
            }

            public ICollection<T> FindAll(Expression<Func<T, bool>> match)
            {
                return _context.Set<T>().Where(match).ToList();
            }

            public async Task<ICollection<T>> FindAllAsync(Expression<Func<T, bool>> match)
            {
                return await _context.Set<T>().Where(match).ToListAsync();
            }

            public T Add(T entity)
            {
                _context.Set<T>().Add(entity);
                _context.SaveChanges();
                return entity;
            }

            public async Task<T> AddAsync(T entity)
            {
                _context.Set<T>().Add(entity);
                await _unitOfWork.Commit();
                return entity;
            }

            public T Update(T updated)
            {
                if (updated == null)
                {
                    return null;
                }

                _context.Set<T>().Attach(updated);
                _context.Entry(updated).State = EntityState.Modified;
                _context.SaveChanges();

                return updated;
            }

            public async Task<T> UpdateAsync(T updated)
            {
                if (updated == null)
                {
                    return null;
                }

                _context.Set<T>().Attach(updated);
                _context.Entry(updated).State = EntityState.Modified;
                await _unitOfWork.Commit();

                return updated;
            }

            public void Delete(T t)
            {
                _context.Set<T>().Remove(t);
                _context.SaveChanges();
            }

            public async Task<int> DeleteAsync(T t)
            {
                _context.Set<T>().Remove(t);
                return await _unitOfWork.Commit();
            }

            public int Count()
            {
                return _context.Set<T>().Count();
            }

            public async Task<int> CountAsync()
            {
                return await _context.Set<T>().CountAsync();
            }

            public IEnumerable<T> Filter(Expression<Func<T, bool>> filter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null, string includeProperties = "", int? page = null,
                int? pageSize = null)
            {
                IQueryable<T> query = _context.Set<T>();
                if (filter != null)
                {
                    query = query.Where(filter);
                }

                if (orderBy != null)
                {
                    query = orderBy(query);
                }

                if (includeProperties != null)
                {
                    foreach (
                        var includeProperty in includeProperties.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
                    {
                        query = query.Include(includeProperty);
                    }
                }

                if (page != null && pageSize != null)
                {
                    query = query.Skip((page.Value - 1) * pageSize.Value).Take(pageSize.Value);
                }

                return query.ToList();
            }


            public IQueryable<T> Getlist(Expression<Func<T, bool>> filter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null, string includeProperties = "", int? page = null,
                int? pageSize = null)
            {
                IQueryable<T> query = _context.Set<T>();
                if (filter != null)
                {
                    query = query.Where(filter);
                }

                if (orderBy != null)
                {
                    query = orderBy(query);
                }

                if (includeProperties != null)
                {
                    foreach (
                        var includeProperty in includeProperties.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
                    {
                        query = query.Include(includeProperty);
                    }
                }

                if (page != null && pageSize != null)
                {
                    query = query.Skip((page.Value - 1) * pageSize.Value).Take(pageSize.Value);
                }

                return  query.AsQueryable();
            }


            public IQueryable<T> FindBy(Expression<Func<T, bool>> predicate)
            {
                return _context.Set<T>().Where(predicate);
            }

            public bool Exist(Expression<Func<T, bool>> predicate)
            {
                var exist = _context.Set<T>().Where(predicate);
                return exist.Any() ? true : false;
            }
        }

    Tuesday, September 22, 2020 9:43 AM
  • User-474980206 posted

    EF core is a repository. IAsyncQueryProvider is one of its interfaces. Your issue is you are calling code written for the EF core repo, not the generic IQueryable.

    The easiest implementation in you repro is just expose the DbSet which implements the interface. The other is write your own AsQuerable() extension method that returns a custom object that implements both interfaces. Hint, just return the underlying interface.

    But in either case, you are tightly binding to the EF core implementation, so what is the point of your repository?

    Tuesday, September 22, 2020 3:23 PM