Left / Right connect to multiple connections in a request

Question

I have a lookup table, there are 3 fields in this table ( Id , User1 , User2 ). User2 field may be null, but we use 0 when not in use.

When I executed the Linq query below, the entries in which User2 == 0 are not part of the result.

How can I do a left / right join in this case?

 class MyReference { public int Id { get; set; } public int User1 { get; set; } public int User2 { get; set; } } class User { public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } } class MyClassResult { public int Id { get; set; } public string U1FirstName { get; set; } public string U2FirstName { get; set; } } var result = (from myReference in context.MyReference join u1 in context.USER on myReference.User1 equals User.Id join u2 in context.USER on myReference.User2 equals User.Id select new MyClassResult { Id = myReference.Id, U1FirstName = u1.FirstName, U2FirstName = u2.FirstName }).ToList(); 

Answer 1

You must use the into and DefaultIfEmpty to get null elements:

 var result = (from myReference in context.MyReference join u1 in context.USER on myReference.User1 equals u1.Id join u2 in context.USER on myReference.User2 equals u2.Id into x2 from un2 in x2.DefaultIfEmpty() select new MyClassResult { Id = myReference.Id, U1FirstName = u1.FirstName, U2FirstName = un2 != null ? un2.FirstName : null }); 

Alternatively, especially if myReference.User1 can be 0 too, you can use the let keyword to get user information before final selection:

 var result = (from myReference in context.MyReference let u1 = users.FirstOrDefault(u => u.Id == myReference.User1) let u2 = users.FirstOrDefault(u => u.Id == myReference.User2) select new MyClassResult { Id = myReference.Id, U1FirstName = u1 != null ? u1.FirstName : null, U2FirstName = u2 != null ? u2.FirstName : null });