EF 6 Add Where Clause To An Include With Navigation Property

Question

I'm going to try ask this question without posting all the objects in my model. I have a somewhat complex query but only two objects are related to the problem.

I have a website used to run office football pools. So my domain model has Team and TeamRecords

Here are the definitions. I removed some irrelevant properties on the object.

public class Team
{
    /// <summary>
    /// Team ID
    /// </summary>
    public int TeamID { get; set; }


    /// <summary>
    /// Team Recordproperty
    /// </summary>
    public virtual ICollection<TeamRecord> TeamRecords { get; set; }
}

public class TeamRecord
{

    /// <summary>
    /// Team ID
    /// </summary>
    public int TeamID { get; set; }

    /// <summary>
    /// Team the record belongs to
    /// </summary>
    public virtual Team Team { get; set;}

    /// <summary>
    /// Season ID
    /// </summary>
    public int SeasonID { get; set; }

    /// <summary>
    /// Season navigation property
    /// </summary>
    public virtual Season Season { get; set; }

}

I configure the Team to TeamRecords relationship like this:

HasMany(t => t.TeamRecords).WithRequired(tr => tr.Team).HasForeignKey(tr=>new {tr.TeamID});

Then I try to run a query like this. Basically when I select a team, I want to only select the teamrecord for the current season. So I want to add a where clause to my Include method. Ignore the other objects in the query. They're probably self explanatory.

var picks = context.Picks.Where(p => ((p.Game.SeasonID == seasonID) && (p.Game.Week ==     week) && (p.PoolID == poolID) && (p.UserID == userID)))
                            .Include(p => p.Game).Include(p => p.Game.HomeTeam).Include(p => p.Game.VisitingTeam).Include(p => p.Pool)
                            .Include(p => p.Game.HomeTeam.TeamRecords.Where(tr=>tr.SeasonID == seasonID))
                            .Include(p => p.Game.VisitingTeam.TeamRecords.Where(tr=>tr.SeasonID == seasonID))
                            .Select(p => p);

When I execute that line of code, I get the following error

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.

How can I accomplish this type of filtering? I've searched the internet and have not had any luck.

Answer 1

You can change it to a select statement that produces anonymous type, then execute the query and select the root object again.

You can try something like this.

var picks = context.Picks.Where(p => ((p.Game.SeasonID == seasonID) && (p.Game.Week == week) && (p.PoolID == poolID) && (p.UserID == userID)))
    .Select(p => new
    {
        Pick = p,
        Game = p.Game,
        HomeTeam = p.Game.HomeTeam,
        VisitingTeam = p.Game.VisitingTeam,
        HomeTeamRecords = p.Game.HomeTeam.TeamRecords.Where(tr => tr.SeasonID == seasonID),
        VisitingTeamRecords = p.Game.VisitingTeam.TeamRecords.Where(tr => tr.SeasonID == seasonID),
        Pool = p.Pool
    })
    .ToArray().Select(p => p.Pick).ToArray();

And they will automatically connected, Pick to Game, Game to HomeTeam, Game to VisitingTeam, HomeTeam to TeamRecords, VisitingTeam to TeamRecords, Pick to Pool.

Also known as relationship fix-up.

Answer 2

Entity Framework has no direct support for filtering the Include extension method.

A common alternative is to combine the Query and Load methods on a tracked entity to perform a filtered explicit load. (Search for Applying filters when explicitly loading related entities on that linked article.)

Taking just your Team and TeamRecords entities, this would look something like this:

// Ensure lazy loading is turned off
context.Configuration.LazyLoadingEnabled = false;

// load the team
var team = context.Teams.Find(teamId);

// explicitly load the team records for the current season
context.Entry(team)
    .Collection(t => t.TeamRecords)
    .Query()
    .Where(r => r.SeasonId == currentSeasonId)
    .Load();