C# join two datatables using LINQ

Question

I have 2 datatables:

DataTable table1 --> importing data from Excel file

Sample data:

Account, Name, Address, Phone 
A05911,  Test1, LA,    1234
A05912,  Test2, NY,    1235
A05912,  Test2, NY,    1235
A05913,  Test3, BO,    1239

DataTable table2 -- importing data from SQL database

Sample data:

 Account, Dummy
 A05911,  yyyy1
 A05912,  xxxx2
 A05913,  zzzz3

I want to join these 2 datatables so the resulting datatable will be:

Account, Dummy, Name, Address, Phone 
A05911,  yyyy1, Test1, LA,    1234
A05912,  xxxx2, Test2, NY,    1235
A05912,  xxxx3, Test2, NY,    1235
A05913,  zzzz4, Test3, BO,    1239

I have tried with following query:

var result = ( from a in table1.AsEnumerable(),
                     join b in table2.AsEnumberable()
                     on a.Field<string>("Account") equals b.Field<string>("Account") 
                 into temp from res in temp.DefaultIfEmpty()
                 select new 
                 {
                      Account = a.Field<string>("Account"),
                      Test = res == null ? null : res.Field<string>("Dummy")
                 });

But this query does not give all the columns from table1 and 'Dummy' column from table2. It only returns 'Account' from table1 and its 'Dummy' from table2.

How do I achieve this and also how do I store this query result into a datatable?

I want the following result in datatable:

Account, Dummy, Name, Address, Phone 
A05911,  yyyy1, Test1, LA,    1234
A05912,  xxxx2, Test2, NY,    1235
A05912,  xxxx3, Test2, NY,    1235
A05913,  zzzz4, Test3, BO,    1239

What's the logic behind writing `a.Field("Account").ToString()`? — Blindy, Aug 10 '22 at 18:33

score 2 · Answer 1 · answered Aug 10 '22 at 18:33

2

You need to add them to your select:

 select new 
 {
      Account = a.Field<string>("Account"),
      Name = a.Field<string>("Name"),
      Address = a.Field<string>("Address"),
      Phone = a.Field<string>("Phone"),
      Test = res == null ? null : res.Field<string>("Dummy")
 });

answered Aug 10 '22 at 18:33

D Stanley

149,601
11
178
240

So, the columns in table1 can vary except the Column 'Account'. So I need to select all the columns from table1 and 'Dummy' column from table2. Also, the result of this query is IEnumerable. How do I convert it to store in DataTable variable? – DevP Aug 10 '22 at 18:47
https://stackoverflow.com/questions/6609732 – D Stanley Aug 10 '22 at 18:51
Define the columns in the new datatable and add rows from `result` – D Stanley Aug 10 '22 at 18:52

C# join two datatables using LINQ

1 Answers1