How to union two data tables and order the result

Question

Q: If I have two DataTables like this :

Dt1(emp_num,emp_name,type)

Dt2(emp_num,emp_name,type)

I wanna to Union them and order the result by emp_name.

Solution 1

var dt1 = new DataTable(); // Replace with Dt1
var dt2 = new DataTable(); // Replace with Dt2

var result = dt1.AsEnumerable()
            .Union(dt2.AsEnumerable())
            .OrderBy (d => d.Field<string>("emp_name"));

Solution 2

i think this code will help u to do it without using entity...

Dt1.Merge(Dt2);

Comments

[wcm]

Great answer, you helped me a lot (+1) but it's missing one thing. If you want the result to be a DataTable: retium result.CopyToDataTable();

[Razor]

That's because it's missing that requirement in the question.

[Malcolm Salvador]

question : is this UNION distinct? if it is not, how can I make rows DISTINCT?

[Razor]

Distinct is a separate operation. To do this you can use the .Distinct() extension method.

[Tushar R.]

It works as expected. If you don't set any primary key on base table (in above example, Dt1), then merge works like 'UNION ALL'. It means if there are rows with same column values in base table and table to be merged, then both rows will be present in base table after merge. But if primary key is set on base table, then only those rows which don't exist in the base table as per the primary key, will be added in base table. Hence, no duplicates if primary key is set on base table.