Dbset of in memory database returns entity with empty collection navigation property

Question

The problem with the test:

[Fact]
public async Task CountOfDaysOffInCurrentYearToToday()
{
    var application1 = new Domain.Entities.Application
    {
        Type = ApplicationType.DayOff, Status = ApplicationStatus.Approved, StatedDates =
        {
            new ApplicationDate { Date = new DateTime(2021, 4, 1) },
            new ApplicationDate { Date = new DateTime(2022, 4, 1) },
            new ApplicationDate { Date = new DateTime(2023, 4, 1) }
        }
    };

    var application2 = new Domain.Entities.Application
    {
        Type = ApplicationType.DayOffAtExpenseOfVacation, Status = ApplicationStatus.Approved, StatedDates =
        {
            new ApplicationDate { Date = new DateTime(2021, 4, 1) },
            new ApplicationDate { Date = new DateTime(2022, 4, 1) },
            new ApplicationDate { Date = new DateTime(2023, 4, 1) }
        }
    };

    var application3 = new Domain.Entities.Application
    {
        Type = ApplicationType.DayOffAtExpenseOfVacation, Status = ApplicationStatus.Approved, StatedDates =
        {
            new ApplicationDate { Date = new DateTime(2021, 4, 1) },
            new ApplicationDate { Date = new DateTime(2022, 4, 1) },
            new ApplicationDate { Date = new DateTime(2023, 4, 1) }
        }
    };

    var user = new AppUser
    {
        ApplicantApplications = { application1, application2, application3 }
    };

    var context = GetDbContext();
    await context.Users.AddAsync(user);
    await context.SaveChangesAsync();

    var bl = new UserCalendarBL(context, GetDateTime());

    Assert.Equal(4, await bl.CountOfDaysOffInCurrentYearToToday(user.Id));
}

When I debug it, it shows that StatedDates is empty in every application:

private static IDbContext GetDbContext()
{
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseInMemoryDatabase(new StackFrame(1, true).GetMethod()!.Name)
        .Options;
    return new AppDbContext(options);
}

private IDateTimeService GetDateTime()
{
    var mock = new Mock<IDateTimeService>();
    mock.Setup(e => e.Now()).Returns(new DateTime(2022, 5, 1));
    return mock.Object;
}

These are entity types:

public class Application
{
    public int Id { get; set; }

    public AppUser Applicant { get; set; }
    public string ApplicantId { get; set; }

    public DateTime DateOfCreation { get; set; }
    public DateTime? DateOfSent { get; set; }

    public ApplicationType Type { get; set; }
    public ApplicationStatus Status { get; set; }

    public List<ApplicationDate> StatedDates { get; set; } = new List<ApplicationDate>();

    public AppUser Coordinator { get; set; }
    public string CoordinatorId { get; set; }

    public string Comment { get; set; }
}

public class AppUser : IdentityUser
{
    public List<Application> ApplicantApplications { get; set; } = new List<Application>();
    public List<Application> CoordinatorApplications { get; set; } = new List<Application>();
    public string FullName { get; set; }
    public string Position { get; set; }
    public int? CountOfUnusedVacationDaysOverPastYears { get; set; }
}

public class ApplicationDate
{
    public DateTime Date { get; set; }
    public int ApplicationId { get; set; }
    public Application Application { get; set; }
}

ApplicationDate has composite key of Date and ApplicationId

These are testing methods:

public async Task<int> CountOfDaysOffInCurrentYearToToday(string userId)
{
    return await Task.Run(() =>
    {
        var applications = ApprovedApplicationsInCurrentYearToToday(userId).AsParallel().Where(
            application =>
                application.Type == ApplicationType.DayOff ||
                application.Type == ApplicationType.DayOffAtExpenseOfVacation ||
                application.Type == ApplicationType.DayOffAtExpenseOfWorkingOut).ToImmutableArray();

        return applications.Sum(CountOfApplicationDatesInCurrentYearToToday);
    });
}

private IEnumerable<Domain.Entities.Application> ApprovedApplicationsInCurrentYearToToday(string userId)
{
    return _context.Applications
        .Include(application => application.Applicant)
        .AsNoTracking()
        .AsParallel()
        .Where(application =>
            application.Applicant.Id == userId &&
            GetApplicationStatedDates(application)
                .Any(date => date.Year == _dateTime.Now().Year && date < _dateTime.Now()));
}

In the where method, by stopping on a breakpoint, I can verify that StatedDates is empty.

Answer 1

You have to load the navigation property StatedDates explicitly like this:

private IEnumerable<Domain.Entities.Application> ApprovedApplicationsInCurrentYearToToday(string userId)
{
    return _context.Applications
        .Include(application => application.Applicant)
        .Include(application => application.StatedDates)
        .AsNoTracking()
        .AsParallel()
        .Where(application =>
            application.Applicant.Id == userId &&
            GetApplicationStatedDates(application)
                .Any(date => date.Year == _dateTime.Now().Year && date < _dateTime.Now()));
}