MSTest v2 and The source IQueryable doesn't implement IAsyncEnumerable with Entity Framework Core

Question

I am trying to create unit tests(MSTest v2) for a DAL library(EF core)

DataService

    public IQueryable<BalanceDTO> GetCollection()
    {
        var entities = dbContext.Balance;
        var dtos = mapper.Map<ICollection<BalanceDTO>>(entities).ToList();
        dtos.ForEach(_d =>
        {
            _d.MonthSort = _d.Date.Month;
            _d.MonthName = (new DateTimeFormatInfo()).GetMonthName(_d.MonthSort);
        });
        return dtos.AsQueryable();
    }

    public async Task<IList<BalanceDTO>> GetBalancesByYear(int year)
    {
        return await GetCollection().Where(_d => _d.Date.Year == year).OrderBy(_d => _d.MonthSort).ToListAsync();
    }

Test

    [TestMethod()]
    [DataTestMethod]
    [DataRow(2020, 2019)]
    public void GetBalancesByYearTest(int found, int notfound)
    {
        var _configuration = new ConfigurationBuilder()
            .SetBasePath(AssemblyProperties.AssemblyDirectory)
            .AddJsonFile("appsettings.json")
            .Build();
        var optionsBuilder = new DbContextOptionsBuilder<AccountManagerContext>();
        optionsBuilder.UseSqlServer(_configuration.GetConnectionString("AccountManagerLocalDB"));

        var balanceDataService = new BalanceDataService(optionsBuilder);
        var elementsFound = balanceDataService.GetBalancesByYear(found);
        var elementsNotFound = balanceDataService.GetBalancesByYear(notfound);

        Assert.IsNotNull(balanceDataService);
        Assert.IsTrue(elementsFound.Result.Count > 0);
        Assert.IsTrue(elementsNotFound.Result.Count == 0);
    }

But I get this error:

InvalidOperationException: The source IQueryable doesn't implement IAsyncEnumerable<AccountManager.DAL.DTO.BalanceDTO>. 
Only sources that implement IAsyncEnumerable can be used for Entity Framework asynchronous operations.

I have found a couple of link but couldn't figure out how to resolve this.

ToArrayAsync() throws "The source IQueryable doesn't implement IAsyncEnumerable"

How to overcome the IQueryable doesn't implement IAsyncQueryProvider while Mocking the FromSql() method?

Any idea about how to create tests for my DataService methods?

Answer 1

I ended up with this solution. If anyone sees an error or something to be improved, I do appreciate any recommendations.

    public IQueryable<BalanceDTO> GetCollection()
    {
        var entities = dbContext.Balance;
        var dtos = mapper.Map<ICollection<BalanceDTO>>(entities);
        foreach(var _d in dtos)
        {
            _d.MonthSort = _d.Date.Month;
            _d.MonthName = (new DateTimeFormatInfo()).GetMonthName(_d.MonthSort);
        };
        return dtos.AsQueryable();
    }

    public async Task<IList<BalanceDTO>> GetBalancesByYear(int year)
    {
        var entities = dbContext.Balance.Where(_d => _d.Date.Year == year).OrderBy(_d => _d.Date);
        var balanceDTOs = Task<IList<BalanceDTO>>.Factory.StartNew(() =>
        {
            var dtos = mapper.Map<IList<BalanceDTO>>(entities);

            foreach (var _d in dtos)
            {
                _d.MonthSort = _d.Date.Month;
                _d.MonthName = (new DateTimeFormatInfo()).GetMonthName(_d.MonthSort);
            };
            return dtos;
        });

        return await balanceDTOs;
    }

I've modified GetBalancesByYear so it doesn't make use of the GetCollection as it would not be performant if it has to collect the complete collection of elements and transform them into DTO just before filtering what it's not needed.

I am quite confused on who to create correctly the async methods for my DAL when they need to return DTO and not just Entities.