StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POLEFT LINQ TO SQL C# JOIN on many to many table
text
Body
copied!<p>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. </p> <blockquote> <ol> <li>Claimants ( all rows should be returned from this table) </li> <li>Claim </li> <li>User</li> </ol> </blockquote> <p>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.</p> <p>I have the following query</p> <pre><code> 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 }); </code></pre> <p>I have tried using an into statement as follows but with no luck</p> <pre><code> 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 }); </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload