Entity Framework Core query only work with .ToList()

Question

I'm using .NET Core 7 and Entity Framework Core 7.0.13 and I have the following query:

var test = _context.Set<T>
    .AsNoTracking()
    .Include(m => m.MachineOperations!
        .Where(m =>
            m.InsertedAt.Date <= endDate.Date &&
            m.EndTime <= endDate.TimeOfDay &&
            m.InsertedAt.Date >= startDate.Date &&
            m.StartTime >= startDate.TimeOfDay))
    .ThenInclude(m => m.EggQuantities)
    .Where(m =>
        diffDays.Contains(m.WeekDay) &&
        m.MachineOperations!.Any() &&
        m.InitialProductionTime <= startDate.TimeOfDay &&
        m.FinalProductionTime >= startDate.TimeOfDay);

var test2 = _mapper.ProjectTo<MachineScheduleDataDto>(test);

My problem is that the .Where clause is not being applied. After some tries, I realized that if I add a .ToList() at the end of the query and after I convert it using .AsQueryable() to pass as an argument to the mapper, the .Where clause works fine. Why does it happen? I can't use .ToList() because it selects all the properties of the classes that I am querying.

After many tries, I realized that my query was ok, my problem is inside of the .ProjectTo(). For example, if I pass some argumentes that shouldn't return any MachineOperation, the query doesn't actually return anything, which is ok, but when the result of the query pass through the .ProjectTo, it magically finds some records and brings it to me. It really doesn't make any sense to me.

Result of the query:

Result of the .ProjectTo():

MachineScheduleDataDto class:

public record MachineScheduleDataDto
{
    public required ICollection<MachineOperationDto> MachineOperationsDto { get; set; }
}

public record MachineOperationDto
{
    public int MachineOperationId { get; set; }
    public EMachineStatus MachineStatus { get; set; }

    public virtual required MachineScheduleDataDto MachineScheduleDto { get; set; }
}

My Mappers:

CreateMap<MachineSchedule, MachineScheduleDataDto>()
    .ForMember(p => p.MachineOperationsDto, opts => opts.MapFrom(m => m.MachineOperations));

CreateMap<MachineOperation, MachineOperationDto>()
   .ForMember(p => p.MachineScheduleDto, opts => opts.MapFrom(m => m.MachineSchedule));

Answer 1

Given this is Automapper, though I am not familiar with that flavour of ProjectTo, the issue is that you are mixing eager loading /w filtering with IQueryable projection. Projection /w ProjectTo does not require, and likely ignores eager loading including filtering. If you want to filter the projections, do it via the mapping configuration. So if you have a MachineScheduleDataDto which contains MachineOperationDtos, your mapping for building a MachineScheduleDataDto should include the rules about what MachineOperations to include. I.e:

var configuration = new AutomapperConfiguration(cfg =>
{
    cfg.CreateMap<MachineScheduleData, MachineScheduleDataDto>()
        .ForMember(x => x.MachineOperationsDtos, 
            opt => opt.MapFrom(src => src.MachineOperations
               .Where(m =>
                  m.InsertedAt.Date <= endDate.Date &&
                  m.EndTime <= endDate.TimeOfDay &&
                  m.InsertedAt.Date >= startDate.Date &&
                  m.StartTime >= startDate.TimeOfDay)));
});

The configuration will obviously have the mappings for the MachineOperation -> MachineOperationDto etc. as well and will use those mappings to map the resulting filtered machine operations into their DTOs. Then your resulting query would look like:

var query= _context.Set<MachineScheduleDatas>
    .Where(m =>
        diffDays.Contains(m.WeekDay) &&
        m.MachineOperations!.Any() &&
        m.InitialProductionTime <= startDate.TimeOfDay &&
        m.FinalProductionTime >= startDate.TimeOfDay);

var dtos = await_mapper.ProjectTo<MachineScheduleDataDto>(query)
    .ToListAsync();

or what I'm more familiar with the ProjectTo extension method:

var dtops = await _context.Set<MachineScheduleDatas>
    .Where(m =>
        diffDays.Contains(m.WeekDay) &&
        m.MachineOperations!.Any() &&
        m.InitialProductionTime <= startDate.TimeOfDay &&
        m.FinalProductionTime >= startDate.TimeOfDay)
    .ProjectTo<MachineScheduleDataDto>(configuration)
    .ToListAsync();