It appears that a .CopyToDataTable class existed in the Linq Betas, but the
class was cut due to lack of resources. If you follow the link provided by
Patrice:
http://msdn2.microsoft.com/en-us/library/bb396189.aspx, you will
find the MSDN .CopyToDataTable documentation.
There is also another MSDN page that provides the .CopyToDataTable class
code (beta quality code, does not handle nullable types):
http://msdn2.microsoft.com/en-us/library/bb669096.aspx
At the end of this blog is the code needed to handle nullable types:
http://blogs.msdn.com/aconrad/archive/2008/01/11/pathetic-plea-for-help.aspx#7103127
Putting the pieces together and translating to VB, here is code that is
working for me. Caveat: it may be incomplete, but it does what I need it to
do thus far. Hopefully, MS will complete .CopyToDataTable in the next
release.
Calling code example:
Dim dt As New DataTable
Dim query = From tblAdminActivity In dc.tblAdminActivities _
Order By tblAdminActivity.ActivityID
dt = query.CopyToDataTable()
Insert into a module:
Imports System.Runtime.CompilerServices
Public Module CustomLINQtoDataSetMethods
<Extension()> _
Public Function CopyToDataTable(Of T)(ByVal source As IEnumerable(Of T))
As DataTable
Return New ObjectShredder(Of T)().Shred(source, Nothing, Nothing)
End Function
<Extension()> _
Public Function CopyToDataTable(Of T)(ByVal source As IEnumerable(Of T),
_
ByVal table As DataTable, _
ByVal options As LoadOption?) As
DataTable
Return New ObjectShredder(Of T)().Shred(source, table, options)
End Function
End Module
Create this class:
Option Strict Off
Option Explicit On
Imports System.Reflection
Public Class clsCopyToTable
Public Class ObjectShredder(Of T)
'fields
Private _fi As FieldInfo()
Private _ordinalMap As Dictionary(Of String, Integer)
Private _pi As PropertyInfo()
Private _type As Type
'constructor
Public Sub New()
Me._type = GetType(T)
Me._fi = Me._type.GetFields
Me._pi = Me._type.GetProperties
Me._ordinalMap = New Dictionary(Of String, Integer)
End Sub
Public Function ShredObject(ByVal table As DataTable, _
ByVal instance As T) As Object()
Dim fi As FieldInfo() = Me._fi
Dim pi As PropertyInfo() = Me._pi
If (Not instance.GetType Is GetType(T)) Then
'if the instance is derived from T, extend the table schema
'and get the properties and fields.
Me.ExtendTable(table, instance.GetType)
fi = instance.GetType.GetFields
pi = instance.GetType.GetProperties
End If
'add the property and field values of the instance to an array.
Dim values As Object() = New Object(table.Columns.Count - 1) {}
Dim f As FieldInfo
For Each f In fi
values(Me._ordinalMap.Item(f.Name)) = f.GetValue(instance)
Next
Dim p As PropertyInfo
For Each p In pi
values(Me._ordinalMap.Item(p.Name)) = p.GetValue(instance,
Nothing)
Next
'return the property and field values of the instance.
Return values
End Function
' Summary: Loads a DataTable from a sequence of objects.
' source parameter: The sequence of objects to load into the
DataTable.</param>
' table parameter: The input table. The schema of the table must
match that
' the type T. If the table is null, a new table is
created
' with a schema created from the public properties
and fields
' of the type T.
' options parameter: Specifies how values from the source sequence
will be applied to
' existing rows in the table.
' Returns: A DataTable created from the source sequence.
Public Function Shred(ByVal source As IEnumerable(Of T), _
ByVal table As DataTable, _
ByVal options As LoadOption?) As DataTable
'load the table from the scalar sequence if T is a primitive type.
If GetType(T).IsPrimitive Then
Return Me.ShredPrimitive(source, table, options)
End If
'create a new table if the input table is null.
If (table Is Nothing) Then
table = New DataTable(GetType(T).Name)
End If
'initialize the ordinal map and extend the table schema based on
type T.
table = Me.ExtendTable(table, GetType(T))
'enumerate the source sequence and load the object values into
rows.
table.BeginLoadData()
Using e As IEnumerator(Of T) = source.GetEnumerator
Do While e.MoveNext
If options.HasValue Then
table.LoadDataRow(Me.ShredObject(table, e.Current),
options.Value)
Else
table.LoadDataRow(Me.ShredObject(table, e.Current), True)
End If
Loop
End Using
table.EndLoadData()
'return table.
Return table
End Function
Public Function ShredPrimitive(ByVal source As IEnumerable(Of T), _
ByVal table As DataTable, _
ByVal options As LoadOption?) As
DataTable
'create a new table if the input table is null.
If (table Is Nothing) Then
table = New DataTable(GetType(T).Name)
End If
If Not table.Columns.Contains("Value") Then
table.Columns.Add("Value", GetType(T))
End If
'enumerate the source sequence and load the scalar values into
rows.
table.BeginLoadData()
Using e As IEnumerator(Of T) = source.GetEnumerator
Dim values As Object() = New Object(table.Columns.Count - 1) {}
Do While e.MoveNext
values(table.Columns.Item("Value").Ordinal) = e.Current
If options.HasValue Then
table.LoadDataRow(values, options.Value)
Else
table.LoadDataRow(values, True)
End If
Loop
End Using
table.EndLoadData()
'return table.
Return table
End Function
Public Function ExtendTable(ByVal table As DataTable, _
ByVal type As Type) As DataTable
'extend the table schema if the input table was null or if the
value
'in the sequence is derived from type T.
Dim f As FieldInfo
Dim p As PropertyInfo
Try
For Each f In type.GetFields
If Not Me._ordinalMap.ContainsKey(f.Name) Then
Dim dc As DataColumn
'add the field as a column in the table if it doesn't
exist already.
dc = IIf(table.Columns.Contains(f.Name),
table.Columns.Item(f.Name), table.Columns.Add(f.Name, f.FieldType))
'add the field to the ordinal map.
Me._ordinalMap.Add(f.Name, dc.Ordinal)
End If
Next
For Each p In type.GetProperties()
If Not _ordinalMap.ContainsKey(p.Name) Then
'add the property as a column in the table if it doesn't
exist already.
Dim colType As Type = p.PropertyType
If (colType.IsGenericType) _
AndAlso (colType.GetGenericTypeDefinition() Is
GetType(Nullable(Of ))) Then
colType = colType.GetGenericArguments()(0)
End If
Dim dc As DataColumn
dc = IIf(table.Columns.Contains(p.Name),
table.Columns(p.Name), table.Columns.Add(p.Name, colType))
Me._ordinalMap.Add(p.Name, dc.Ordinal)
End If
Next
Catch exc As Exception
'your exception handler
Finally
End Try
'return table
Return table
End Function
End Class
End Class
