EF 6在包含导航属性的Include中添加Where子句

本文关键字：Include 添加 Where 子句 属性 包含 导航 EF | 更新日期: 2023-09-27 18:17:56

我要试着问这个问题，而不张贴我的模型中的所有对象。我有一个有点复杂的查询，但只有两个对象与这个问题有关。

我有一个网站用来运行办公室足球赌注。所以我的领域模型有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});

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

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

当我执行那行代码时，我得到以下错误

Include路径表达式必须引用在该类型上定义的导航属性。引用导航属性使用点点路径，集合导航属性使用Select操作符。

我如何完成这种类型的过滤?我已经在网上搜索过了，但没有任何运气。

EF 6在包含导航属性的Include中添加Where子句

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

你可以试试这样做。

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。

又称关系修复

实体框架不直接支持过滤Include扩展方法。

一种常见的替代方法是在跟踪实体上组合Query和Load方法来执行过滤的显式加载。(搜索应用过滤器时显式加载相关实体链接的文章)

只取Team和TeamRecords实体，它看起来像这样:

// 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();
相关文章：