Conditional join in LINQ?

Question

I am trying to write a query that captures information from one database and attaches it to information in another database.

TableA idA valueA idB TableB idB valueB 

The tricky part is that in table A, idB is not always defined, so when I make a normal join, I only get results where TableA has idB. I want to be able to capture all the information from TableA, even if it does not have the corresponding idB value.

Answer 1

Here is the syntax version of the left join request syntax for responding to 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 }; 

Answer 2

Use the left outer join to check if the value returned from the right side is null and the default value for this 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 make a left outer join in LINQ using SelectMany (directly calling Queryable methods) or in the join ... into syntax of understanding

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

The output of Bs will be IEnumerable<typeof-db-TableB>

Answer 4

Left connection 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 };