LINQ join two DataTables

Question

Hi I have a problem joining two DataTables using LINQ. Tables have columns like this:

table1        table2 ID, name       ID, stock 1, item1       1, 100 2, item2       3, 50 3, item3 

I used linq to join like this:

DataTable dtResult = new DataTable(); dtResult.Columns.Add("ID", typeof(string)); dtResult.Columns.Add("name", typeof(string)); dtResult.Columns.Add("stock", typeof(int));  var result = from dataRows1 in table1.AsEnumerable()              join dataRows2 in table2.AsEnumerable()              on dataRows1.Field<string>("ID") equals dataRows2.Field<string>("ID")               select dtResult.LoadDataRow(new object[]              {                 dataRows1.Field<string>("ID"),                 dataRows1.Field<string>("name"),                 dataRows2.Field<int>("stock"),               }, false); result.CopyToDataTable(); 

Problem is, result only shows IDs which are in the table2.

dtResult ID, name, stock 1, item1, 100 3, item3, 50 

I need to show also the missing items. This is the wanted result:

dtResult ID, name, stock 1, item1, 100 2, item2, 0  //Prefer if it is "0", otherwise can be left "null" 3, item3, 50 

I believe I should do left outer join, but I do not have enough knowledge about linq. Help appreciated. Thank you!

Answer 1

This will let you default to 0 if the row doesn't exist in table2:

var result = from dataRows1 in table1.AsEnumerable()              join dataRows2 in table2.AsEnumerable()              on dataRows1.Field<string>("ID") equals dataRows2.Field<string>("ID") into lj              from r in lj.DefaultIfEmpty()              select dtResult.LoadDataRow(new object[]              {                 dataRows1.Field<string>("ID"),                 dataRows1.Field<string>("name"),                 r == null ? 0 : r.Field<int>("stock")               }, false); 

MSDN source

Answer 2

try this:

var result = from dataRows1 in table1.AsEnumerable() join dataRows2 in table2.AsEnumerable() on dataRows1.ID  equals dataRows2.ID into ps from r in ps.DefaultIfEmpty() select new { C= dataRows1 , r == null ? 0 : dataRows2.Stock};