将具有多个左外连接的SQL查询转换为LINQ查询

本文关键字：查询 SQL 转换 LINQ 连接 | 更新日期: 2023-09-27 17:50:52

将普通SQL查询转换为涉及多个LEFT OUTER join的LINQ查询…

原SQL查询:

SELECT a.*
FROM Testers t
LEFT OUTER JOIN Users u ON u.TesterId = t.TesterId
LEFT OUTER JOIN ValidForms v ON v.DepartmentId = u.DepartmentId

LINQ查询代码如下:

var x = (from t in Context.Testers.AsEnumerable()
         from u in Context.Users
                    .Where(a => a.TesterId == t.TesterId)
                    .DefaultIfEmpty()
                 from v in Context.ValidForms
                  .Where(b => b.DepartmentId == u.DepartmentId)
                    .DefaultIfEmpty()
                    Select new myEntity
                    {
                      col1 = t.col1,
                        col2 = t.col2
                    }).AsEnumerable()
return x.ToList();

运行查询，我得到一个错误:非静态方法需要一个目标

如果有人能指出如何在LINQ正确地做查询，我将不胜感激。

我也检查了这里发布的SO问题，但我无法掌握所提供的概念:SQL到Linq查询多个左外连接

谢谢。

更新:

将具有多个左外连接的SQL查询转换为LINQ查询

这是一个很好的方法。如果您遵循该示例，您的代码应该看起来像这样:

var x = (from t in Context.Testers.AsEnumerable()
         join u in Context.Users on t.TesterId equals u.TesterId into group1
         from a in group1.DefaultIfEmpty()
         join v in Context.ValidForms on a.DepartmentId equals v.DepartmentId into group2
         from b in group2.DefaultIfEmpty()
         select new MyEntity {
             col1 = b.col1,
             col2 = b.col2
         }).AsEnumerable();


var x = (from t in Context.Testers.AsEnumerable()
         join u in Context.Users on t.TesterId equals u.TesterId 
         join v in Context.ValidForms on u.DepartmentId equals v.DepartmentId into group1
         from b in group1.DefaultIfEmpty()
         select new MyEntity {
             col1 = (b != null) ? b.col1 : null,
             col2 = (b != null) ? b.col2 : null
         }).AsEnumerable();
相关文章：