VB.NET LINQ join

Question

I have this challenging task in VB.NET LINQ. I have 2 databases from different servers. I cannot link the databases. The data is retrieved from the databases as :

• DB1

• Client_ID Engagement_ID Enabled Description

• 600 10 True Company1
• 600 20 False Company2
• 700 10 True Company3
• DB2
• Client_ID Engagement_ID Enabled Description
• 600 5 True Company1
• 600 10 False Company2
• 500 30 True Company3

In the front end I need to add the Engagements of the Clients that are present in DB1 and not present in DB2. I make and API call to add the engagements as I cannot update the database. In this example I need to add the second record of DB1 to DB2 as the client ID 600 exists in DB2 and the engagement ID 20 for Client ID 600 does not exist in DB2. I cannot add the third record of DB1 to DB2 as the client ID 700 does not exists in DB2. I need the LINQ query to return records where DB1.client_id = DB2.Client_ID and DB1.Engagement_ID <> DB2.Engagement_ID. If the LINQ query returns the records in a datatable or some data structure, I can loop through the records and make api calls and pass the client ID, engagement ID , Enabled and description as parameters to update DB2. Please help me with this issue. I tried to do it but LINQ allows equijoins and I cannot get it to work for DB1.client_id = DB2.Client_ID and DB1.Engagement_ID <> DB2.Engagement_ID.

THe T SQL for this task is:

select * from DB1
left join DB2 on DB1.client_ID = DB2.client_ID
and DB1.Engagement_ID = DB2.Engagement_ID
where DB2.CLient_ID is null and DB2.Engagement_ID is null
and DB1.client_id in (select client_id from DB2)

I am trying to convert this T SQL to LINQ

Thanks in advance

Answer 1

You can add the second join criterion as a Where clause:

Dim q = From e1 In db1
        Join e2 In db2 On e1.Client_ID Equals e2.Client_ID
        Where e1.Engagement_ID <> e2.Engagement_ID
        Select e1, e2