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?

score 0 · Answer 1 · edited Mar 12 '23 at 10:26

0

Entity Framework works with DbSets, that are more than queryables.

You can use the In-Memory provider for your tests, or mock the Dbsets.

edited Mar 12 '23 at 10:26

marc_s

732,580
175
1,330
1,459

answered Apr 21 '20 at 07:16

Paulo Morgado

14,111
3
31
59

Thanks, but isn't that for xUnit? – blfuentes Apr 21 '20 at 18:45
I see. Still this doesn't fix the issue I am facing with the methods. `GetBalancesByYear` throws the same exception. I cannot use directly use DbSet because I need to adapt the entities to the corresponding `DTO` – blfuentes Apr 21 '20 at 19:15
Does `GetBalancesByYear` work outside your unit tests? `AsQueryable` outside of testing is, usually, a code smell. Whatever you think it's making, – Paulo Morgado Apr 21 '20 at 22:33
So far... yes, or at least I think so. When I call the `GetColletion()` method I see in the SQL profiler `SELECT [b].[BalanceID], [b].[Date], [b].[DateClosed], [b].[FinalAmount], [b].[InitialAmount], [b].[PercentVariation], [b].[Variation] FROM [Balance] AS [b]` What would you recommend as solution to use DbSet with the AutoMapper ? – blfuentes Apr 22 '20 at 19:36
It was been years since I've used AutoMapper, but I recall it doing a good job of rewriting the query. Check their documentation. – Paulo Morgado Apr 22 '20 at 22:23

score 0 · Accepted Answer · answered Apr 21 '20 at 21:00

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.

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

2 Answers2