Using LINQ to JOIN two datatables using two key columns

Question

Datatable A:

Key1 | Key2 | A | B

[ 1 1 ... ]

[ 1 2 ... ]

Datatable B:

Key1 | Key2 | x | Y

[ 1 1 ... ]

[ 1 2 ... ]

Desired result:

Key1 | Key2 | A | B | X | Y

[ 1 1 ... ]

[ 1 2 ... ]

In the end result columns A, B, X and Y have been added to the new datatable. This happened because key1 and key2 were equal in both datatable A and B. Would this be possible to do with a full outer join, given a condition - (Key1 and Key2 are euqal) ?

Can you put the data in your Data tables in a more readable way? Sorry i am still not quite following — Shyju
– Shyju, Commented Apr 14, 2016 at 15:19
Maybe a T-SQL example of how you might do this in the database itself will help understand the problem and illuminate the answer. — CodingGorilla
– CodingGorilla, Commented Apr 14, 2016 at 15:25
@Shyju I'm on it, haven't found a way to properly format yet. — Iason
– Iason, Commented Apr 14, 2016 at 15:25
if we could see the data in an actual data table format. one table for A, B and the result. — sam starobin
– sam starobin, Commented Apr 14, 2016 at 15:31
Are you assuming B[....] and Y[...] contain the same values? — jdweng
– jdweng, Commented Apr 14, 2016 at 15:33

Shyju · Accepted Answer · 2016-04-14 15:42:11Z

1

var list1 = (from t1 in dataTable1.AsEnumerable()
                select new
                {
                    Key1 = t1.Field<int>("Key1"),
                    Key2 = t1.Field<int>("Key2"),
                    A = t1.Field<string>("A"),
                    B = t1.Field<string>("B")
                });

var list2 = (from b in dataTable2.AsEnumerable()
                select new
                {
                    Key1 = b.Field<int>("Key1"),
                    Key2 = b.Field<int>("Key2"),
                    X = b.Field<string>("X"),
                    Y = b.Field<string>("Y")
                });

// Now join the 2 collections and get the result you want.

var result = (from x in list1
                join y in list2 on new { x.Key1,x.Key2} equals new { y.Key1,y.Key2 }
                select new { A = x.A, X = y.X }).ToList();

Assuming Key1 and Key2 are int type and A.B,X and Y are of string type.

answered Apr 14, 2016 at 15:42

Shyju

220k106 gold badges421 silver badges499 bronze badges

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

4 Comments

Panagiotis Kanavos Over a year ago

Won't AsEnumerable load everything from the database?

Shyju Over a year ago

From msdn The AsEnumerable<TSource>(IEnumerable<TSource>) method has no effect other than to change the compile-time type of source from a type that implements IEnumerable<T> to IEnumerable<T> itself.

Iason Over a year ago

How can I automate this process? Say I only know the 2 datatables have the key1 and key2 columns, but I don't know what other columns they have (I can safely assume they are different columns in the 2 datatables though). I'm asking because here it was done manually (for column X,Y, A and B).

Iason Over a year ago

@Shyju Also, what if datatable does exist in memory but is empty? Can what I ask be done?

Beldi Anouar · Accepted Answer · 2016-04-14 15:34:44Z

1

Try this code friend :

var req = (from A in DatatableA
       join B in DatatableB 
        on A.Key1
            equals B.Key1  into DatatableResult
            select new
           {
                  Key1 = A.Key1    ,
                 Key2 = A.Key2    ,
               A= A.A ,
               x= B.x ,
               y= B.y ,

             }).ToList();

answered Apr 14, 2016 at 15:34

Beldi Anouar

2,1802 gold badges13 silver badges17 bronze badges

1 Comment

Iason Over a year ago

Thanks. How can I do this with an empty table? Say table A was empty, and B was the same as in my question. How would I get the same result table as in the question above?

Collectives™ on Stack Overflow

Using LINQ to JOIN two datatables using two key columns

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related