Entity Framework select distinct values from one to many table with filter

Question

I'm trying to write a query in EF.

Consider this relationship:

The goal is to get the teachers with the full collection of students, who are active between a certain filtered period (from-to).

I wrote the following query:

var filtered = _context.Teachers
                       .Join(_context.Students, // Target
                             fk => fk.Id, // FK
                             pk => pk.teacherId, // PK
                             (fk, pk) => new { teach = fk, students = pk }
                            )
                       .Where(i => i.students.date_active >= from &&
                                   i.students.date_active <= to)
                       .OrderBy(i => i.teach.name)
                       .Select(i => i.teach)
                       .Include(i => i.Students)
                       .AsNoTracking();

With this query, I get duplicate teachers. So I'll just add the Distinct() operator and I have the teachers. But then my teacher object still contains all the students. I want only the students for that period. Any help on how to get the good result?

List<Dto.Teacher> list = filtered.Select(i => Dto.Teacher
                                              {
                                                  id = i.Id,
                                                  name = i.name
                                                  Students = i.Students.Select(j => new Dto.Student
                    {
                        id = i.id,
                        date_active = i.date_active,
                    }).ToList(),
                }).ToList();

public class Teacher() 
{ 
    public int id { get; set; }
    public string name { get; set; }

    public List<Dto.Student> Students { get; set; }
}

Answer 1

when using an ORM such as EF the join operator should be avoided as often as possible.

In your case you may try something like the following (variation are possible):

_context.Teachers.
    Where(t => t.Sudents.Any(s => s.date_active >= from &&
        s.date_active <= to)
    ).
    Select(t => new {
        teacher = t,
        activeStudents = t.Students.Where(s => s.date_active >= from &&
        s.date_active <= to)
    });