Dynamic join clause linq

Question

Is it possible to build dynamic linq query. My problem is that the join conditions could be one to multiple. The number of fields to join changes for different scenarios.

 dim data = (From drow As DataRow In dtDistinctRecords.Rows
             Join mainSourceRow As DataRow In mainSource.Rows
             On drow.Field(Of String)(var0) Equals mainSourceRow.Field(Of String)(var0)
                 And drow.Field(Of String)(var1) Equals mainSourceRow.Field(Of String)(var1)
                 And drow.Field(Of String)(var2) Equals mainSourceRow.Field(Of String)(var2)
                 And drow.Field(Of String)(var3) Equals mainSourceRow.Field(Of String)(var3)

The above problem's solution is given below: This piece of code works on setting option infer (available on project property) on but not on off. I have a legacy application which has this option set off. Still unable to make it work with option infer set to off.

 Private Sub SurroundingSub()
    Dim columnNames As String() = {"MyColumn1", "MyColumn2"}
    Dim dt As DataTable = New DataTable()
    dt.Columns.Add("MyColumn", GetType(String))
    dt.Columns.Add("MyColumn1", GetType(String))
    dt.Columns.Add("MyColumn2", GetType(String))
    dt.Columns.Add("Data", GetType(String))

    For i As Integer = 0 To 10 - 1
      Dim dr As DataRow = dt.NewRow()
      dr("MyColumn") = "MyColumn" & i
      dr("MyColumn1") = "MyColumn1" & i
      dr("MyColumn2") = "MyColumn2" & i
      dr("Data") = "Data1" & i
      dt.Rows.Add(dr)
    Next

    Dim dt1 As DataTable = New DataTable()
    dt1.Columns.Add("MyColumn", GetType(String))
    dt1.Columns.Add("MyColumn1", GetType(String))
    dt1.Columns.Add("MyColumn2", GetType(String))
    dt1.Columns.Add("Data", GetType(String))

    For i As Integer = 0 To 5 - 1
      Dim dr As DataRow = dt1.NewRow()
      dr("MyColumn") = "MyColumn" & i
      dr("MyColumn1") = "MyColumn1" & (i)
      dr("MyColumn2") = "MyColumn2" & (i)
      dr("Data") = "Data2" & i
      dt1.Rows.Add(dr)
    Next

    dt1.Rows(0)("MyColumn1") = "MyColumn111"
    dt1.Rows(1)("MyColumn2") = "MyColumn888"
    Dim data = (From dr In dt.AsEnumerable() Join dr1 In dt1.AsEnumerable() On dr("MyColumn") Equals dr1("MyColumn") Select New With {.Dr = dr, .Dr1 = dr1
          })


 **'It fails here**
 For Each column As String In columnNames

   Dim columnname = column
   data = data.Where(Function(x) x.dr.Field(Of String)(columnname) = 
   x.dr1.Field(Of String)(columnname))
 Next

    Dim value = data.[Select](Function(x) x.dr1).CopyToDataTable()
  End Sub

xanatos · Accepted Answer · 2018-07-26T12:14:10.367

1

Cheat (a little): If you have a inner join, putting the condition on the On part of the join or in the Where is equivalent, and if you have multiple conditions in And in a Where, splitting the Where in multiple Where is equivalent.

So you could:

Dim data = From drow as DataRow In dtDistinctRecords.Rows 
           From mainSourceRow In mainSource.Rows 
           Select New With { drow, mainSourceRow }

If cond1 Then
    data = data.Where(Function(x) x.drow.Field(Of String)("var0") = x.mainSourceRow.Field(Of String)("var0"))
End If

If cond2 Then
    data = data.Where(Function(x) x.drow.Field(Of String)("var1") = x.mainSourceRow.Field(Of String)("var1"))
End If

edited Jul 26 '18 at 12:14

answered Jul 26 '18 at 12:07

xanatos

109,618
12
197
280

@arjun Then how would you decide which conditions to add? – xanatos Jul 26 '18 at 12:33
I have a list of column names on which join has to be applied. In sql , we can just build build string and run it. I am wondering if I can do something similar here. – arjun Jul 26 '18 at 12:35
@arjun Yes, you can make a `For Each` cycle on the column list and pass the column name inside the `x.drow.Field(Of String)(colName) = x.mainSourceRow.Field(Of String)(colName)` – xanatos Jul 26 '18 at 12:37
What if i wanna apply the where condition on multiple fields at once instead of one at a time. – arjun Jul 26 '18 at 12:41
1

@arjun There is no difference as I've written. Linq is lazy, and until you enumerate it, it doesn't do anything, and multiple `Where` are equivalent to a single big `Where` – xanatos Jul 26 '18 at 12:45
Could you please see the changes I have done on this issue. when the option infer options on project property is set to off the following code does not work. Is there a work around on how to achieve when the option infer options is set to off. – arjun Jul 27 '18 at 09:01
@arjun I don't know VB.NET . The code I wrote I obtained by a C#->VB.NET converter :-) – xanatos Jul 27 '18 at 09:11
@arjun You have `Option Strict On`... That is creating the problem. You are using an anonymous (the `Select New With`). You can't have both `Strict On` and `Infer Off`. See https://stackoverflow.com/q/3451434/613130 Or you can put a `Option Infer On` as the first line of the vb file, and the option will be only for that file. – xanatos Jul 27 '18 at 09:20
That is correct is correct . The options Strict off and infer off. The problem is with infer option being off. If I set it on it will work for sure. But I am working on legacy application and if i changed this option to on there are loads of other compile issues. The question is can i still achieve the problem with this option set to off. – arjun Jul 27 '18 at 09:28
@arjun Put the option at the beginning of the file. If the file is too much full of legacy code, split the method you want in another file. You can even have multiple files for the same class through the use of `Partial Module MyModule` – xanatos Jul 27 '18 at 09:29

Dynamic join clause linq

1 Answers1