c# - 在多对多表上向LEFT LINQ TO SQL C#JOIN

嗨，我对linq to sql有点陌生，我了解一些基础知识。问题是我想在查询中进行左联接。查询中有3个表。


  
  索赔者（所有行应从该表返回）
  要求
  用户
  


该查询应返回所有拥有索赔者的用户。这是通过多对多表Claim来完成的。但是无论用户如何，都应返回所有索赔人。因此，左派加入了索赔人。

我有以下查询

    var d = (from Claimants in DB.Claimants
                 join Claims in DB.Claims on Claimants.Claiment_ID equals Claims.Claiment_ID
                 join Users in DB.Users on Claims.User_ID equals Users.User_ID
                 where (Claimants.TrialDate.Value >= dtDayStart & Claimants.TrialDate <= dtDayEnd)
                 select new
                 {
                     ClaimantFirstName = Claimants.FirstName,
                     ClaimantLasname = Claimants.LastName,
                     ClaimantsID = Claimants.IDNumber,
                     Claimants.OurReference,
                     Claimants.TrialDate,
                     InterviewStart = Claims.DateTimeStart,
                     InterviewEnd = Claims.DateTimeEnd,
                     Claims.Priority,
                     UserFirstname = Users.FirstName,
                     UserLastName = Users.LastName,
                     UserID = Users.IDNumber
                 });

我尝试使用如下的in语句，但是没有运气

        var d = (from Claimants in DB.Claimants
                 join Claims in DB.Claims on Claimants.Claiment_ID equals Claims.Claiment_ID
                 into TheClaimants
                 from Claims in TheClaimants.DefaultIfEmpty()

                 join Users in DB.Users on Claims.User_ID equals Users.User_ID
                 where (Claimants.TrialDate.Value >= dtDayStart & Claimants.TrialDate <= dtDayEnd)
                 select new
                 {
                     ClaimantFirstName = Claimants.FirstName,
                     ClaimantLasname = Claimants.LastName,
                     ClaimantsID = Claimants.IDNumber,
                     Claimants.OurReference,
                     Claimants.TrialDate,
                     InterviewStart = Claims.DateTimeStart,
                     InterviewEnd = Claims.DateTimeEnd,
                     Claims.Priority,
                     UserFirstname = Users.FirstName,
                     UserLastName = Users.LastName,
                     UserID = Users.IDNumber
                 });

如果有人可以向我指出正确使用这些联接的正确方向并解释其工作原理，我将不胜感激。提前非常感谢您。

最佳答案

var d = (from Claimants in DB.Claimants
  join Claims in DB.Claims on Claimants.Claiment_ID equals Claims.Claiment_ID)
  .DefaultIfEmpty()
  join Users in DB.Users on Claims.User_ID equals Users.User_ID
  where (Claimants.TrialDate.Value >= dtDayStart & Claimants.TrialDate <= dtDayEnd)
  .DefaultIfEmpty()
  select new
  {
    ClaimantFirstName = Claimants.FirstName,
    ClaimantLasname = Claimants.LastName,
    ClaimantsID = Claimants.IDNumber,
    Claimants.OurReference,
    Claimants.TrialDate,
    InterviewStart = Claims.DateTimeStart,
    InterviewEnd = Claims.DateTimeEnd,
    Claims.Priority,
    UserFirstname = Users.FirstName,
    UserLastName = Users.LastName,
    UserID = Users.IDNumber
 });

Left outter join