Testing repository queries of Entity Framework with NSubstitute

I am trying mock my dbContext in order to test my repository queries/filters. I have tried now with in memory database or by mocking the db context. In both cases I can only get it working when i don't use async calls. Also with MockQueryable.NSubstitute I could not get it working when having an extra filtering condition before running FirstOrDefaultAsync.

I always the exception: 'IQueryable' doesn't implement 'IAsyncQueryProvider'

I could not figure out where and how I can implement the provider. Does anymore have a good example?

TestSetup:

[Fact]
public async Task Get_ById_Returns_CorrectRequirement()
{
    // Arrange
    long requirementId = 7;
    string ntUser = "anyUser";

    var options = new DbContextOptionsBuilder<MyDbContext>()
        .UseInMemoryDatabase(databaseName: "TestDatabase")
        .Options;

    using var context = new MyDbContext(options);

    context.TDummyTable.AddRange(new List<TDummyTable>
{
    new TDummyTable { PkKey = 1, Valid = true },
    new TDummyTable { PkKey = requirementId, Valid = true },
});
    await context.SaveChangesAsync();

    // Act
    var requirement = await _sut.Get(requirementId, ntUser);

    // Assert
    Assert.Equal(requirementId, requirement.PkKey);
}

Repo method:

public async Task<TDummyTable> Get(long id, string userNt)
{
    var itemsQuery = this.context.TDummyTable.AsQueryable();
    itemsQuery = itemsQuery.Where(x => x.PkKey == 23);
    return await itemsQuery.FirstOrDefaultAsync(x => x.PkKey.Equals(id));
}

EDIT: This is how I try to use the MockQueryable in the arrange

    var data = new List<TDummyTable>
    {
        new TDummyTable { PkKey = 1, Valid = true},
        new TDummyTable { PkKey = requirementId,  Valid = true},
    };


    var mock = data.AsQueryable().BuildMockDbSet();
    context.TDummyTable.Returns(mock);

EDIT2: I kind of got it working by the hints of @stevePy but yet async does still makes problems. Result is always null I can basically not run the test on the return of the method.

TEST Method:

[Fact]
public async Task GetByFilter()
{
    //Arrange
    string ntUser = "AnyUser";
    int projectId = 50;
    List<TDummyTable> data = new List<TDummyTable>();
    data.Add(GenerateSampleData(74));
    data.Add(GenerateSampleData(projectId));
    data.Add(GenerateSampleData(38));
    data.Add(GenerateSampleData(projectId));
    data.Add(GenerateSampleData(projectId));

    var query = dummyRepository.GetQueryable().Returns(data.AsQueryable());

    //Something like this would be my prefered option but its not working
    //dummyRepository.Get(Arg.Any<IQueryable<TDummyTable>>()).Returns((IQueryable<TDummyTable> query) =>
    //{
    //    var result = query.ToList();
    //    return Task.FromResult(result);
    //});

    // Act
    var result = await _sut.Get(ntUser: ntUser, testingProjectId: projectId);

    // Assert
    // Verify that GetQueryable() was called
    dummyRepository.Received(1).GetQueryable();

    // Verify that Get was called with the correct query
    await dummyRepository.Received(1).Get(Arg.Is<IQueryable<TDummyTable>>(
        query => query.Count() == 3
                 && query.All(x => x.FkProjectId == projectId)
    ));
}

REPO Methods:

public IQueryable<TEntity> GetQueryable()
{
    return context.Set<TEntity>().AsQueryable();
}

public async Task<List<TEntity>> Get(IQueryable<TEntity> query)
{
    return await query.AsNoTracking().ToListAsync();
}

Service Method:

public async Task<List<TDummyTable>> Get(string ntUser, int? projectId = null)
{
    var query = dummyRepository.GetQueryable();
    if (projectId != null)
        query = query.Where(x => x.FkProjectId == projectId);
    return await dummyRepository.Get(query);
}

Unfortunately I don't have experience with NSubstitute specifically as I use Moq for mocking, but if you are using a Repository pattern then the idea is generally to have the Repository serve as the boundary for the mocking, avoiding the need to go down to the DbContext/DbSet.

The repository is the gatekeeper to the domain data. Business logic lives in the controllers, services, etc. which is what you want to test. So when you have business logic that is going to go to the Repository's .Get() method you mock the repository, not call a concrete repository with a mocked DbContext

var mockRepo = new Mock<DummyTableRepository>();
mockRepo.Setup(x => x.GetAsync(expectedId, expectedUserNt))
   .ReturnsAsync( new TDummyTable { PkKey = expectedId, Valid = true });

The above is Moq, but NSubstitute should have similar to handle mocking asynchronous calls.

The problem with your current approach is that you are trying to test Linq, that a Where condition can filter a row from a set. Linq is already tested, EF is already tested. Now you might have some more complex conditions enforced within a repository method, but I would suggest that these would be asserted as part of integration tests (Tests against a live database) that get run as part of a continuous integration rather than writing unit tests down at the DB level or even using something like an in-memory database. The reason is that behavior can change between Linq in memory and what gets transposed down to SQL for your given database, plus for the most part unit tests should be concerned with business logic, stuff that is variable in nature.

Where MockQueryable comes into play:

There are times where your repository won't return individual entities but rather it returns sets of entities. One option is to simply have the repository return an IEnumerable<TEntity> or IList<TEntity>, but this isn't very efficient. You may want to use projection to get a set of ViewModels, pagination, apply sorting, etc. Plus exposing synchronous and asynchronous flavors. Passing all of these concerns to the repository is complex, and leads to a lot of boiler plate. So one option is to allow repositories to return IQueryable<TEntity>. In this way the callers of the repository can decide exactly how the results would be consumed. The mocked repository cannot simply pass back new [] { new TEntity {...}, new TEntity {...}, ... }.AsQueryable(); if the consumers might be using asynchronous calls against it, so instead you use MockQueryable to return:

var query = new [] 
{ 
    new TEntity {...}, 
    new TEntity {...}, 
    ... 
}.BuildMock();

This way the consumers of the repository can use either sync or async calls against the results.

MockQueryable has a BuildMockDbSet() which is intended for cases where the services under test are using an injected DbContext rather than a repository. You would create a Mock of the DbContext then initialize it's DbSet reference to the BuildMockDbSet() instance. It looks like this may be what you attempted within the DbContext injected into the Repository, but it doesn't look like the example is the whole picture to see why it didn't appear to work. Since you have a repository I would highly recommend avoiding that complexity and instead use the repository as the boundary for your unit tests.

Update:

ok looking at the Edit #2, the mock repository is close, but it needs to return the MockQuertable wrapped List, not .AsQueryable():

List<TDummyTable> data = new List<TDummyTable>();
data.Add(GenerateSampleData(74));
data.Add(GenerateSampleData(projectId));
data.Add(GenerateSampleData(38));
data.Add(GenerateSampleData(projectId));
data.Add(GenerateSampleData(projectId));

var query = dummyRepository.GetQueryable().Returns(data.BuildMock());

BuildMock() is what MockQueryable exposes to create an IQueryable wrapper that works with async.

The next thing is the test itself is somewhat missing the mark in that you are testing the mocked repository, not the Service. The point of the repository pattern is to enable testing by serving as a boundary between the service (where all business logic should reside) and the data. (In this case, EF Entities)

So the goal isn't to create a mock of the repository then call the repository methods in a test, it is to create a mock of the Repository in a test which will create a Service, passing this mocked repository as the dependency, then test the service behaviour. The mock provides the data in place of a real repository and can assert that expected calls are made, and unexpected calls are not made for instance.

So this would look something like:

var testService = new Service(dummyRepository);

then the test calling the service to do what it should be expected to do.

There is also no need for the "Get" method on the repository when it returns IQueryable. The caller can decide how to consume the query:

The service call would be:

var query = dummyRepository.GetQueryable();
if (projectId != null)
    query = query.Where(x => x.FkProjectId == projectId);
return await query.AsNoTracking().ToListAsync();

In this way the Repository doesn't care about async vs. synchronous, tracking, etc. The caller has full control over how it's consumed. A good example is taking advantage of projection. If you just wanted a list of Ids and names:

var query = dummyRepository.GetQueryable();
if (projectId != null)
    query = query.Where(x => x.FkProjectId == projectId);

var results = await query.Select(x => new 
{
     x.Id,
     x.Name
}.ToListAsync();

The repository can serve any number of calls to retrieve data allowing the consumers to decide how to consume it. If a synchronous method wants to use the repository, that is fine, nothing in the repository changes. The consumer just calls .ToList() etc. instead of awaiting the async method.