EF Core 8如何转换带固定art_group条件的SQL Server关联查询？

核心方案：手动显式关联替代导航属性 #

由于red_listing_items与all_article_groups之间未配置导航属性，无法依赖EF Core自动关联，必须手动编写Join/LeftJoin语句还原原T-SQL的多条件关联逻辑。

步骤1：还原原T-SQL的关联结构 #

假设你的原T-SQL逻辑类似（简化示例）：

SELECT rli.*, ag1.*, ag4.*, ag3.*, ag2.*, ag5.*
FROM red_listing_items rli
JOIN red_list_mappings rlm ON rli.Id = rlm.RedListingItemId
JOIN red_listing_headers rlh ON rli.RedListingHeaderId = rlh.Id
LEFT JOIN all_article_groups ag1 ON rli.ArticleId = ag1.ArticleId AND ag1.art_group = 1
LEFT JOIN all_article_groups ag4 ON rli.ArticleId = ag4.ArticleId AND ag4.art_group = 4
LEFT JOIN all_article_groups ag3 ON rli.ArticleId = ag3.ArticleId AND ag3.art_group = 3
LEFT JOIN all_article_groups ag2 ON rli.ArticleId = ag2.ArticleId AND ag2.art_group = 2
LEFT JOIN all_article_groups ag5 ON rli.ArticleId = ag5.ArticleId AND ag5.art_group = 5
WHERE ag1.Id = [指定固定值] -- 你的过滤条件

步骤2：编写对应的EF Core查询代码 #

使用GroupJoin+SelectMany+DefaultIfEmpty()模拟LEFT JOIN，同时提前筛选all_article_groups的固定art_group值：

var query = _dbContext.RedListingItems
    // 关联RedListMapping
    .Join(_dbContext.RedListMappings,
        rli => rli.Id,
        rlm => rlm.RedListingItemId,
        (rli, rlm) => new { rli, rlm })
    // 关联RedListingHeader
    .Join(_dbContext.RedListingHeaders,
        combined => combined.rli.RedListingHeaderId,
        rlh => rlh.Id,
        (combined, rlh) => new { combined.rli, combined.rlm, rlh })
    // 第一次关联all_article_groups（art_group=1）
    .GroupJoin(_dbContext.AllArticleGroups.Where(ag => ag.ArtGroup == 1),
        main => main.rli.ArticleId,
        ag => ag.ArticleId,
        (main, ags1) => new { main, ags1 = ags1.DefaultIfEmpty() })
    .SelectMany(x => x.ags1, (x, ag1) => new { x.main.rli, x.main.rlm, x.main.rlh, ag1 })
    // 第二次关联all_article_groups（art_group=4）
    .GroupJoin(_dbContext.AllArticleGroups.Where(ag => ag.ArtGroup == 4),
        main => main.rli.ArticleId,
        ag => ag.ArticleId,
        (main, ags4) => new { main, ags4 = ags4.DefaultIfEmpty() })
    .SelectMany(x => x.ags4, (x, ag4) => new { x.main.rli, x.main.rlm, x.main.rlh, x.main.ag1, ag4 })
    // 重复逻辑完成art_group=3/2/5的关联
    .GroupJoin(_dbContext.AllArticleGroups.Where(ag => ag.ArtGroup == 3),
        main => main.rli.ArticleId,
        ag => ag.ArticleId,
        (main, ags3) => new { main, ags3 = ags3.DefaultIfEmpty() })
    .SelectMany(x => x.ags3, (x, ag3) => new { x.main.rli, x.main.rlm, x.main.rlh, x.main.ag1, x.main.ag4, ag3 })
    .GroupJoin(_dbContext.AllArticleGroups.Where(ag => ag.ArtGroup == 2),
        main => main.rli.ArticleId,
        ag => ag.ArticleId,
        (main, ags2) => new { main, ags2 = ags2.DefaultIfEmpty() })
    .SelectMany(x => x.ags2, (x, ag2) => new { x.main.rli, x.main.rlm, x.main.rlh, x.main.ag1, x.main.ag4, x.main.ag3, ag2 })
    .GroupJoin(_dbContext.AllArticleGroups.Where(ag => ag.ArtGroup == 5),
        main => main.rli.ArticleId,
        ag => ag.ArticleId,
        (main, ags5) => new { main, ags5 = ags5.DefaultIfEmpty() })
    .SelectMany(x => x.ags5, (x, ag5) => new { x.main.rli, x.main.rlm, x.main.rlh, x.main.ag1, x.main.ag4, x.main.ag3, x.main.ag2, ag5 })
    // 添加你的过滤条件（比如ag1.Id等于固定值）
    .Where(result => result.ag1.Id == [你的固定值])
    // 用AutoMapper映射到目标DTO
    .ProjectTo<YourTargetDto>(_mapper.ConfigurationProvider);

步骤3：排查返回0行的问题 #

你之前添加过滤后返回0行，大概率是以下原因：

• 误用Join而非LeftJoin：原T-SQL如果是LEFT JOIN，用EF Core的Join会直接过滤掉无匹配的行，必须用GroupJoin+DefaultIfEmpty()模拟左连接
• 过滤条件逻辑错误：比如固定值与all_article_groups中对应art_group下的实际Id不匹配，建议先单独查询SELECT * FROM all_article_groups WHERE art_group = 1确认数据存在
• 过滤时机错误：如果把art_group的过滤放在最终Where而非关联前的AllArticleGroups.Where()，会导致关联后再过滤，丢失部分数据

步骤4：结合Repository的GetAllBy方法 #

可以将关联逻辑封装到Repository方法中，再传入过滤表达式：

// Repository中封装关联逻辑
public IQueryable<RedListingItemWithGroups> GetAllWithArticleGroups()
{
    return _dbContext.RedListingItems
        .Join(_dbContext.RedListMappings, rli => rli.Id, rlm => rlm.RedListingItemId, (rli, rlm) => new { rli, rlm })
        .Join(_dbContext.RedListingHeaders, c => c.rli.RedListingHeaderId, rlh => rlh.Id, (c, rlh) => new { c.rli, c.rlm, rlh })
        // 重复五次LeftJoin逻辑...
        .Select(result => new RedListingItemWithGroups
        {
            RedListingItem = result.rli,
            Ag1 = result.ag1,
            Ag4 = result.ag4,
            // 其他关联对象
        });
}

// 调用时添加过滤并映射
var data = _repository.GetAllWithArticleGroups()
    .Where(item => item.Ag1.Id == [你的固定值])
    .ProjectTo<YourTargetDto>(_mapper.ConfigurationProvider)
    .ToList();

关键验证技巧 #

用EF Core的ToQueryString()查看生成的SQL，对比原T-SQL是否一致，这是排查问题最直接的方式：

Console.WriteLine(query.ToQueryString());

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