如何编写EF查询，根据不同实体ID返回带导航属性的嵌套列表

我们可以分三步分别查询符合三类ID规则的数据，再手动合并嵌套实体，保证无重复同时满足导航属性加载要求，实现逻辑如下：

实现步骤 #

• 第一步：查询匹配TvShowIds的剧集，完整加载所有季和集

var tvShowsFromIds = new List<TvShow>();
if (TvShowIds?.Any() == true)
{
    tvShowsFromIds = await context.TvShows
        .Include(t => t.Seasons)
            .ThenInclude(s => s.Episodes)
        .Where(t => TvShowIds.Contains(t.Id))
        .ToListAsync();
}

• 第二步：查询匹配TvShowSeasonIds的季，加载所属剧集和该季下所有集

var seasonsFromIds = new List<Season>();
if (TvShowSeasonIds?.Any() == true)
{
    seasonsFromIds = await context.Seasons
        .Include(s => s.TvShow)
        .Include(s => s.Episodes)
        .Where(s => TvShowSeasonIds.Contains(s.Id))
        .ToListAsync();
}

• 第三步：查询匹配TvShowEpisodeIds的集，加载所属季和所属剧集

var episodesFromIds = new List<Episode>();
if (TvShowEpisodeIds?.Any() == true)
{
    episodesFromIds = await context.Episodes
        .Include(e => e.Season)
            .ThenInclude(s => s.TvShow)
        .Where(e => TvShowEpisodeIds.Contains(e.Id))
        .ToListAsync();
}

• 第四步：合并所有数据到无重复的TvShow集合

// 初始化结果字典，按TvShowId去重
var resultDict = tvShowsFromIds.ToDictionary(t => t.Id);

// 处理季查询结果
foreach (var season in seasonsFromIds)
{
    var tvShow = season.TvShow;
    // 剧集不在字典里就新增
    if (!resultDict.TryGetValue(tvShow.Id, out var existingTvShow))
    {
        existingTvShow = new TvShow
        {
            Id = tvShow.Id,
            Seasons = new List<Season>()
            // 可补充其他TvShow字段赋值
        };
        resultDict.Add(tvShow.Id, existingTvShow);
    }
    // 季不存在就加入剧集的季列表
    if (!existingTvShow.Seasons.Any(s => s.Id == season.Id))
    {
        existingTvShow.Seasons.Add(new Season
        {
            Id = season.Id,
            TvShowId = season.TvShowId,
            Episodes = season.Episodes.ToList()
            // 可补充其他Season字段赋值
        });
    }
}

// 处理集查询结果
foreach (var episode in episodesFromIds)
{
    var season = episode.Season;
    var tvShow = season.TvShow;
    // 先处理剧集
    if (!resultDict.TryGetValue(tvShow.Id, out var existingTvShow))
    {
        existingTvShow = new TvShow
        {
            Id = tvShow.Id,
            Seasons = new List<Season>()
            // 可补充其他TvShow字段赋值
        };
        resultDict.Add(tvShow.Id, existingTvShow);
    }
    // 再处理季
    var existingSeason = existingTvShow.Seasons.FirstOrDefault(s => s.Id == season.Id);
    if (existingSeason == null)
    {
        existingSeason = new Season
        {
            Id = season.Id,
            TvShowId = season.TvShowId,
            Episodes = new List<Episode>()
            // 可补充其他Season字段赋值
        };
        existingTvShow.Seasons.Add(existingSeason);
    }
    // 最后加集，避免重复
    if (!existingSeason.Episodes.Any(e => e.Id == episode.Id))
    {
        existingSeason.Episodes.Add(episode);
    }
}

// 得到最终结果
List<TvShow> finalResult = resultDict.Values.ToList();

注意事项 #

• 若实体包含除关联字段外的其他业务字段，可在新建TvShow/Season实例时补充对应字段的赋值逻辑
• 手动合并的方式不受EF上下文跟踪配置影响，兼容性更强；如果开启了全局跟踪，也可以直接复用查询返回的实体，EF会自动合并相同主键的实体
• 空ID集合的判断逻辑避免了空引用异常和无效SQL生成

内容的提问来源于stack exchange，提问作者Jason Landbridge