EF 6 Add where item to enable navigation property

Question

I will try to ask this question without publishing all the objects in my model. I have a somewhat complicated query, but only two objects are associated with the problem.

I have a website used to manage soccer pools in the office. Therefore my domain model has Team and TeamRecords

Here are the definitions. I removed some irrelevant properties of 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 set up Team to TeamRecords relationships as follows:

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

Then I try to run such a query. Basically, when I select a team, I only want to select the records command for the current season. So I want to add a where clause to my Include method. Ignore other objects in the request. They probably explain it themselves.

 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 this line of code, I get the following error

The Include path expression must specify the navigation property defined in the type. Use dashed paths for referenced navigation properties and the Select operator for navigation properties for the collection.

How can I perform this type of filtering? I searched on the internet and no luck.

Answer 1

You can change it to a select statement that creates an anonymous type, then run 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 connect to Pick from Game , Game from HomeTeam , Game to VisitingTeam , HomeTeam to TeamRecords , VisitingTeam to TeamRecords , Pick to Pool .

Also known as link fixes .

Answer 2

There is no direct support for filtering the Include extension method in the Entity Framework.

A common alternative is to combine the Query and Load methods on the tracked object to perform filtered explicit loading . (Look for filtering when explicitly loading related objects into this related article.)

Taking only your Team and TeamRecords , it looks 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();