Dear Masters, Can u give Linq query to merge this three data tables.Tq

Linq to merge 3 data tables.xlsx (9.1 KB)


please read these two threads

Hi @lakshmi_narayana_ch ,

Are you sure you want LINQ?
Because it can get pretty complicated…how complicated you ask?

Well here you go:

(From row In dt_main.AsEnumerable()
Let zipCountCountry = dt1.AsEnumerable().Where(Function(w) row("ID").ToString.Trim.Equals(w("ID").ToString.Trim))
Let pin = dt2.AsEnumerable.Where(Function(w) row("ID").ToString.Trim.Equals(w("ID").ToString.Trim))
Let firstCond = If(zipCountCountry.Count.Equals(0), False, True)
Let secCond = If(pin.Count.Equals(0), False, True)
Let ra = New Object() _
		If(firstCond AndAlso String.IsNullOrEmpty(row("Zip").ToString),zipCountCountry.Select(Function(s) s("Zip").ToString).First(),row("Zip")),
		If(firstCond AndAlso String.IsNullOrEmpty(row("Count").ToString),zipCountCountry.Select(Function(s) s("Count").ToString).First(),row("Count")),
		If(firstCond AndAlso String.IsNullOrEmpty(row("Country").ToString),zipCountCountry.Select(Function(s) s("Country").ToString).First(),row("Country")),
		If(secCond AndAlso String.IsNullOrEmpty(row("Pin").ToString),pin.Select(Function(s) s("Pin").ToString).First(),row("Pin"))
Select dt_result.Rows.Add(ra)).CopyToDataTable()


This is something I came up with on the spot so its a little bulky.

I’m sure you can reduce things here and there and make it more readable, but Dot Net syntax is very bulky so I’d recommend using VBA for excel automation instead.

Problem is if you decide to add/omit columns tomorrow, you have to edit the code. VBA is easier to work with imo.

Here is the workflow if you are interested.
MergeThreeTables.xaml (7.1 KB)

Kind Regards,
Ashwin A.K

Hi @lakshmi_narayana_ch

It can approached using the Join


The LINQ used

dt =

	From r3 In 
			From r1 In dt1
			Join r2 In dt2
			On r1("ID").ToString.Trim Equals r2("ID").ToString.Trim
			Let carr1 = r1.Table.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).ToArray 
			Let arr = carr1.Select(Function(c) If(String.IsNullOrEmpty(r1(c).ToSTring.Trim), If(r2.Table.Columns.Contains(c), r2(c), r1(c)), r1(c))).ToArray
			Select dt.Rows.Add(arr)
	Join r4 In dt3
	On r3("ID").ToString.Trim Equals r4("ID").ToString.Trim
	Let carr2 = r3.Table.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).ToArray
	Let rarr = carr2.Select(Function(c) If(String.IsNullOrEmpty(r3(c).ToSTring.Trim), If(r4.Table.Columns.Contains(c), r4(c), r3(c)), r3(c))).ToArray
	Select dt.Rows.Add(rarr)



Please refer the xaml file

DT_MergeThreeDTs.xaml (7.3 KB)