Linq Return grouped Table as Datatable

Question

I wrote a function that returns a DataTable Object. For the following code

Public Function GetArtikelsAbove50(ByVal limit As Integer) As DataTable
    Dim query = _Set.Ten_Most_Expensive_Products.AsEnumerable.Where(Function(x) x.UnitPrice > limit) _
    .GroupBy(Function(x) x.UnitPrice)
    Return query.CopyToDataTable
End Function

query is an System.Data.EnumerableRowCollection(Of SPTest.northwindDataSet.Ten_Most_Expensive_ProductsRow) Object. The code runs and everything is good. But if I add:

.GroupBy(Function(x) x.UnitPrice)

the compiler underlinies the return object (query.CopyToDataTable). query is now an System.Collections.Generic.IEnumerable(Of System.Linq.IGrouping(Of Decimal, SPTest.northwindDataSet.Ten_Most_Expensive_ProductsRow)) Object so CopyToDataTable is not a member of that anymore. Since I need the .GroupBy Function (some goes for .Sum)) I am looking for a way to use it in combination with the .CopyToDataTable. Is that possible and if so, how can I accomplish it?

I thought, that mybe returning a DataView Objeckt (with Return query.AsDataView) would work, but same error.

Hint: During my research I found that in this question a user mentioned that the function .CopyToDataTable "has been restricted to IEnumerable and does not work for IEnumerable(of T)". I am not so familiar with Datasets and I am not sure if this helps to solve the problem. Just wanted to share the information, maybe it is helpful.

Answer 1

The problem is that applying GroupBy changes what comes out of your expression. Your enumerable is now an IEnumerable of

 System.Linq.IGrouping(Of Decimal, SPTest.northwindDataSet.Ten_Most_Expensive_ProductsRow)

and not just of

 SPTest.northwindDataSet.Ten_Most_Expensive_ProductsRow

CopyToDataTable is an extension function on IEnumerable(of DataRow), so it isn't available for this new anonymous type.

As for what to do... well, to get the function back, you'd need to transform your new type into a DataRow or a subclass of DataRow.

For example, you could create a new table with your desired row structure in your DataSet (let's call it NewTable for this example), which will cause a new, strongly-typed class called NewTableRow to be generated.

You could then write a .Select statement to transform the System.Linq.IGrouping(Of Decimal, SPTest.northwindDataSet.Ten_Most_Expensive_ProductsRow) objects into NewTableRow objects.

The IEnumerable(Of SPTest.northwindDataSet.NewTableRow) instance that would result would have CopyToDataTable available again.

ETA: an example. Disclaimer: this was written in C# and auto-translated to VB, so there may be some quirks. But you should get the idea.

' We need an instance of the the target table to access the NewRow method
Dim newTable As New NewTable 
Dim newRows = query.[Select](Function(g) ' We're using an anonymous method here, but you could make it a regular method.
  'A quirk of DataTable & DataRow: you can't just "new" up a DataRow.  You have to use the
  'NewRow or generated, strongly-typed NewXXXXRow method to get a new DataRow.
  Dim newRow = newTable.NewNewTableRow() 
  ' You can now set the properties on your new strongly-typed DataRow.
  newRow.UnitPrice = g.Key   
  newRow.Quantity = g.Sum(Function(d) d.Quantity)
  ' Don't forget to return the result!
  Return newRow
End Function)

Or, you could use the AddNewTableRow method to create AND add your new row to your target table. You wouldn't even need to use CopyToDataTable!