How do I select variable fields in a LINQ Select statement?

[Christhemist]

The fields in which will be selected will change each time. Also, I am selecting from several lists so it makes it hard to use a string list.

Here is my code: (Note: This works manually but once the field names change it will not work.)

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                   Join row2 In Matrix1Datatable.AsEnumerable()
                   On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
                   Select row1("Account") & "," & row1("First") & "," & row2("CODE") & "," & row2("SWITCH") 

As you can see I am selecting different fields from two DataTables. However, the name and quantity of these fields will change often. I came up with the following solution but it won't work with multiple lists. (Note: I already populated the string list "ListOfColumnNames" prior to running this code)

Here is that code:

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                   Join row2 In Matrix1Datatable.AsEnumerable()
                   On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
                   Select String.Join(", ", From i In ListOfColumnNames Select row1(i))

[dday9]

You would store the values in a collection (such as a String array) and then perform an inner query like you would in SQL. Take a look at this example that I used with some sample data to group the values based on if their column is even or odd:

Public Module Module1
  Public Sub Main()
    'Create a new DataTable
    Dim dt As New DataTable()

    'Add some columns
    dt.Columns.AddRange({New DataColumn("Offercode"), New DataColumn("Code"), New DataColumn("Account"), New DataColumn("First"), New DataColumn("Switch")})

    'Add some sample data
    Dim alphabet As String = "abcdefghijklmnopqrstuvwxyz"
    dt.Rows.Add(Enumerable.Range(1, dt.Columns.Count).Select(Function(i) alphabet(i).ToString()).ToArray())
    dt.Rows.Add(Enumerable.Range(1 + dt.Columns.Count, dt.Columns.Count).Select(Function(i) alphabet(i).ToString()).ToArray())
    dt.Rows.Add(Enumerable.Range(1 + dt.Columns.Count * 2, dt.Columns.Count).Select(Function(i) alphabet(i).ToString()).ToArray())

    'Table looks like this
    '+-----------+------+---------+-------+--------+
    '| Offercode | Code | Account | First | Switch |
    '+-----------+------+---------+-------+--------+
    '|    b      |  c   |    d    |   e   |   f    |
    '|    g      |  h   |    i    |   j   |   k    |
    '|    l      |  m   |    n    |   o   |   p    |
    '+-----------+------+---------+-------+--------+

    Dim odds() As String = {dt.Columns.Item(0).ColumnName, dt.Columns.Item(2).ColumnName, dt.Columns.Item(4).ColumnName}
    Dim evens() As String = {dt.Columns.Item(1).ColumnName, dt.Columns.Item(3).ColumnName}

    Dim odd_values() As String = (From rows As DataRow In dt.AsEnumerable() Select String.Join(", ", (From column As String In odds Select rows.Item(column)))).ToArray()
    Dim even_values() As String = (From rows As DataRow In dt.AsEnumerable() Select String.Join(", ", (From column As String In evens Select rows.Item(column)))).ToArray()
    Console.WriteLine(String.Join(", ", odd_values))
    Console.WriteLine(String.Join(", ", even_values))
  End Sub
End Module

Fiddle: Live Demo

[Christhemist]

dday9, your example was way different from the linq query I was using. I can create a custom string of fields to use, but the "Select" part of my code doesn't read the string list as a Select Statement. This is what I am looking for help with.

Here is my attempt at getting a customer string list to use for the select statement:

  For i As Integer = 0 To CombinedListView.Items.Count - 1 Step 1

            CombinedListView.Items(i).Selected = True

        Next i

        For Each item As ListViewItem In CombinedListView.Items

            CombinedDataTable.Columns.Add(item.Text)

        Next

        For Each col In CombinedDataTable.Columns

            If CombinedListView.SelectedItems(Counter).SubItems(1).Text = "Matrix" Then
                SelectMe = SelectMe & "row2(""" & col.ToString & """) & "","" & "
            Else
                SelectMe = SelectMe & "row1(""" & col.ToString & """) & "","" & "
            End If

            Counter = Counter + 1
        Next


        SelectMe = SelectMe.Substring(0, SelectMe.Length - 8)

CombinedListView: column 1 has the field name, column 2 has the name of which list it is from. I then loop through the CombinedListView field names and add them as columns to the CombinedDataTable.

I then loop through the DataTable Columns and depending on what list the column was chosen from I add it to the "SelectMe" string.

SelectMe is the custom string I am trying to use for my Select Statement in the LINQ query, but it doesn't read it as a Select Statement. See Below:

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                   Join row2 In GetMatrixFieldsDatatable.AsEnumerable()
                   On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
                   Select SelectMe

SelectMe basically ends up looking like this: "row1("Account") & "," & row1("First") & "," & row2("CODE") & "," & row2("SWITCH")"

[.paul.]

That way is not possible. The row1, row2 parts can't be part of a string.