Search code examples
c#entity-frameworkasynchronousrepository-patternaspnetboilerplate

Accessing ToListAsync() from Custom Repository for SqlQuery in ASPNetBoilerplate


I'm using ASP.Net Boilerplate. I have implemented IRepository to create a custom Repository so I could add a custom method for returning data from a Stored Procedure. However, no matter how I seem to structure the Task, async or await components I get the following error:

System.InvalidOperationException: The provider for the source IQueryable doesn't implement IDbAsyncQueryProvider. Only providers that implement IDbAsyncQueryProvider can be used for Entity Framework asynchronous operations. For more details see http://go.microsoft.com/fwlink/?LinkId=287068.

My Repository looks like:

namespace myApp.EntityFramework.Repositories
{
    public class TruckRepository : UHaulTrucks.HoustonRepositoryBase<DailyDockQuery>, ITruckRepository
    {

        public TruckRepository(IDbContextProvider<UHaulHoustonDbContext> dbContextProvider) : base(dbContextProvider)
        {

        }

  public IQueryable<DailyDockQuery> GetDailyDockQuery()
        {

           var ret = Context.Database.SqlQuery<DailyDockQuery>("exec Central_DailyDockQuery").AsQueryable();
            return ret;
        }
   }
}

And my service:  

public async Task<PagedResultOutput<DailyDockQueryListDto>> GetDailyDockQuery()
        {
            var query = _truckRepository.GetDailyDockQuery();

            var dailyCount = await query.CountAsync();


            var dailies = await query.ToListAsync();

            var dailiesDtos = dailies.MapTo<List<DailyDockQueryListDto>>();

            return new PagedResultOutput<DailyDockQueryListDto>(
                dailyCount,
                dailiesDtos
                );
        }

The error above will be generated at any attempt to perform an async method, such as:

var dailyCount = await query.CountAsync();

or

var dailies = await query.ToListAsync();

As I've mentioned I have tried several variations in the Repository and Service with Task and async combinations with the same results. I'm new at implementing custom Repositories in this way and suspect although I should be getting the benefit of CountAsync and ToListAsync from IRepository that I still need to implement those in the custom class.

Any help is greatly appreciated. Still finding my way here.


Solution

  • Executing stored procedure (select records) sample code in Aspnetboilerplate:

    public async Task<List<string>> GetUserNames()
    {
        EnsureConnectionOpen();
    
        using (var command = CreateCommand("GetUsernames", CommandType.storedProcedure))
        {
            using (var dataReader = await command.ExecuteReaderAsync())
            {
                var result = new List<string>();
    
                while (dataReader.Read())
                {
                    result.Add(dataReader["UserName"].ToString());
                }
    
                return result;
            }
        }
    }
    

    I highly recommend you to read the document about using stored procedures in Aspnetboilerplate. There's everything you need: https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html