Explicit Loading N:M with Filtering [duplicate]

Question

I am trying to filter the results from an explicit load in EntityFramework.

The explicit loading works when I do not apply any filter but it does not load results once the filter is applied.

Classes

public partial class Student
{
    public int StudentId { get; set; }
    public int CourseId { get; set; }
    public string Name { get; set; }
    public string Status { get; set; }
    public virtual ICollection<Grade> Grades { get; set; }
}

public partial class Grade
{
    public int GradeId { get; set; }
    public string Value { get; set; }
    public string Status { get; set; }
    public virtual ICollection<Student> Students { get; set; }
}

Fluent API Mapping

modelBuilder.Entity<Grade>()
    .HasMany(e => e.Students)
    .WithMany(x => x.Grades)
    .Map(m => m.ToTable("StudentGrades").MapLeftKey("GradeId").MapRightKey("StudentId"));

Usage

This works and populates the student.Grades property.

using (var context = new Model1())
{
    context.Configuration.LazyLoadingEnabled = false;

    var student = context.Students.Single(x => x.StudentId == 1);
    context.Entry(student).Collection(x => x.Grades).Load();
}

The SQL that is generated looks like this:

SELECT 
[Extent2].[GradeId] AS [GradeId], 
[Extent2].[Value] AS [Value], 
[Extent2].[Status] AS [Status]
FROM  [dbo].[StudentGrades] AS [Extent1]
INNER JOIN [dbo].[Grades] AS [Extent2] ON [Extent1].[GradeId] = [Extent2].[GradeId]
WHERE [Extent1].[StudentId] = 1 // this is parameterized in the actual hit.

When I run this query I get the full results.

However, when I apply filtering and use the following line, it does not populate student.Grades.

context.Entry(student).Collection(x => x.Grades).Query().Where(x => x.Status == "A").Load();

This line generates this query:

SELECT 
[Extent2].[GradeId] AS [GradeId], 
[Extent2].[Value] AS [Value], 
[Extent2].[Status] AS [Status]
FROM  [dbo].[StudentGrades] AS [Extent1]
INNER JOIN [dbo].[Grades] AS [Extent2] ON [Extent1].[GradeId] = [Extent2].[GradeId]
WHERE ([Extent1].[StudentId] = 1) AND ('A' = [Extent2].[Status])
//the "1" is parameterized in the actual hit.

When I run this manually against the DB I get the correctly filtered results within SQL Server. The problem is that this doesn't populate student.Grades in the C# object.

Answer 1

This technique is mentioned in the MSDN Article - Applying filters when explicitly loading related entities section, so it's supposed to be supported and working. Strangely enough, it's working for one-to-many relationship, many-to-many with explicit link table and 2 one-to-many associations, but not for many-to-many with implicit link table.

I have no explanation why is that (didn't find related documentation). I also have no explanation why, but combining it with a request for eagerly loading the other collection does the trick:

context.Entry(student).Collection(s => s.Grades)
    .Query().Where(g => g.Status == "A")
    .Include(g => g.Students)
    .Load();

The drawback of this (as mentioned in the comments) is that it would also load a lot of students that belong to the loaded grades.

So the better way would be to use the explicit link table and relationships like this:

Model:

public partial class Student
{
    public int StudentId { get; set; }
    public int CourseId { get; set; }
    public string Name { get; set; }
    public string Status { get; set; }
    public virtual ICollection<StudentGrade> StudentGrades { get; set; }
}

public partial class Grade
{
    public int GradeId { get; set; }
    public string Value { get; set; }
    public string Status { get; set; }
    public virtual ICollection<StudentGrade> StudentGrades { get; set; }
}

public class StudentGrade
{
    public int StudentId { get; set; }
    public int GradeId { get; set; }
    public virtual Student Student { get; set; }
    public virtual Grade Grade { get; set; }
}

Configuration:

modelBuilder.Entity<StudentGrade>()
   .ToTable("StudentGrades")
   .HasKey(e => new { e.GradeId, e.StudentId });

modelBuilder.Entity<StudentGrade>()
    .HasRequired(e => e.Grade)
    .WithMany(x => x.StudentGrades)
    .HasForeignKey(e => e.GradeId)
    .WillCascadeOnDelete();

modelBuilder.Entity<StudentGrade>()
    .HasRequired(e => e.Student)
    .WithMany(x => x.StudentGrades)
    .HasForeignKey(e => e.StudentId)
    .WillCascadeOnDelete();

Now the explicit loading does not require tricks and will load the filtered related StudentGrade entities with only GradeId and StudentId fields populated, thus avoiding loading the additional Grade and Student objects:

context.Entry(student).Collection(s => s.StudentGrades)
    .Query().Where(sg => sg.Grade.Status == "A")
    .Load();