Convert a two-table exists query from SQL to Linq using dynamic fields in the subquery

I'm trying to query old Access database tables and compare them with SQL Server tables. They often don't have primary keys, or they have extra fields that had some purpose in the nineties, etc., or the new tables have new fields, etc.

I need to find records - based on a set of fields specified at runtime - that are in one table but not another.

So, I do this kind of query all the time in SQL, when I'm comparing data in different tables:

dim fields_i_care_about as string = "field1, field2, field3" 
'This kind of thing gets set by a caller, can be any number of fields, depends on the 
'table

dim s as string= ""

dim flds = fields_i_care_about.split(",")
for i as integer = 0 to ubound(flds)
    if s > "" then s += " AND "
    s += " dysfunctional_database_table." & flds(i) & "=current_database_table." & flds(i)
next

s = "SELECT * from dysfunctional_database_table where not exists (SELECT * from current_database_table WHERE " & s & ")"

====

I'm trying to do this using Linq because it seems like some of the dataty开发者_运维百科pe problems with two different database types become less of a headache, but I'm new to Linq and totally stuck.

I got as far as this:

• Put old and new tables into datatables as dt1 and dt2

   Dim new_records = _
              From new_recs In dt2.AsEnumerable
              Where Not ( _
                  From old_recs In dt1.AsEnumerable Where old_recs(field1) = new_recs(field1) AndAlso old_recs(field2) = new_recs(field2)).Any
               Select new_recs
  

But I can't figure out how to put this part in on the fly - old_recs(field1) = new_recs(field1) AndAlso old_recs(field2) = new_recs(field2)

So far I've tried: putting the fields I want to compare and making them a string and just putting that string in as a variable ( I thought I was probably cheating, and I guess I was)

dim str = old_recs(field1) = new_recs(field1) AndAlso old_recs(field2) = new_recs(field2)

 From new_recs In dt2.AsEnumerable
            Where Not ( _
                From old_recs In dt1.AsEnumerable Where str).Any
             Select new_recs

It tells me it can't convert a Boolean -

Is there any way to do this without Linq expressions? They seem far more complex than what I'm trying to do here, and they take a lot of code, and also I can't seem to find examples of Expressions where we're comparing two fields in a subquery.

Is there a simpler way? I know I could do the usual EXISTS query using JOIN or IN - in this case I don't need the query to be super fast or anything. And I don't need to use a DataTable or DataSet - I can put the data in some other kind of object.

So I found a lot of sample code that used MethodInfo and reflection and things like that, but I couldn't get any of it to work - these Datarows have a Field method but it requires that you add an (of object) argument before the field name argument and that's tricky to do.

So I'm not sure if this solution is the most efficient way, but at least it works. I'd be interested in finding out whether this way of doing it is efficient and why or why not. It seemed like most people used reflection to do this kind of thing, but I couldn't get that working properly and anyway what I'm trying to do is pretty simple while those methods were pretty complex. I suppose I'm doing Linq with a SQL mindset, but anyway it works.

 Dim f As Func(Of DataRow, DataRow, String, Boolean) = Function(d1 As DataRow, d2 As DataRow, s As String)
                                                                  Dim fields = Split(s, ",")
                                                                  Dim results As Boolean = True
                                                              For k As Integer = 0 To UBound(fields)
                                                                  Dim obj = DataRowExtensions.Field(Of Object)(d1, fields(k))
                                                                  Dim obj2 = DataRowExtensions.Field(Of Object)(d2, fields(k))
                                                                  If obj <> obj2 Then results = False : Exit For
                                                              Next
                                                              Return results
                                                          End Function
 Dim new_records = _
        From new_recs In dt2.AsEnumerable.AsQueryable()
        Where Not ( _
            From old_recs In dt1.AsEnumerable.AsQueryable Where f(old_recs, new_recs, id_key)).Any
         Select new_recs

    Try
        Return new_records.CopyToDataTable
    Catch ex As Exception
        Stop
    End Try