CODEWITHSUNDEEP
.netvb.netlinqdynamic-linq
arjun
arjun

Reputation: 645

Dynamic join clause linq

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

Upvotes: 0

Views: 77

Answers (1)

xanatos
xanatos

Reputation: 111860

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

Upvotes: 1

Related Questions