EF Loading Filtered Child Entries only

Question

Say i have typical students and courses classes, pulled as entities and auto generated via EF6. Example

class Student {
      string name,
      virtual ICollection<Course> Courses
      virtual ICollection<A> example a
      virtual ICollection<B> example b
      virtual ICollection<C> example c
      etc...
}

class Course {
      DateTime courseDate,
      virtual Student Student
      etc...
}

I want to be able to get a list of students who have a course tomorrow and just have that course in the child list. (Presume max 1 course per day)

Things i've tried....

List<Student> Method 1 (DateTime date)
{
    ctx.Configurations.LazyLoadingEnabled = false;
    return (from s in ctx.Students.Include(x=>x.Course)
                 join c in ctx.Courses
                 where c.courseDate == date
                 select s).ToList();
}

Result: List of students with course (but nothing in the courses object)

List<Student> Method 2 (DateTime date)
{
    return ctx.Students.Where(x=>x.Courses.Any(y=>y.courseDate==date)).ToList()
}

Result: List of Students with all properties attached and unfiltered.

List<Student> Method 3 (DateTime date)
{
    ctx.Configurations.LazyLoadingEnabled = false;
    return ctx.Students.Include(x=>x.Courses).Where(x=>x.Courses.Any(y=>y.courseDate==date)).ToList()
}

Result: List of Students with just courses property attached but STILL unfiltered.

    List<Student> Method 4 (DateTime date)
    {
        ctx.Configurations.LazyLoadingEnabled = false;
        return 
  ctx.Students.Include(x=>x.Courses).Where(x=>x.Courses.Select(y=>y.courseDate).Contains(date)).ToList()
    }

Result: Same as above

The List goes on....

I do not want the data to be pulled through and filtered after initial query.

Can anyone help.

I consider this a duplicate: https://stackoverflow.com/q/16798796/861716 — Gert Arnold, Jun 17 '20 at 12:51

score 0 · Answer 1 · answered Jun 17 '20 at 12:42

So you have Students and Courses. Every Student attends zero or more Courses. I would think that Courses are attended by zero or more Students (many-to-many). It seems to me that you designed that every course is attended by exactly one Student (one-to-many).

If you really intended one-to-many, your Course class should change slightly:

class Course
{
    public int Id {get; set;}
    public DateTime CourseDate {get; set;}
    ...

    public int StudentId {get; set;}   // <======= !!!
    public virtual Student Student {get; set;}
}

Back to your problem

You wrote:

I want to be able to get a list of students who have a course tomorrow and just have that course in the child list.

From your examples, it seems that you want all Students that have at least one Course tomorrow, together with all Courses that this Student has tomorrow.

Normally when using entity framework it would be enough to use the virtual ICollections like you did:

DateTime tomorrow = ...
var studentsWithCoursesOnDate = dbContext.Students.Select(student => new
{
    // Select only the properties that you plan to use:
    Id = student.Id,
    Name = student.Name,
    ... 

    CoursesOnDate = student.Courses
        .Where(course => course.CourseDate == tomorrow)
        .Select(course => new
        {
            // again: only the properties that you plan to use
            Id = course.Id,
            Title = course.Title,
            ...

            // not needed, you know the value
            // StudentId = course.StudentId,
        })
        .ToList(),
})
.ToList();

This should work with full entity framework. Entity framework knows the relations betweeen the tables and will perform the proper GroupJoin for you. I have heard that you can't use the virtual ICollections in EF-core, I haven't verified that.

If you want to do the GroupJoin yourseld:

DateTime tomorrow = ...
IQueryable<Course> tomorrowCourses = dbContext.Courses
    .Where(course => course.CourseDate == tomorrow);

var studentsWithCoursesOnDate = dbContext.Students
    .GroupJoin(tomorrowCourses,             // GroupJoin Students and tomorrow's Courses
    student => student.Id,                  // from every Student take the Id
    course => course.StudentId,             // from every Course take the foreign key

    (student, coursesOfThisStudent) => new  // when they match, make one new object
    {
         // again: select only the properties that you plan to use:
         Id = student.Id,
         Name = student.Name,
         ...

        CoursesOnDate = coursesOfThisStudent.Select(course => new
        {
            // again: only the properties that you plan to use
            Id = course.Id,
            Title = course.Title,
            ...
        })
        .ToList(),
})
.ToList();

Note: identifier coursesOfThisStudent actually contains only tomorrow's courses of this student, but that would make the identifier unnecessarily long.

I use Select, because Include fetches all columns of the matching data rows, inclusive the foreign keys and other data that you might not intend to use right now. Include is seldom efficient.

When querying data, always use Select to select the values that you want. Only use Include if you plan to change (update) the fetched data.

Hi, Thanks for the Answer... Student and Course were just psuedo classes for simplicity but i get the idea... unfortuntaly my VPN has died so i cant test till tomorrow.... It looks like it should work i'll comeback and report. Thanks again :D — user3652973, Jun 17 '20 at 14:49

EF Loading Filtered Child Entries only

1 Answers1

Back to your problem