Datatable.merge like join

Question

I have two DataTables like this:

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();

dt1:

dt2:

I need to merge two DataTables and set to GridView like this:

dt1.Merge(dt2, true, MissingSchemaAction.Add);
GridView1.DataSource = dt1;
GridView1.DataBind();

But getting output like this:

How can I merge like this?

Salah Akbari · Accepted Answer · 2016-06-27T07:37:13.267

5

You need to add this lines:

dt1.PrimaryKey = new[] { dt1.Columns[0], dt1.Columns[1] };
dt2.PrimaryKey = new[] { dt2.Columns[0], dt2.Columns[1] };

edited Jun 27 '16 at 07:37

answered Jun 27 '16 at 07:00

Salah Akbari

39,330
10
79
109

Getting error like this: These columns don't currently have unique values."} System.Exception {System.ArgumentException} – onur Jun 27 '16 at 07:02
I don't have unique column. I need to join by 1. and 2. columns. – onur Jun 27 '16 at 07:05
2

Then set col 0 and col 1 as primary key – Sir Rufo Jun 27 '16 at 07:06
Yes! It's work when I set col0 and col1 as primary key. Thank you. – onur Jun 27 '16 at 07:09
1

@Ricardo I updated my answer, also if you had problem with showing data correctly try this `dt1.Merge(dt2, false);` instead of `dt1.Merge(dt2, true, MissingSchemaAction.Add);`. – Salah Akbari Jun 27 '16 at 07:39

score 1 · Answer 2 · edited Sep 18 '17 at 19:58

You can do it with LINQ too:

var result = from a in dt1.AsEnumerable()
             join b in dt2.AsEnumerable() on new { C = a.Field<string>("CAR"),
             M = a.Field<string>("MODEL") } equals new { C = b.Field<string>("CAR"), 
             M = b.Field<string>("MODEL") } 
             select
             {
                CAR = a.Field<string>("CAR"),
                MODEL = a.Field<string>("MODEL"),
                DATE1 = a.Field<string>("DATE1") // You should write your type
                //...
             };

Datatable.merge like join

2 Answers2