LINQ complex query navigation property

Question

I have a problem here with getting data from my database with LINQ

I have two tables Team and TeamMember, which are related by 1-N relationship. I am using Entity Framework and I have one entity for each of the tables with a property for each column. Also in the Team entity there is a TeamMember navigation property as result of this relationship.

I want to do a query where I can get all my Team's with their Team Members.

result = (from t in this.context.Teams
          orderby t.Name
          select t)
         .Include("TeamMembers")

That works fine. I get a collection of Team Entities with the Team.TeamMember property populated with the data of the member of each team.

The problem is when I want to do a more complex query like filtering the query for the TeamMembers.

For example, both tables have a column EndDateTime. If I want to get all the team and team members which are not ended (their end date time is not null) I don't know how to do it.

With this query I will filter just the teams, but not the team members.

result = (from t in this.context.Teams
          where t.EndDateTime == null
          orderby t.Name
          select t)
         .Include("TeamMembers")
         .ToList();

Any idea?

I kind of "solve" it doing the filter of the member after the query, to the collection. Like this:

//Filter out the End dated care coordiantors
var careCoordinatorsToDelete = new List<CareCoordinator>();
foreach (var team in result)
{
    careCoordinatorsToDelete.Clear();

    foreach (var careCoordinator in team.CareCoordinators)
    {
        if (careCoordinator.EndDateTime != null)
            careCoordinatorsToDelete.Add(careCoordinator);
    }

    foreach (var toDelete in careCoordinatorsToDelete)
    {
        team.CareCoordinators.Remove(toDelete);
    }
}

But I don't think this is a good solution at all.

Answer 1

As I've pointed out, I think this is a duplicate. But summarising the answers, you simply need to include the Where clause on the child as part of the Select statement (by using it as part of an anonymous type), enumerate the query, and then retrieve the objects you want.

Because you've selected the TeamMembers that you want into another property they will be retrieved from the database and constructed in your object graph.

result = (from t in this.context.Teams
          where t.EndDateTime == null
          orderby t.Name
          select new 
          { 
              Team = t,
              Members = t.TeamMembers.Where(tm => tm.EndDateTime == null)
          })
         .ToList()
         .Select(anon => anon.Team)
         .ToList();