Comparison of two tables using Linq

 There are two tables


A1 200
A2 400
A3 300
A4 500
A5 600
A6 700
A7 800
A8 900
A9 100
A10 500



A1 200
A3 300
A6 350
A7 800
A8 1800
A9 100
A10 500
A2 400
A11 10
A12 200
A13 300
A10 400

Need a table like this


A1 200 0
A3 300 0
A6 700 350
A7 800 0
A8 900 -900
A9 100 0
A10 500 0
A2 400 0
A11 #N/A #N/A
A12 #N/A #N/A
A13 #N/A #N/A
A10 500 100

The third table contains all the Codes of table-2 and the amount from table-1 wrt to code(which are present in table-1) and the diff col (table-1_amount  -   table-3_amount). Normally it is a VLOOKUP. (table-3 contains the amount of all those codes which are present in table-1 and table-2)

Need to perform this using LINQ.


give a first try on join datatable activity - join type outer join

fetching the evaluated result from joined table result we would do with e.g. LINQ

In one of your solution on the forum, I found the linq query and made some changes. It is working fine now.

(From d In dtData2.AsEnumerable
Let check = dtData1.AsEnumerable.Where(Function (r) r(0).ToString.Trim.Equals(d(0).ToString.Trim)).ToList
Let c2 = If(check.Count()>0, check(0)(1), "")
Let c3 = If(Not c2.Equals(""), (Convert.ToDouble(d(1).ToString)-Convert.ToDouble(c2.ToString)).ToString, "NA")
Let ra = {d(0), c2, c3}
Select dtResult.Rows.Add(ra)).CopytoDataTable
1 Like

the case has a chance to get turned from full outer join to left join (dt2 - to - dt1)

I will have a look on it later. In the meanwhile find a demo on left join done with LINQ here:

1 Like


find the alternate approach implementing a left join (fro dt2-to-dt1)


maybe it it will give better results when:

  • multiple same codes from dt2 will be present in dt1
  • AND the result will be deduplicated

find starter help here:
LeftJoin_1Col_DiffReport.xaml (12.1 KB)

Please explain the query.

Could you please explain the working of ‘group’?

have a look here:

okay. Thanks

While using the above query on 6,00,000 rows of data, it is taking a lot of time. Can we decrease time somehow?

it is a very relative mesurement. Lets try to bring it into facts and numbers.
However lets find out which building block is consuming which portion of execution time. From where is the data retrieved, is it Excel?

The query part is taking time.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.