.net 在左联接中具有子查询的LINQ查询出错

xtupzzrd 于 2023-01-06 发布在 .NET

关注(0)|答案(1)|浏览(183)

我试着用LINQ在左连接中做一个子查询，在SQL中看起来像这样：

SELECT fields
FROM table1 A
LEFT JOIN table2 B ON B.Establishment = A.Establishment  
LEFT JOIN table3 C ON C.Vdb = B.Vdb AND C.Year = (SELECT MAX(Year) FROM table3 D WHERE D.Vdb = C.Vdb)

使用LINQ，我做了以下工作：

var query = await (
    from a in _context.Table1
    
    join b in _context.Table2 on a.Establishment equals b.Establishment
    
    join c0 in _context.Table3 on b.Vdb equals c0.Vdb into gGroup
    from c in gGroup.Where(x => x.Year == (from c1 in _context.Table3
                                            where c1.Vdb == x.Vdb
                                            select c1.Year).Max()).DefaultIfEmpty()
                                            
    select new
    {
        fields
    })
    .ToListAsync();

我使用LINQPad构建了这段代码，所以我尝试在那里运行，一切都很顺利，但当我将这段代码放入IDE并尝试运行它时，我得到了以下错误：

{
  "Message": "The LINQ expression (expression here) could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.",
  "ErrorName": "InvalidOperationException"
}

我不知道到底出了什么问题所以我可以解决这个问题，有人能帮我吗？

.net

来源：https://stackoverflow.com/questions/75018632/error-on-linq-query-with-sub-query-in-left-join

1条答案

按热度按时间

o7jaxewo1#

GroupJoin几乎不可由EF Core翻译。如果不执行简单的LEFT JOIN，请不要使用此操作符。
我重写了query，使用另一种技术，它返回相同的结果：

var table3 = _context.Table3;

var latest = 
    from t in table3.Select(t => new { t.Vdb }).Distinct()
    from r in table3
      .Where(r => r.Vdb == t.Vdb)
      .OrderByDescending(r.Year)
      .Take(1)
    select r;

var query = 
    from a in _context.Table1
    join b in _context.Table2 on a.Establishment equals b.Establishment into gb
    from b in gb.DefaultIfEmpty()
    join c in latest on b.Vdb equals c.Vdb into gc
    from c in gc.DefaultIfEmpty()
    select new
    {
        // fields
    };

赞(0）回复(0）举报 2023-01-06

我来回答

.net 在左联接中具有子查询的LINQ查询出错

1条答案

相关问题

热门标签

最新问答