Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ join two DataTables

Tags:

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!

like image 450
user1080533 Avatar asked Dec 24 '13 12:12

user1080533


People also ask

How do I select a query in Linq?

LINQ query syntax always ends with a Select or Group clause. The Select clause is used to shape the data. You can select the whole object as it is or only some properties of it. In the above example, we selected the each resulted string elements.


2 Answers

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

like image 173
Sven Grosen Avatar answered Sep 28 '22 03:09

Sven Grosen


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}; 
like image 27
Masoumeh Karvar Avatar answered Sep 28 '22 04:09

Masoumeh Karvar