Conditional Join In LINQ?

Question

I am trying to write a query that grabs information from one database and joins it to information in a different database.

TableA
idA
valueA
idB

TableB
idB
valueB

The tricky part is that in TableA, idB isn't always defined, so when I do a normal join, I only get results where TableA has a idB value. What I want is to be able to grab all of the information from TableA even if it doesn't have a corresponding idB value.

Answer 1

You need a left join

Answer 2

Use a left outer join by checking if the value returned from the right hand side is null and supplying a default value for that case.

 var q = db.TableA.Join( db.TableA,
                         a => a.idB,
                         b => b.idB,
                         (a,b) => new
                                  {
                                      A = a.ValueA,
                                      B = b == null ? null : b.ValueB
                                  });

Answer 3

You can do a left outer join in LINQ with SelectMany (directly calling Queryable methods) or in comprehension syntax join ... into:

var results = from a in db.TableA
              join b in db.TableB on a.idB equals b.idB
                into found
              select new {
                A = a,
                Bs = found
              };

In the output Bs will be IEnumerable<typeof-db-TableB>

Answer 4

Left Join Example:

var leftOuterJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from item in prodGroup.DefaultIfEmpty(new Product{Name = String.Empty, CategoryID = 0})
        select new { CatName = category.Name, ProdName = item.Name };

Answer 5

Here is a query expression syntax version of the left join to follow up on tvanfosson's answer.

var query = from rowA in db.TableA
            join rowB in db.TableB
            on rowA.idB equals rowB.idB into b
            from item in b.DefaultIfEmpty()
            select new
            {
                idA = rowA.idA,
                valueA = rowA.valueA,
                idB = rowA.idB, 
                valueB = item != null ? item.valueB : 0 // or other default value
            };