使用IQueryable做多表关联，如何将前置查询结果传入Join方法

两种场景的实现方案 #

场景1：4张表属于同一个DbContext，之前只是拆分写了分步查询 #

这种情况你之前写的分步IQueryable变量可以直接传入Join方法，不需要先执行查询，EF会自动把整段逻辑合并成单次SQL查询，不会阶梯查库。

// 前序查询都是IQueryable类型，不会立刻执行
IQueryable<Group> groups = dbContext.Groups.Where(g => g.IsValid).Select(g => g);
IQueryable<Mark> marks = dbContext.Marks;
IQueryable<ThirdTable> thirds = dbContext.ThirdTables;
IQueryable<FourthTable> fourths = dbContext.FourthTables;

// 连续Join关联
var result = groups
    .Join(
        inner: marks,
        outerKeySelector: g => g.Id,
        innerKeySelector: m => m.GroupId,
        resultSelector: (g, m) => new { Group = g, Mark = m }
    )
    .Join(
        inner: thirds,
        outerKeySelector: gm => gm.Mark.Id,
        innerKeySelector: t => t.MarkId,
        resultSelector: (gm, t) => new { gm.Group, gm.Mark, Third = t }
    )
    .Join(
        inner: fourths,
        outerKeySelector: gmt => gmt.Third.Id,
        innerKeySelector: f => f.ThirdId,
        resultSelector: (gmt, f) => new 
        {
            gmt.Group.Name,
            gmt.Mark.Score,
            gmt.Third.CreateTime,
            f.Remark
        }
    )
    .ToList(); // 整段逻辑最后才会生成SQL执行一次

场景2：4张表属于不同的DbContext（跨库/不同上下文实例） #

这种情况没法直接在IQueryable层面做SQL级别的Join，需要先把前序依赖的查询结果加载到内存，再用内存的Join方法处理：

// 第一步先执行前序查询，提前过滤、仅查需要的字段，减少内存占用
List<Group> groups = context1.Groups.Where(g => g.IsValid).ToList();
List<Mark> marks = context2.Marks.Where(m => groups.Select(g => g.Id).Contains(m.GroupId)).ToList();
List<ThirdTable> thirds = context3.ThirdTables.Where(t => marks.Select(m => m.Id).Contains(t.MarkId)).ToList();
List<FourthTable> fourths = context4.FourthTables.Where(f => thirds.Select(t => t.Id).Contains(f.ThirdId)).ToList();

// 内存中做连续Join
var result = groups
    .Join(
        marks,
        g => g.Id,
        m => m.GroupId,
        (g, m) => new { g, m }
    )
    .Join(
        thirds,
        gm => gm.m.Id,
        t => t.MarkId,
        (gm, t) => new { gm.g, gm.m, t }
    )
    .Join(
        fourths,
        gmt => gmt.t.Id,
        f => f.ThirdId,
        (gmt, f) => new 
        {
            gmt.g.Name,
            gmt.m.Score,
            gmt.t.CreateTime,
            f.Remark
        }
    )
    .ToList();

跨上下文查询时，前序查询尽量只返回后续关联需要的关键字段，不要加载全表数据，可大幅提升查询效率、降低内存消耗。

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