1

I have two DataTables with the same structure as each other. There are two columns in these tables ID and LIST. One table is populated from an internal database and other table is populated by a webservice call to a vendor's database.

Both Tables have close to 2 million records.

I am trying to compare the two tables to figure out where that are matches so I can figure out which records that I need to synchronize.

Table 1            Table 2          ACTION
--------           --------         ------
ID  LIST           ID  LIST
--  ----           --  ----
1   ABC            1   ABC          No Change
1   BCD                             Add To Table 2 (1,BCD)
2   ABC            2   ABC          No Change
2                  2   BCD          Delete From Table 2 (2,BCD) Leave (2,ABC) 
                   3   BCD          Delete From Table 2 (3,BCD)

With the data provided. I have come up with the following LINQ queries

Dim t1_DistinctIDs As System.Collections.Generic.IList(Of String) = (From r In ds.Tables("t1").AsEnumerable
                                                                     Select r.Field(Of String)("ID")).Distinct.ToList

Dim t2_DistinctIDs As System.Collections.Generic.IList(Of String) = (From r In ds.Tables("t2").AsEnumerable
                                                                     Select r.Field(Of String)("ID")).Distinct.ToList

'Records to Delete from Table 2 Because They Don't Exist In Table 1
Dim IDsToDelete As Array = (From r In ds.Tables("t2").AsEnumerable()
                            Where Not t1_DistinctIDs.Contains(r.Field(Of String)("ENTITY_ID"))).ToArray

'Get All ID & LIST in Table 1
Dim t1_AllItems As Array = (From r In ds.Tables("t1").AsEnumerable
                            Select New With
                              {
                                .ID = r.Field(Of String)("ID"),
                                .LIST = r.Field(Of String)("LIST")
                              }
                           ).ToArray

'Find All Records In Table 1 That Do Not Exist In Table 2
Dim ListToAdd As Array = (From r In ds.Tables("t2").AsEnumerable()
                          Join l In t1_AllItems
                            On r.Field(Of String)("ID") Equals l.ID _
                           And r.Field(Of String)("LIST") Equals l.LIST _
                          Select New With
                            {
                              .ID = r.Field(Of String)("ID"),
                              .NAME = r.Field(Of String)("LIST")
                            }
                         ).ToArray

This is what I would do if I were using T-SQL.

DECLARE @T1 AS TABLE (
    ID INT,
    LIST VARCHAR(10)
)

DECLARE @T2 AS TABLE (
    ID INT,
    LIST VARCHAR(10)
)


INSERT INTO @T1(ID,LIST) VALUES (1,'ABC')
INSERT INTO @T1(ID,LIST) VALUES (1,'BCD')
INSERT INTO @T1(ID,LIST) VALUES (2,'ABC')
INSERT INTO @T2(ID,LIST) VALUES (1,'ABC')
INSERT INTO @T2(ID,LIST) VALUES (2,'ABC')
INSERT INTO @T2(ID,LIST) VALUES (2,'BCD')
INSERT INTO @T2(ID,LIST) VALUES (3,'BCD')

SELECT 
    t1.ID,
    t1.LIST
FROM @T1 t1
LEFT JOIN @T2 t2
  ON t1.ID = t2.ID
 AND t1.LIST = t2.LIST
WHERE t2.ID IS NULL

I am trying to figure out how to do a Left Join where t2.ID IS NULL in LINQ.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
jlimited
  • 685
  • 2
  • 11
  • 20

0 Answers0