LINQ: How to introduce where in Include?

Question

I have this query that I am using to get students, I also want to include the current course in which student is studying. How to change this query to make it work?

var students = Context.Students
                      .Where(x=>x.Race == "SomeRace")
                      .Include(x=> x.StudentCourse.FirstOrDefault(x=>x.Status == CourseStatus.Current))

This query will through an error, since EF does not allow to put where condition in the Include. Any turn around? Any other way to write this query?

Update: If I use the solution proposed by @CoryNelson which is something like this:

studentResult = Context.Students
.Where(x=>x.Race == "SomeRace")
.Select(x=>new
{
    Student = x,
    CurrentCourse = x.StudentCourse
        .FirstOrDefault(y=>y.Status == CourseStatus.Current)
};

The studentResult I get is not a list, like if I try to do

foreach( student in studentResult) 
{ 
   finalResult.Add( 
   new StudentDto
  { 
      FirstName = student.Firstname // I do not get student first name, intellisense is not letting me select FirstName, which means studentResult is not a collection? 
   } 
}

Filtered `Include` is a recurring issue. It's just not possible. As for your last effort, try `student.Student.Firstname`. You're looping through anonymous types from the previous statement. — Gert Arnold, Jul 16 '15 at 20:10

Evan Frisch · Accepted Answer · 2015-07-16T20:11:54.797

3

You are using Include wrong. Include is used to force eager loading on a entity that is connected to the entities you are bringing back. Look here for more information on Include.

What you are trying to do should be in the where statement along with the other selector criteria.

Updated

Then you do want to use Cory Nelson's LINQ query.

var students = Context.Students
                      .Where(x => x.Race == "SomeRace")
                      .Select(x => new
                      {
                          Student = x,
                          CurrentCouse = x.StudentCourse.FirstOrDefault(y => y.Status == CourseStatus.Current)
                      });

Then in order to access them you do something like:

foreach(var s in students)
{
    finalResult.Add(new StudentDto { FirstName = s.Student.Firstname } );
}

The use of new in the Select creates an anonymous type. By saying Student = x you are saying the anonymous type has a field called Student which will have the fields of the orginal variable x which was of the Student class. The article provided has more details on anonymous types but that is all you should need to access the fields.

edited Jul 16 '15 at 20:11

answered Jul 16 '15 at 19:02

Evan Frisch

1,334
5
22
40

I can not use this, I want all paitents with some race and the current course. It might filter the results and not return students if the student does not has any current course. – Obviously Jul 16 '15 at 19:16
@Obviously Just to clarify before I update anything, you want all students with "SomeRace" and any course that is current? Not a specific current course but any current course? – Evan Frisch Jul 16 '15 at 19:54
Ideally there will be just one student course that is current, so i can select FirstOrDefault() – Obviously Jul 16 '15 at 19:56
Yes that was the issue, now its working – Obviously Jul 16 '15 at 20:29

score 1 · Answer 2 · answered Jul 16 '15 at 18:59

1

Include brings in all or none of a navigation property. You might want to use a projection instead:

Context.Students
    .Where(x=>x.Race == "SomeRace")
    .Select(x=>new
    {
        Student = x,
        CurrentCourse = x.StudentCourse
            .FirstOrDefault(y=>y.Status == CourseStatus.Current)
    };

answered Jul 16 '15 at 18:59

Cory Nelson

29,236
5
72
110

This should work, but when I do this and try to populate the result(which should be collection) I do not get intellesense. Why is that? – Obviously Jul 16 '15 at 19:17
foreach( student in result) { new StudentDto{ FirstName = student.Firstname // I do not get student, intellisense is not letting me select FirstName } } – Obviously Jul 16 '15 at 19:17

LINQ: How to introduce where in Include?

2 Answers2