LEFT LINQ TO SQL C# JOIN on many to many table

Question

Hi im kinda new to linq to sql I know about the basics. The problem is I want to do a left join in a query. There are 3 tables in the query.

1. Claimants ( all rows should be returned from this table)
2. Claim
3. User

The query should return all Users who have Claimants. This is done through the many to many table Claim. But regardless of Users all Claimants should be returned. Thus the left join on Claimants.

I have the following query

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

I have tried using an into statement as follows but with no luck

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

I would appreciate it if someone could point me in the right direction as to how to use these joins left right correctly and explain how the work. Thank you very much in advance.

Answer 1

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

Answer 2

You must know a Luan. If you want all the Claiments to return start by selecting from Claiments and then left join onto the other tables.

Try the following :

LINQ to SQL Left Outer Join

Answer 3

In LINQ, the ".Join()" extension method is the equivalent of SQL inner join.

For outer joins you have to use the ".GroupJoin()" extension method.

Assuming you know the .Join well, the GroupJoin is simple to use. I have to admit that when I first needed to do an outer join in LINQ it was damn hard to find out. I cannot fanthom why did they call it like that.

Although in VB.Net, here's an article that presents various SQL constructs translated into LINQ syntax, even if in VB, still easy to convert to extension methods: http://blogs.msdn.com/b/vbteam/archive/2007/12/31/converting-sql-to-linq-part-6-joins-bill-horst.aspx?Redirected=true

EDIT: @DavidB posted in his comments a much better solution, but only if you can use some ORM navigational properties. If you don't have them, then GroupJoin is probably the most reasonable