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.