Compare tables based on more than one column using LINQ?

Help Something Else
,
1

INPUT

There are two Data Tables.

The first table is as follows:

PLCNO NAME DATE AGE DDNO EML TRANID
1000 John 14-06-2021 23 1 500 ASC234RT
1001 Mary 15-06-2021 43 2 100 WERT432
1002 Ram 16-06-2021 55 400 DHI765WE
1003 Shyam 13-06-2021 22 5 400 QWE123AS
1004 Ali 12-06-2021 18 6 300 JHJHJ23
1006 Robin 11-06-2021 32 100 ASW23RT
1007 Ash 10–6-2021 29 5 400 TEW345

and the second table is like

PLCNO CITY PHONE BLD ISPRES DDNO EML STATUS
1000 CALIFORNIA 23456 B YES 1 500 CONF
1001 PARIS 24356 AB NO 2 100 PEND
1002 LONDON 23789 O YES 100 DONE
1003 DELHI 23098 AB NO 5 400 CONF
1004 CHENNAI 23000 AB YES 6 300 PEND
1005 LUCKNOW 23765 A NO 2 500 DONE

The columns PLCNO, DDNO, EML are common in both the tables.

OUTPUT

Two output tables are required which are as follows-

First Output table is as follows:

PLCNO NAME DATE AGE DDNO EML TRANID STATUS
1000 John 14-06-2021 23 1 500 ASC234RT CONF
1001 Mary 15-06-2021 43 2 100 WERT432 PEND
1002 Ram 16-06-2021 55 400 DHI765WE
1003 Shyam 13-06-2021 22 5 400 QWE123AS CONF
1004 Ali 12-06-2021 18 6 300 JHJHJ23 PEND
1006 Robin 11-06-2021 32 4 100 ASW23RT
1007 Ash 10–6-2021 29 5 400 TEW345

Second output Table is as follows:

PLCNO NAME DATE AGE DDNO EML TRANID STATUS
1000 John 14-06-2021 23 1 500 ASC234RT CONF
1001 Mary 15-06-2021 43 2 100 WERT432 PEND
1003 Shyam 13-06-2021 22 5 400 QWE123AS CONF
1004 Ali 12-06-2021 18 6 300 JHJHJ23 PEND

PROCESS

To get the output tables, we need to compare PLCNO, DDNO and EML of both the input tables. All the rows for which the their values are Equal it is added in the output table. The first output table is the first input table with one extra column (STATUS) from the 2nd input table with values mentioned only for rows in which there is a match (i.e. LEFT JOIN)

The 2nd output table it’s INNER JOIN on PLCNO, DDNO and EML columns.

RQUIRED

Separate LINQ queries for both outputs

Note: The actual operation is to be performed on tables containing 650K rows and 150 columns. There is no such requirement of row order to be same as shown in output tables.

2

There are several topics related to DT join operation using LINQ.

Search the forum.

Cheers

3

do some preperations for the target structures:
Clone dt1 and add the status datacolumn to the dtReportTMPL
Clone for dtOut1/2
grafik

First LINQ:

(From d1 In dt1.AsEnumerable
Group Join d2 In dt2.AsEnumerable 
On d1("PLCNO").toString Equals d2("PLCNO").toString And d1("DDNO").toString Equals d2("DDNO").toString And d1("EML").toString Equals d2("EML").toString Into gj = Group
From g In gj.DefaultIfEmpty
Let status =  If(isNothing(g), Nothing, g("STATUS"))
Let ra = d1.ItemArray.Append(status).toArray
Select dtOut1.Rows.Add(ra)).CopyToDataTable

Second LINQ:

(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable 
On d1("PLCNO").toString Equals d2("PLCNO").toString And d1("DDNO").toString Equals d2("DDNO").toString And d1("EML").toString Equals d2("EML").toString 
Let ra = d1.ItemArray.Append(d2("STATUS")).toArray
Select dtOut1.Rows.Add(ra)).CopyToDataTable
5 Likes
4

Can you explain the functioning of DefaultIfEmpty. @ppr (Processing Model of the queries)

5

@kumar.varun2 - please check these references…

2 Likes
6

@kumar.varun2
prasath17 already did. In case of open questions a look to the docu or to
Linqsamples.com DefaultIfEmpty LINQ Sample (simple)
gives a good first help

7

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