C# join two datatables using LINQ

Question

I have 2 data tables:

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.AsEnumerable()
                     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<string>("Account").ToString()? — Blindy
– Blindy, Commented Aug 10, 2022 at 18:33

D Stanley · Accepted Answer · 2022-08-10 18:33:46Z

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, 2022 at 18:33

D Stanley

153k12 gold badges190 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DevP Over a year ago

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<a>. How do I convert it to store in DataTable variable?

D Stanley Over a year ago

stackoverflow.com/questions/6609732

D Stanley Over a year ago

Define the columns in the new datatable and add rows from result

Collectives™ on Stack Overflow

C# join two datatables using LINQ

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related