Variable Select Statement in LINQ

[Christhemist]

I am trying to recreate an Access/SQL like view builder in VB.net

I have two DataTables, AddressListDataTable and MatrixDataTable

These two DataTables will be combined with LINQ and added to a third DataTable called CombinedDataTable

My Issue is that the fields in both the AddressListDataTable and MatrixDataTable will change often, so I need to figure out how to build a custom Select Statement.

Here is my code: (Highlighted is the line that will change often because the field names will change)

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                   Join row2 In MatrixDatatable.AsEnumerable()
                   On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
                   Select row1("Account") & "|" & row1("First") & "|" & row1("OFFERCODE") & "|" & row2("Expiration") & "|" & row2("OfferDescription")

The highlighted row actually works, but it is a manual solution. I will be selecting different fields each time because the AddressListDataTable and MatrixDataTable content will be populated with different data and column names each time.

So, how do I create a custom select statement each time the code is ran?

Also, here is how I am adding the data to the CombinedDataTable:

For Each r1r2 In both

            Dim words As String() = r1r2.Split(New Char() {"|"c})
            
            CombinedDataTable.Rows.Add(words)

        Next

[.paul.]

Select row1(a string variable) & "|" & ...

[jmcilhinney]

Will there always be the same number of columns selected?

[i00]

Do you mean like this:

Dim both = From row1 In AddressListDatatable.AsEnumerable()
           Join row2 In MatrixDatatable.AsEnumerable()
           On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
           Select Join(AddressListDatatable.Columns.OfType(Of DataColumn).Select(Function(x) $"{row1(x.ColumnName)}").ToArray, "|") & "|" &
                  Join(MatrixDatatable.Columns.OfType(Of DataColumn).Select(Function(x) $"{row2(x.ColumnName)}").ToArray, "|")

Kris

[Christhemist]

@.paul That's what I tried but it doesn't like the quotes within the string

@jmcilhinney it will not always be the same amount of columns, that will vary each time.

@i00 I believer your solution selects all of the columns from both tables. I need to only select certain columns from the join statement.

[jmcilhinney]

it will not always be the same amount of columns, that will vary each time.

In that case, you should be able to do something like this:

Dim addressListColumnNames As New List(Of String)
Dim matrixColumnNames As New List(Of String)
 
'Add the names of the columns you want to project to the appropriate lists.
 
Dim both = From row1 In AddressListDatatable.AsEnumerable()
           Join row2 In MatrixDatatable.AsEnumerable()
           On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
           Select String.Join("|",
                              addressListColumnNames.Select(Function(alcn) row1(alcn)).
                                                     Concat(matrixColumnNames.Select(Function(mcn) row2(mcn))))

[Christhemist]

That worked perfectly!

My only complaint now is that for large recordsets it takes longer than I'd like to loop through the data to split it into the CombinedDataTable fields. As seen here:

 

        Dim DataCount As Integer = 1

        ProgressBar1.Maximum = both.Count
    
        For Each r1r2 In both

            ProgressBar1.Value = DataCount

            Dim words As String() = r1r2.Split(New Char() {"|"c})

            CombinedDataTable.Rows.Add(words)

            DataCount = DataCount + 1

        Next

[jmcilhinney]

Why create a String that needs splitting in the first place?

Dim addressListColumnNames As New List(Of String)
Dim matrixColumnNames As New List(Of String)
 
'Add the names of the columns you want to project to the appropriate lists.
 
Dim both = From row1 In AddressListDatatable.AsEnumerable()
           Join row2 In MatrixDatatable.AsEnumerable()
           On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
           Select addressListColumnNames.Select(Function(alcn) row1(alcn)).Concat(matrixColumnNames.Select(Function(mcn) row2(mcn)))
 
Dim combinedDataTable As New DataTable
 
'Add columns to combinedDataTable here.
 
For Each result In both
    combineDataTable.Rows.Add(result.ToArray())
Next

Not only will that be faster but it will maintain the same data types too.

[Christhemist]

Great idea!

I am getting this error when trying it though: "Input array is longer than the number of columns in this table."


Edit: Nevermind, I didn't have any columns added to the DataTable... my bad.

Thanks for all your help!