dcsimg
Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    93

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

    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.)

    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 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:

    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))
    Last edited by Christhemist; May 7th, 2018 at 06:05 PM.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,540

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

    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:
    Code:
    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

  3. #3

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    93

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

    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:

    Code:
      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:

    Code:
    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")"

  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    22,365

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

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width