使用GroupJoin实现LINQ左连接在FluentAPI中失效问题排查

正常运行的LINQ查询表达式 #

以下LINQ查询执行正常：

var topQuery1 = (
    from rov in context.Rocks
        .Where(x => eIds.Contains(x.RockId))

    join ro in context.Rocks
        .Where(x => x.Break == "OUT")
        .OrderByDescending(x => x.Timestamp)
        on
           new { rov.WorkerId, rov.Flatter } equals
           new { ro.WorkerId, ro.Flatter }
           into _rovOut

    from rovOut in _rovOut.DefaultIfEmpty()

    join ao in context.Workers()
        on rov.WorkerId equals ao.WorkerId
           into _profile

    from profile in _profile.DefaultIfEmpty()

    select new { rov, rovOut, profile }
);

转换为Fluent API后的报错代码 #

将上述查询转为Fluent API实现后未达到预期效果，代码如下：

var topQuery2 = context.Rocks.Where(x => eIds.Contains(x.RockId))
.GroupJoin(context.Workers(),
    r => r.WorkerId,
    p => p.WorkerId,
    (a, b) => new { profile = b.SingleOrDefault(), rov = a })
.GroupJoin(context.Rocks.Where(x => x.Break == "OUT").OrderByDescending(x => x.Timestamp),
    a => new { a.rov.WorkerId, a.rov.Flatter },
    b => new { b.WorkerId, b.Flatter },
    (a, b) => new { rovOut = b.SingleOrDefault(), rov = a.rov, profile = a.profile });

错误信息 #

执行时出现如下错误：

创建查询字符串时出错：LINQ表达式“DbSet()
.Where(x => __eIds_0
.Contains(x.RockId))
.GroupJoin(
inner: DbSet(),
outerKeySelector: r => r.WorkerId,
innerKeySelector: p => p.WorkerId,
resultSelector: (a, b) => new {
profile = b.SingleOrDefault(),
rov = a
})”无法被翻译。请将查询改写为可翻译的形式，或通过调用“AsEnumerable”、“AsAsyncEnumerable”、“ToList”或“ToListAsync”显式切换到客户端评估。

解决方法 #

最终通过使用自定义的LeftJoin扩展方法解决了问题。

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