LINQ to DataSet - Joins and GridView Issues

Question

I've got three DataTables that I need to join together, and use the joined data as the DataSource for a GridView. The first (localSQLTable) is populated via a query against an MS-SQL database. The second two (serviceResponse.Tables(0) and serviceResponse.Tables(1) ) are built using DataSet.ReadXML from the results of a web service.

I've gotten this far:

Dim joinedData = From f In localSQLTable _
                 Join s1 As DataRow In serviceResponse.Tables(0) _
                 On f.Item("KNum") Equals s1.Item("Number") _
                 Join s2 As DataRow In serviceResponse.Tables(1) _
                 On s1.Item("KNumber_Id") Equals s2.Item("KNumber_Id") _
                 Select Guid = f.Item("Guid"), Num = f.Item("Num"), Desc = f.Item("Desc"), KNum = f.Item("KNum"), KDesc = s2.Item("KDescription_Text"), Type = s2.Item("Type") _
                 Where (Type.ToString.ToUpper = "LONG_HTML")

myGridView.DataSource = joinedData
myGridView.DataBind()

However, it seemed joinedData is just an IEnumerable (of anonymous type). I've tried a few things, including the following:

• Attempting to build joinedData as an IEnumerable(Of DataRow) using a lambda function (of which I am not familiar at all) to build the new DataRow
• Calling .ToList() or .AsEnumerable() (after toying with types) on the result set

The main issue is that no matter what I seem to try, there's something wrong with using the results as the DataSource for my GridView - I got one of the two exceptions:

• The data source for GridView with id did not have any properties or attributes from which to generate columns. Ensure that your data source has content.
• The data source does not support server-side data paging.

I also know I probably shouldn't be using .Item ("Field") instead of the strongly-typed .Field (Of T)("Field") in my Linq query - I was waiting on that change until I've got the data actually usable.

I'm not married to Linq; if DataSet.Merge is more appropriate (or some other methodology), I'll entertain it. There's also a distinct possibility that I'll actually have to join what I have to another two DataTables later. If that's the case, I'll likely merge the serviceResponse tables into one, so I'll still only be joining three tables.

So what can I do to join this data together and use the result as my GridView's DataSource? And is anything I'm doing going to be any faster than just tacking on two extra columns in my original DataTable (localSQLTable) and filling them row-by-row using the XML response data?

Answer 1

The end result of all of this is the following:

Dim joinedData As Generic.IEnumerable(Of DataRow) = (From f In localSQLTable.AsEnumerable() _
                                                     Join h In serviceResponse.Tables(0).AsEnumerable() _
                                                     On h.Item("serviceknum") Equals f.Item("knum") _
                                                     Select GetFinalDataRow(finalTable, f, h))

gvGridOne.DataSource = joinedData.CopyToDataTable()
gvGridOne.DataBind()

I explicitly defined the DataTable and schema (by adding DataColumns) of the ending DataTable I need, then passed that DataTable and the two DataRows in my join to GetFinalDataRow(), defined as:

Public Function GetFinalDataRow(ByRef FinalTable As DataTable, ByVal Row1 As DataRow, ByVal Row2 As DataRow) As DataRow
    Dim newRow As DataRow = FinalTable.NewRow()
    For Each col As DataColumn In FinalTable.Columns
        If Row1.Table.Columns.Contains(col.ColumnName) Then
            newRow(col.ColumnName) = Row1.Item(col.ColumnName)
        ElseIf Row2.Table.Columns.Contains(col.ColumnName) Then
            newRow(col.ColumnName) = Row2.Item(col.ColumnName)
        Else
            newRow(col.ColumnName) = ""
        End If
    Next

    Return newRow
End Function

My joinedData object is now an IEnumerable of DataRows, and I can copy that to a DataTable for my GridView's datasource, and it will auto generate columns and allow paging.