c# - EF 6 将 Where 子句添加到包含导航属性

Question

我将尝试在不发布模型中的所有对象的情况下提出这个问题。我有一个有点复杂的查询，但只有两个对象与该问题相关。

我有一个用于运行办公室足球池的网站。所以我的域模型有 Team 和 TeamRecords

这里是定义。我删除了对象上一些不相关的属性。

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; }

}

我像这样配置 Team 到 TeamRecords 的关系:

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

然后我尝试运行这样的查询。基本上当我选择一支球队时，我只想选择当前赛季的球队记录。所以我想在我的 Include 方法中添加一个 where 子句。忽略查询中的其他对象。它们可能不言自明。

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);

当我执行那行代码时，出现以下错误

包含路径表达式必须引用在类型上定义的导航属性。将点路径用于引用导航属性，将 Select 运算符用于集合导航属性。

我怎样才能完成这种类型的过滤？我已经在互联网上搜索过，但没有任何运气。

Answer 1

您可以将其更改为生成匿名类型的选择语句，然后执行查询并再次选择根对象。

你可以尝试这样的事情。

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();

并且他们会自动连接，Pick到Game，Game到HomeTeam，Game 到 VisitingTeam，HomeTeam 到 TeamRecords，VisitingTeam 到 TeamRecords，Pick 到 Pool。

也称为 relationship fix-up .