Explicit Loading N:M with Filtering

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.

As funny as it may sound but it seems that it is by design)). If you look at EF source code you will see that Load method is kind of empty... https://github.com/aspnet/EntityFramework6/blob/master/src/EntityFramework/QueryableExtensions.cs — Yevgeniy.Chernobrivets, Aug 02 '16 at 18:45
@Yevgeniy.Chernobrivets That is interesting. I Don't think that is the release code because it works in just about every other situation. Even in this situation it is hitting the DB but it is just not populating back to the collection. — Matt Rowland, Aug 02 '16 at 18:47
Maybe this method should be empty because its task only to call enumeration which triggers db call. But if you do ToList instead of Load then it works as expected and returns list of items. — Yevgeniy.Chernobrivets, Aug 02 '16 at 18:52
Maybe that `.Query().Where(x => x.Status == "A").AsNoTracking().Load();` — Roman Marusyk, Aug 02 '16 at 19:37

Ivan Stoev · Accepted Answer · 2016-08-02T20:49:08.317

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();

Hi Ivan. This indeed loads the `Students` collections of the grades in the query. Note, however, that this *potentially* loads a massive amount of students. OP *might* be better off by simply including grades (of which I assume there are less than students) and filter them in memory. — Gert Arnold, Aug 02 '16 at 20:18
@GertArnold Hi, it indeed loads the related students (fortunately not cascading all their Grades). Another way was to use explicit link table with both navigation properties and explicit FK fields and 2 one-to-many relations, in which case the original code works and loads only the related Id fields. I'm not a fan at all of a filtered entity load tricks, posted this just because OP is asking for a way to do it. — Ivan Stoev, Aug 02 '16 at 20:31
Well, this may be a viable solution in many cases, just not here I guess. Don't tell anyone, but I don't even like many-to-many associations with a hidden junction class, *at all*. — Gert Arnold, Aug 02 '16 at 20:34
This actually works perfectly. It might load a lot of extra students by the single student that I am grabbing is not affected by that. I think I am still going to go with making the relationship use explicit entities. Thanks! — Matt Rowland, Aug 02 '16 at 20:36
You are welcome. And nice choice! Updated the answer with that option. Actually I've tried the both before posting the initial answer, and for some reason decided that you want `Grade` objects, so removed it from the answer. — Ivan Stoev, Aug 02 '16 at 20:56

George Vovos · Answer 2 · 2016-08-02T19:03:13.840

1

That's expected
The first Load() is DbCollectionEntry.Load()
The second is IQueryable.Load()

Basically you call Load on an empty IQueryable

Try

var grades = context.Students.Where(s => s.StudentId == 1).SelectMany(s => s.Grades).Where(g => g.Status == "A").ToList();




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 (N'A' = [Extent2].[Status]) AND (1 = [Extent1].[StudentId])

edited Aug 02 '16 at 19:03

answered Aug 02 '16 at 18:55

George Vovos

7,563
2
22
45

So how do I make it work? – Matt Rowland Aug 02 '16 at 18:57
But that way was described on [MSDN](https://msdn.microsoft.com/en-us/data/jj574232.aspx). It should work – Roman Marusyk Aug 02 '16 at 18:59
I am not selecting the grades. I need them loaded into the student's `Grade` collection. – Matt Rowland Aug 02 '16 at 19:10
Student.Grades=grades; ? – George Vovos Aug 02 '16 at 19:12
This causes a duplicate primary key violation upon `.SaveChanges()`. t is trying to insert them into the linking table a second time because EF sees it as a new relation after the `Student.Grades=grades;` – Matt Rowland Aug 02 '16 at 19:18
@MattRowland check this http://stackoverflow.com/questions/7079378/how-to-filter-nested-collection-entity-framework-objects – George Vovos Aug 02 '16 at 19:19
@MattRowland You can explicitly set the state of the Grades to Unchanged – George Vovos Aug 02 '16 at 19:20
@MattRowland What exactly are you trying to do? Do you need to edit you student?Add/Edit grades? – George Vovos Aug 02 '16 at 19:23
It seems like there isn't a clean way of doing it. I'll give you an upvote for now. I wait out the bounty to see if someone comes back with a cleaner way to handle this. – Matt Rowland Aug 02 '16 at 19:24
No there isn't a clean way for that,Update your question with what you really want to accomplish and we'll find a nice solution for that – George Vovos Aug 02 '16 at 19:26
3

I don't want to just update the question because the solution is not possible. That changes the entire question. If This question doesn't get an answer that is sufficient I will open a new question with the different criteria. – Matt Rowland Aug 02 '16 at 19:39

Explicit Loading N:M with Filtering

2 Answers2