Microsoft InMemoryDatabase doesn't connect relationship in Entity Framework during test

Question

Result in test should be 3, but it's zero. This is because in Get method .Include() line doesn't connect Task to Entries. But in debug mode in Autos they seems to be connected. This works in live code, so I think it's the problem with in-memory database configuration.

Get method:

public IEnumerable<Entry> Get(string userId, DateTime from, DateTime to)
{
     List<Entry> result;

     try
     {
         result = _db.Entries
             .Include(n => n.Task.ApplicationUser)   //that's where InMemoryDb doesn't connect Task to Entries
             .Where(n => n.Task.ApplicationUserId == userId).Select(n => n)
             .Where(n => (n.Created.Date >= from.Date) && (n.Created.Date <= to.Date))
             .ToList();
     }
     catch (Exception e)
     {
         _logger.LogError($"Exception: {e.Message} StackTrace: {e.StackTrace}");
         return new List<Entry>();
     }
     return result;
};

In-memory database configuration:

private async Task<TasksContext> GetDbContext()
{
     var options = new DbContextOptionsBuilder<TasksContext>()
         .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
         .Options;

     // creating DBcontext from mock
     var dbContext = new TasksContext(options);
     dbContext.Database.EnsureDeleted();
     dbContext.Entries.AsNoTracking();

     dbContext.Tasks.Add(new Task { Id = 1, Name = "RunningId1", GroupId = 4, Created = _createdFirstEntry, ApplicationUserId = _userId });
     dbContext.Tasks.Add(new Task { Id = 2, Name = "CookingId2", GroupId = 4, Created = _createdSecondEntry, ApplicationUserId = _userId });
     dbContext.Tasks.Add(new Task { Id = 3, Name = "ReadingId3", GroupId = 4, Created = _createdThirdEntry, ApplicationUserId = _userId });

     dbContext.Entries.Add(new Entry { Id = _entryId_1, TaskId = _taskId_1, Duration = _duration, Created = _createdFirstEntry, LastUpdated = null, Deleted = null });
     dbContext.Entries.Add(new Entry { Id = _entryId_2, TaskId = _taskId_2, Duration = _duration, Created = _createdSecondEntry, LastUpdated = null, Deleted = null });
     dbContext.Entries.Add(new Entry { Id = _entryId_3, TaskId = _taskId_3, Duration = _duration, Created = _createdThirdEntry, LastUpdated = null, Deleted = null });
     await dbContext.SaveChangesAsync();
     
     return dbContext;
}

Test method:

public async void Get_TakesValidUserIdAndDateRange_ReturnsListWithThreeElements()
{
     // Arrange
     var dbContext = await GetDbContext();
     var entryRepository = new EntryRepository(dbContext, _logger, _mapper);

     // Act
     var result = entryRepository.Get(_userId,_createdFirstEntry, _createdThirdEntry);

     // Assert
     result.Should().BeOfType(typeof(List<Entry>));
     result.Count().Should().Be(3); 
}

I don't know what to do.

Answer 1

This is a known issue/limitation which has been introduced into the InMemory provider and appears to be tied to the use of Nullable navigation properties with the [Required] attribute.

Some discussions on the change: https://github.com/dotnet/efcore/issues/9470 https://github.com/dotnet/EntityFramework.Docs/pull/3512

So it seems that:

[Required]
public Task? Task { get; set; }

... would not work, but:

public Task Task { get; set; }

should possibly. Though you will get the CS8618 compiler warning. For entities I just mitigate this with a protected default constructor and warning ignore:

#pragma warning disable CS8618 // Non-nullable field must contain a non-null value when exiting constructor. Consider declaring as nullable.
    /// <summary>
    /// Constructor used by EF.
    /// </summary>
    [ExcludeFromCodeCoverage]
    protected Entry() {}
#pragma warning restore CS8618 // Non-nullable field must contain a non-null value when exiting constructor. Consider declaring as nullable.

The InMemory Database provider is no longer actively supported and not recommended beyond simple test scenarios. For unit testing you should consider a pattern that is mock-able, where integration tests would run against a database replica.

When it comes to supporting unit testing I use a Repository pattern that exposes IQueryable<TEntity>. Assuming your "Get" method isn't already a Repository (in which case that would be the boundary to mock) From there the Repository can be mocked and you can use something like MockQueryable.Core (https://www.nuget.org/packages/MockQueryable.Core) to wrap your sample collection of test data to work with both synchronous and asynchronous Linq operations.