.Net Core 2.2 web api with Entity Framework and Linq not able to do async tasks?

Question

I have a .NET Core 2.2 web api in which I wanted to have the controllers return results asynchronously. In going async all the way, calls in the browser to test the get by id and get all worked.

The controller unit tests worked as well, but when I went to create my service level unit tests which involve mocking the context, I came across the error

System.AggregateException : One or more errors occurred. (The provider for the source IQueryable doesn't implement IAsyncQueryProvider. Only providers that implement IAsyncQueryProvider can be used for Entity Framework asynchronous operations.)

As I dug into this error, I came across blogs and stackoverflow articles that said the only way to do this is to wrap the code inside a Task.FromResult.

One such article is: https://expertcodeblog.wordpress.com/2018/02/19/net-core-2-0-resolve-error-the-source-iqueryable-doesnt-implement-iasyncenumerable/

This implies that EF is not actually able to do actual async work or that I do not understand something fundamental (the 2nd option is probably most likely - but I want to confirm).

Code-wise, my service is as follows (just the get methods to narrow this)

namespace MoneyManagerAPI.Services
{
    public class CheckingService : ICheckingService
    {
        readonly CheckbookContext context;

        public CheckingService(CheckbookContext context)
        {
            this.context = context;
        }

        public async Task<Checking[]> GetAllRecordsAsync()
        {
            return await Task.FromResult(context.Checking.OrderByDescending(m => m.Id).ToArray());
        }

        public async Task<Checking> GetByIdAsync(int id)
        {
            return await Task.FromResult(context.Checking.FirstOrDefault(c => c.Id == id));
            //return await context.Checking.FirstOrDefaultAsync(c => c.Id == id);
        }
    }
}

In the GetByIdAsync method, if the commented line of code is uncommented and the other return statement is commented instead the code still compiles but throws the exception method when tested.

My test class has the following code:

namespace Unit.Services
{
    [TestFixture]
    public class CheckingServiceTests : CheckingHelper
    {
        [Test]
        public void GetAllRecordsAsync_ShouldReturnAllRecords()
        {
            // arrange
            var context = this.CreateCheckingDbContext();
            var service = new CheckingService(context.Object);
            var expectedResults = Task.FromResult(CheckingHelper.GetFakeCheckingData().ToArray());

            // act
            var task = service.GetAllRecordsAsync();

            task.Wait();
            var result = task.Result;

            // assert
            expectedResults.Result.Should().BeEquivalentTo(result);
        }

        [Test]
        public void GetByIdAsync_ShouldReturnRequestedRecord()
        {
            // arrange
            var id = 2;
            var context = this.CreateCheckingDbContext();
            var service = new CheckingService(context.Object);
            var expectedResult = CheckingHelper.GetFakeCheckingData().ToArray()[1];

            // act
            var task = service.GetByIdAsync(id);

            task.Wait();
            var result = task.Result;

            // assert
            expectedResult.Should().BeEquivalentTo(result);
        }

        Mock<CheckbookContext> CreateCheckingDbContext()
        {
            var checkingData = GetFakeCheckingData().AsQueryable();
            var dbSet = new Mock<DbSet<Checking>>();
            dbSet.As<IQueryable<Checking>>().Setup(c => c.Provider).Returns(checkingData.Provider);
            dbSet.As<IQueryable<Checking>>().Setup(c => c.Expression).Returns(checkingData.Expression);
            dbSet.As<IQueryable<Checking>>().Setup(c => c.ElementType).Returns(checkingData.ElementType);
            dbSet.As<IQueryable<Checking>>().Setup(c => c.GetEnumerator()).Returns(checkingData.GetEnumerator());

            var context = new Mock<CheckbookContext>();
            context.Setup(c => c.Checking).Returns(dbSet.Object);

            return context;
        }
    }
}

Finally, the GetFakeCheckingData is as follows:

namespace Unit.Shared
{
    public class CheckingHelper
    {
        public static IEnumerable<Checking> GetFakeCheckingData()
        {
            return new Checking[3]
            {
                new Checking
                {
                    AccountBalance = 100,
                    Comment = "Deposit",
                    Confirmation = "Test Rec 1",
                    Credit = true,
                    Id = 1,
                    TransactionAmount = 100,
                    TransactionDate = new DateTime(2019, 8, 1, 10, 10, 10)
                },
                new Checking
                {
                    AccountBalance = 90,
                    Comment = "Withdrawal",
                    Confirmation = "Test Rec 2",
                    Credit = false,
                    Id = 2,
                    TransactionAmount = -10,
                    TransactionDate = new DateTime(2019, 8, 10, 10, 10, 10)
                },
                new Checking
                {
                    AccountBalance = 50,
                    Comment = "Deposit",
                    Confirmation = "Test Rec 3",
                    Credit = true,
                    Id = 3,
                    TransactionAmount = 50,
                    TransactionDate = new System.DateTime(2019, 9, 21, 10, 10, 10)
                }
            };
        }
    }
}

Answer 1

Don't use Task.FromResult where you are, because that will cause your live code to run synchronously (when you feed await a completed Task, which Task.FromResult does, everything runs synchronously). That will hurt your code in production.

Microsoft has documentation on how to deal with this in EF6 here, although it looks like it would apply the same to EF Core. The solution is to create your own async methods just for your tests.

However, you could look at refactoring your testing code to use an in-memory database, as the EF Core documentation explains here. The benefit there is that you use the same CheckbookContext and don't use Mock at all, so the async methods should still work just like they normally do.

---

For future reference, when you see an AggregateException, it means a legitimate exception is being thrown, it's just being wrapped inside an AggregateException. If you inspect the InnerExceptions property of the AggregateException, you will see the actual exception.

To avoid the real exception being put inside an AggregateException, don't use .Wait(). Make your test methods async Task and use await.

If for some reason you can't make them asynchronous, then use .GetAwaiter().GetResult(), which will still block the thread, but will give you the real exception.

Once you do that, you will still get an exception, but it will show you the actual problem.

Answer 2

You should follow async-await pattern for both app flow and unit test flow. You should retain the code as

public async Task<Checking> GetByIdAsync(int id)
{    
     return await context.Checking.FirstOrDefaultAsync(c => c.Id == id);
}

and change the unit test method signature to async Task. The following written async unit test invokes a async method as asynchronously.

    [Test]
    public async Task GetByIdAsync_ShouldReturnRequestedRecord()
    {
        // arrange
        var id = 2;
        var context = this.CreateCheckingDbContext();
        var service = new CheckingService(context.Object);
        var expectedResult = CheckingHelper.GetFakeCheckingData().ToArray()[1];

        // act
        var result = await service.GetByIdAsync(id);

        // assert
        expectedResult.Should().BeEquivalentTo(result);
    }