Left outer join in linq

Question

I have the following query but i have no idea on how to do a left outer join on table 1.

var query = (from r in table1
             join f in table2
                 on r.ID equals f.ID
             select new
             {     
                 r.ID, 
                 r.FirstName,
                 r.LastName,
                 FirstNameOnRecord = 
                     (f != null ? f.FirstName : string.Empty),
                 LastNameOnRecord = 
                     (f != null ? f.LastName : string.Empty),
                 NameChanged = 
                     (f != null 
                         ? (f.FirstName.CompareTo(r.FirstName) == 0 
                             && f.LastName.CompareTo(r.LastName) == 0) 
                         : false)
             }).ToList();

[The .NET Standard Query Operators](http://msdn.microsoft.com/en-us/library/bb394939.aspx) has been very useful to me since I started using linq a couple of years ago. The link has loads of examples and definitions (you'll want to peruse the "join" section,and specifically the GroupJoin). — Jagd, Oct 19 '10 at 17:56

score 6 · Accepted Answer · edited May 23 '17 at 11:55

Refer this or this examples to learn more and your case it would be something like this-

var query = from r in table1
            join f in table2
            on r.ID equals f.ID into g
            from f in g.DefaultIfEmpty()
             select new
             {     
                r.ID
                , r.FirstName
                , r.LastName
                , FirstNameOnRecord = (f != null ? f.FirstName : string.Empty)
                , LastNameOnRecord = (f != null ? f.LastName : string.Empty)
                , NameChanged = (f != null ? (f.FirstName.CompareTo(r.FirstName) == 0 
                &&  f.LastName.CompareTo(r.LastName) == 0) : false)
              }).ToList();

score 5 · Answer 2 · answered Oct 19 '10 at 17:38

5

Here is a great breakdown of the left outer join.

answered Oct 19 '10 at 17:38

Aaron McIver

24,527
5
59
88

score 3 · Answer 3 · answered Oct 19 '10 at 17:38

3

Have you seen these examples? You're probably interested in this part about Left Outer Join in Linq.

answered Oct 19 '10 at 17:38

Onkelborg

3,927
1
19
22

score 0 · Answer 4 · answered Apr 03 '14 at 15:15

Using lambda expression

db.Categories    
  .GroupJoin(
     db.Products,
     Category => Category.CategoryId,
     Product => Product.CategoryId,
     (x, y) => new { Category = x, Products = y })
  .SelectMany(
     xy => xy.Products.DefaultIfEmpty(),
     (x, y) => new { Category = x.Category, Product = y })
  .Select(s => new
  {
     CategoryName = s.Category.Name,     
     ProductName = s.Product.Name   
  })

Left outer join in linq

4 Answers4

Linked

Related