使用GroupJoin的Linq左连接在fluentAPI中不起作用

vpfxa7rd  于 2023-11-14  发布在  其他
关注(0)|答案(1)|浏览(159)

我的查询表达式在执行时工作正常。

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

字符串
我把上面的代码翻译成这个fluentAPI,并没有像预期的那样工作。

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()。其中(x => __eIds_0 .Contains(x.RockId)).GroupJoin(inner:DbSet(),outerKeySet:r => r.WorkerId,innerKeySet:p => p.WorkerId,resultValue:无法翻译(a,B)=> new { profile = B.SingleOrDefault(),rov = a })“。请以可翻译的形式重写查询,或通过插入对“AsEnumerable”、“AsAsAsyncEnumerable”、“ToList”或“ToListAsync”的调用来显式切换到客户端计算。有关详细信息,请参阅https://go.microsoft.com/fwlink/?linkid=2101038
[编辑]**
我最终从这个URL https://velog.io/@prediction/EF-CORE-Left-Join-Extension使用LeftJoin,现在一切都正常。

wwwo4jvm

wwwo4jvm1#

我认为问题是在eIds字段中,在哪里以及如何计算它?(如果你发布它,我会回顾这个答案)
common problem with lists and contains in linq是这样的:

var eIds = 
        myContext.SomeTable
        .Select(x=>x.id)
        
var otherQuery = 
        myContext.SomeTable2
        .Where(x=>eIds.contains(x.Id2))
        .ToList()

字符串
这会在Linq to SQL中抛出一个错误,因为在第二个查询中无法将id转换为SQL。
在使用eIds字段之前使用.ToList()进行解析,如下所示:

var eIds = 
        myContext.SomeTable
        .Select(x=>x.id)
        .ToList()

        
var otherQuery = 
        myContext.SomeTable2
        .Where(x=>eIds.contains(x.Id2))
        .ToList()

相关问题