AutoMapper bringing data out of nowhere after Entity Framework Query

Question

I'm using .NET Core 7 and Entity Framework Core 7.0.13 and AutoMapper 12.0.3 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

This happens because using ProjectTo is equivalent to applying custom Select to query in EF Core (i.e. custom projection), which results in Include's being ignored (including the filter). You can try to move the filtering logic into the mapping expression (maybe by using an intermediate type for this, according to the AutoMapper docs it should work) or apply mapping after materialization (as your working attempt does) but it can lead to fetching extra data.