使用 Linq 的多个外部联接，其中 2 个联接到同一个表/对象.得到了 SQL，需要 Linq to Entity

本文关键字：Linq 对象 SQL to Entity 需要 外部 其中 同一个 使用 | 更新日期: 2023-09-27 18:31:07

我正在尝试在Linq中重现以下SQL查询，需要一些帮助。

select
   dbo.Documents.DocId, 
   dbo.Documents.ReykerAccountRef, 
   dbo.Documents.ReykerClientId DocClientID,
   CAAs.ClientId CAAClientIDCheck,
   ClientData.FullName ClientFullName, 
   CAAs.IFAId,  
   AdvisorData.FullName AdvisorFullName
from dbo.Documents
left join 
  dbo.CAAs on dbo.Documents.ReykerAccountRef = dbo.CAAs.AccountRef
left join 
  dbo.hmsProfileDatas AS ClientData
on 
  dbo.CAAs.ClientId = ClientData.ReykerClientID
left join 
  dbo.hmsProfileDatas AS AdvisorData
on 
  dbo.CAAs.IFAId = AdvisorData.ReykerClientID

我正在尝试两次链接到同一个表，一次是客户全名，另一次是顾问全名。

我想在 linq 中生成的基本 sql 是

select table1.*,table2.*,a.Fullname, b.Fullname
from table1
left join
   table2 on table1.t2Id = table2.Id
left join
   table3 AS a 
on 
   table2.t3Id1 = table3.id1
left join
   table3 AS b 
on 
   table2.t3Id2 = table3.id2
因此，表 1 连接到表 2，表 2 具有 2 个外键（t3Id1 和

t3Id2）到 table3 到不同的字段（id1 和 id2）。

这是我尝试遵循一些指导的方法，但它没有返回任何内容！出了什么问题？

        var results3 = from doc in DataContext.Documents
                       from caa
                           in DataContext.CAAs
                           .Where(c => c.AccountRef == doc.ReykerAccountRef)
                           .DefaultIfEmpty()
                       from cpd
                           in DataContext.hmsProfileDatas
                           .Where(pdc => pdc.ReykerClientID == caa.ClientId)
                           .DefaultIfEmpty()
                       from apd
                           in DataContext.hmsProfileDatas
                           .Where(pda => pda.ReykerClientID == caa.IFAId)
                           .DefaultIfEmpty()
                       select new DocumentInList()
                                  {
                                      DocId = doc.DocId,
                                      DocTitle = doc.DocTitle,
                                      ReykerDocumentRef = doc.ReykerDocumentRef,
                                      ReykerAccountRef = doc.ReykerAccountRef,
                                      ClientFullName = cpd.FullName,
                                      AdvisorFullName = apd.FullName,
                                      DocTypeId = doc.DocTypeId,
                                      DocTypes = doc.DocTypes,
                                      DocDate = doc.DocDate,
                                      BlobDocName = doc.BlobDocName,
                                      UploadDate = doc.UploadDate,
                                  };

使用 Linq 的多个外部联接，其中 2 个联接到同一个表/对象.得到了 SQL，需要 Linq to Entity

我希望我正确理解了你的例子。这是另一个简单的例子，它应该提供你需要的东西：

    private class User
    {
        public int UserId;
        public string Name;
        public int GroupId;
        public int CollectionId;
    }
    public class Group
    {
        public int GroupId;
        public string Name;
    }
    public class Collection
    {
        public int CollectionId;
        public string Name;
    }
    static void Main()
    {
        var groups = new[] { 
            new Group { GroupId = 1, Name = "Members" },
            new Group { GroupId = 2, Name = "Administrators" } 
        };
        var collections = new[] { 
            new Collection { CollectionId = 1, Name = "Teenagers" },
            new Collection { CollectionId = 2, Name = "Seniors" } 
        };
        var users = new[] { 
            new User { UserId = 1, Name = "Ivan", GroupId = 1, CollectionId = 1 },
            new User { UserId = 2, Name = "Peter", GroupId = 1, CollectionId = 2 },
            new User { UserId = 3, Name = "Stan", GroupId = 2, CollectionId = 1 },
            new User { UserId = 4, Name = "Dan", GroupId = 2, CollectionId = 2 },
            new User { UserId = 5, Name = "Vlad", GroupId = 5, CollectionId = 2 },
            new User { UserId = 6, Name = "Greg", GroupId = 2, CollectionId = 4 },
            new User { UserId = 6, Name = "Arni", GroupId = 3, CollectionId = 3 },
        };
        var results = from u in users
                      join g in groups on u.GroupId equals g.GroupId into ug
                      from g in ug.DefaultIfEmpty()
                      join c in collections on u.CollectionId equals c.CollectionId into uc
                      from c in uc.DefaultIfEmpty()
                      select new { 
                          UserName = u.Name, 
                          GroupName = g != null ? g.Name : "<No group>",
                          CollectionName = c != null ? c.Name : "<No collection>"
                      };
    }

它在一个表上生成两个连接，以从其他两个表获取数据。下面是输出：

Ivan    Members         Teenagers
Peter   Members         Seniors
Stan    Administrators  Teenagers
Dan     Administrators  Seniors
Vlad    <No group>      Seniors
Greg    Administrators  <No collection>
Arni    <No group>      <No collection>
相关文章：