How do I create a dynamic function? 'Linq'

Question

hi It is possible that my table is dynamic. with variable number of columns. Thanks for the correction (Function FilterByChildTable)

    Private Sub test10_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim dtParent As New DataTable
        Dim dtChild As New DataTable
        Dim dtResult As New DataTable
        dtParent.Columns.Add("parentId", GetType(Integer))
        dtParent.Columns.Add("words")
        dtParent.Rows.Add({1, "w1"})
        dtParent.Rows.Add({2, "w2"})
        dtParent.Rows.Add({3, "w3"})
        dtParent.Rows.Add({4, "w4"})
        dtParent.Rows.Add({5, "w5"})

        dtChild.Columns.Add("chidId", GetType(Integer))
        dtChild.Rows.Add(4)
        dtChild.Rows.Add(3)
        dtChild.Rows.Add(2)

        dtResult = FilterByChildTable(dtParent, dtChild)
        Dim dtParent2 As New DataTable
        dtParent2.Columns.Add("parentId", GetType(Integer))
        dtParent2.Columns.Add("words")
        dtParent2.Columns.Add("other words")
        dtParent2.Rows.Add({1, "w1", "Other"})
        dtParent2.Rows.Add({2, "w2", "Other"})
        dtParent2.Rows.Add({3, "w3", "Other"})
        dtParent2.Rows.Add({4, "w4", "Other"})
        dtParent2.Rows.Add({5, "w5", "Other"})
        dtResult = FilterByChildTable(dtParent2, dtChild,True)
    End Sub
    Private Function FilterByChildTable(DynamicColumn As DataTable, StaticColumn As DataTable,Optional WithZASort as Boolean=false) As DataTable
         'WithZASort False is AZ        
         'DynamicColumn is: with variable number of columns
        'StaticColumn is: with a fixed number of columns

        Dim query = DynamicColumn.AsEnumerable() _
               .Join(StaticColumn.AsEnumerable(),
                     Function(parentRow) parentRow("parentId").ToString(),
                     Function(childRow) childRow("chidId").ToString(),
                     Function(parentRow, childRow) New With {
                         .Filter = parentRow("parentId"),'Static Column
                         .Words = parentRow("words")'Dynamic Column
                     }) _
               .OrderBy(Function(row) row.Filter)
        Dim resultTable As New DataTable("Result") ' Dynamic
        'resultTable.Columns.Add("parentId") 'Static Column
        'resultTable.Columns.Add("words") 'Dynamic Column
        'And Other Column 'Dynamic Column
        Dim dtResult As New DataTable
        dtResult = query.Select(Function(row) resultTable.Rows.Add("need Dynamic Code")).CopyToDataTable
        Return dtResult
    End Function

Answer 1

Hi @Mansour_Dalir ,

You can use the following code to query a DataTable with a dynamic number of columns.

   Private Function FilterByChildTable(DynamicColumn As DataTable, StaticColumn As DataTable, Optional WithSort As Boolean = False) As DataTable

        Dim dynamicColumnNames = DynamicColumn.Columns.Cast(Of DataColumn)().Skip(1).Select(Function(col) col.ColumnName).ToArray()

        Dim query = DynamicColumn.AsEnumerable() _
               .Join(StaticColumn.AsEnumerable(),
                     Function(parentRow) parentRow("parentId").ToString(),
                     Function(childRow) childRow("chidId").ToString(),
                     Function(parentRow, childRow) New With {
                         .Filter = parentRow("parentId"), ' Static Column
                         .DynamicData = dynamicColumnNames.Select(Function(colName) parentRow(colName)).ToArray() ' Dynamic Columns
                     }) _
               .OrderBy(Function(row) row.Filter)

        Dim resultTable As New DataTable("Result") ' Dynamic
        resultTable.Columns.Add("parentId") ' Static Column
        For Each colName In dynamicColumnNames
            resultTable.Columns.Add(colName) ' Dynamic Columns
        Next

        Dim dtResult As DataTable = resultTable.Clone() ' Create a clone of resultTable

        For Each row In query
            Dim newRow = dtResult.NewRow()
            newRow("parentId") = row.Filter

            For i As Integer = 0 To dynamicColumnNames.Length - 1
                newRow(i + 1) = row.DynamicData(i)
            Next

            dtResult.Rows.Add(newRow)
        Next

        If WithSort Then
            dtResult.DefaultView.Sort = "parentId ASC" ' Sort dtResult by parentId column in ascending order
            dtResult = dtResult.DefaultView.ToTable()
        End If

        Return dtResult
    End Function

Best Regards.

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

hi @Jiachen Li-MSFT

Very very easy . Thank. I am becoming a professional.

    Private Function FilterByChildTable2(DynamicColumn As DataTable, StaticColumn As DataTable) As DataTable
        Dim query =
            From sel In DynamicColumn.AsEnumerable()
            Where StaticColumn.AsEnumerable().Select(Function(Helper) Helper.Item("chidId")).Contains(sel.Item("parentId"))
            Order By sel.Field(Of Integer)("parentId")
            Select sel 
        Return query.CopyToDataTable() 'view.ToTable
    End Function