Can you filter an Entity Framework DBSet by a value in the many part of a one-to-many relationship?-CodePudding

I am trying to filter a DbSet by a value that is in the many part of a one to many relationship. For example, I need to get the entire list of students. Each student can have many classes. I need to filter students based off of if they have a specific class.

In the context file I have;

modelBuilder.Entity<Student>()
            .HasMany(e => e.Classes)
            .WithOne(x => x.Student)
            .HasForeignKey(m => m.StudentId);

In the service file, I might filter student by an attribute such as gender which is why I am using an IQueryable<Student>:

IQueryable<Student> studentQuery = _context.Students;
studentQuery = studentQuery.Where(x => x.Gender == "MALE");
var students = await studentQuery
                         .Include(c => c.Classes.Where(c => c.className == "GYM"))
                         .ToListAsync();

What is happening is that when I try to filter the classes, it returns all students but only lists the class gym. What I need is a list of all of the students where one of there classes is GYM.

The eventual output is for a Web API. I hope that I explained this well enough. Thank you for your help.

CodePudding user response：

If you want students where any of their classes is "GYM":

var students = await studentQuery
        .Include(c => c.Classes)
        .Where(s => s.Classes.Any(c => c.className == "GYM")))
        .ToListAsync();