Linq To DataTable Joins

VbNet · Console

/Program.vb

Imports System
Imports System.Data
Imports System.Xml
Imports System.Linq
Imports System.Data.Linq
Imports System.Data.DataSetExtensions
Imports System.Runtime.CompilerServices
Imports System.Reflection
Imports System.Collections.Generic

Public Module Module1
	Public Sub Main()
		
		' create people
		Dim people as New DataTable
		people.Columns.Add("ID", GetType(Integer))
		people.Columns.Add("Name", GetType(String))
		people.Rows.Add(1,"Bob")
		people.Rows.Add(2,"Susan")
		
		
		' create extended info
		Dim extendedInfo as New DataTable
		extendedInfo.Columns.Add("PersonID", GetType(Integer))
		extendedInfo.Columns.Add("SSN", GetType(String))
		extendedInfo.Rows.Add(1,"555-55-5555")
		extendedInfo.Rows.Add(2,"XXX-XX-XXXX")
		
		' linq query
		Dim query = From person In people.AsEnumerable
			Join extra In extendedInfo.AsEnumerable()
			On person("ID") Equals extra("PersonID")
			Select New With {
					.ID = person("ID"),
					.Name = person("Name"),
					.SSN = extra("SSN")
			}
		
        Dim newTable = query.CopyToDataTable()

        ' return results
        For Each row In newTable.AsEnumerable()
			Console.WriteLine("ID: "   & row("ID") & " / " &
								"Name: " & row("Name") & " / " &
								"SSN: "  & row("SSN"))
        Next

		
		
	End Sub
End Module



''' <summary>
''' How to: Implement CopyToDataTable<T> Where the Generic Type T Is Not a DataRow
''' </summary>
''' <remarks>
''' https://msdn.microsoft.com/en-us/library/bb669096.aspx
''' </remarks>
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

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.
    ''' </summary>
    ''' <param name="source">The sequence of objects to load into the DataTable.</param>
    ''' <param name="table">
    ''' 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.
    ''' </param>
    ''' <param name="options">
    ''' Specifies how values from the source sequence will be applied to 
    ''' existing rows in the table.
    ''' </param>
    ''' <returns>A DataTable created from the source sequence</returns>
    ''' <remarks></remarks>
    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 the 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 the table.
        Return table
    End Function

    ''' <summary>
    ''' Extend the table schema if the input table was null or if the value in the sequence is derived from type T
    ''' </summary>
    Public Function ExtendTable(ByVal table As DataTable, ByVal type As Type) As DataTable
        Dim f As FieldInfo
        Dim p As PropertyInfo

        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 = If(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 Me._ordinalMap.ContainsKey(p.Name) Then
                ' Add the property as a column in the table if it doesn't exist already.
                Dim dc As DataColumn
                dc = If(table.Columns.Contains(p.Name),
                        table.Columns.Item(p.Name),
                        table.Columns.Add(p.Name, p.PropertyType))

                ' Add the property to the ordinal map.
                Me._ordinalMap.Add(p.Name, dc.Ordinal)
            End If
        Next

        ' Return the table.
        Return table
    End Function

End Class
;