LINQ is incredibly powerful. I really hope you are using every day. Do you remember I published a 3-parts article on that subject available from here: part 1, part 2, and part 3.

But even if LINQ is incredible, there are times when you would like to get the results of one of your LINQ query and transform it into an ADO.Net DataTable. I had this requirement lately when I wanted to pass some data to one of my report (yeah still using Crystal Reports).

LINQ-to-Datasets offers a CopyToDatatable method but not the other form of LINQ-to-X. This is what I needed to fix!

Downloadable code

The code this month is available in both VB and C#. It has been created using Visual Studio 2013 but it can be used in older versions as well (as long as LINQ is supported).

Reflection is your friend

When I started looking around the web to find a way of achieving my goal, I found mainly 2 ways of converting a LINQ result to a DataTable: Reflection and some other methods specialized to convert an Entity Framework entity.

What I don’t like about the second method is that it is limited to EF entities and it requires a reference to the EF package (which is not required in this scenario here).

So I preferred the first method which is using reflection to convert the content of our LINQ query. Back in 2005, I wrote an article on Reflection.

Reflection is used here to convert an IEnumerable object list to a DataTable. The method treats each object of the list as a DataRow. It also dynamically retrieves the properties and the column names.

Most solutions fall short!

All of the solutions I found on the web are falling short in some aspect. They are all able to convert structures of properties but not plain old members (public variables).

I decided to pull off my sleeves and adjust the code to fit my requirements. The final code is even an extension method so it makes it even easier to use.

What is an example of a list I want to convert?

Simple any list returned by LINQ-to-anything!

Here is an example of a query returning all the properties of all the files in the startup path:

Dim listOfFiles = (New IO.DirectoryInfo(Application.StartupPath)).GetFiles()

The same list as here above but with a limited set of columns:

Dim listOfFiles = From file In (New IO.DirectoryInfo(Application.StartupPath)).GetFiles()
                  Order By file.Name
                  Select file.Name, file.Length, file.CreationTime, file.Extension

A last example (because by now, I am sure you understand where I am heading to):

Dim listOfEmployees As IEnumerable(Of Employee) = GetEmployees()

Figure 1: The demo application in action

The missing conversion method

What I want is to be able to take any of these 3 lists, pass it to a method that will build a DataTable for me (columns and content).

I have named this method ObjectQuerytoDataTable and I placed it in a module to be able to create an extension method from it. It reads as follows:

<Runtime.CompilerServices.Extension()>
Public Function ObjectQueryToDataTable(Of T)(objlist As IEnumerable(Of T)) As DataTable
    Dim dtReturn As New DataTable()

    'if the input list is null, return an empty table
    If objlist Is Nothing Then Return dtReturn

    Dim objProps As PropertyInfo() = Nothing
    Dim objFields As FieldInfo() = Nothing

    For Each objRec As T In objlist
        If objProps Is Nothing Then
            'loops through PROPERTIES to create columns
            objProps = objRec.[GetType]().GetProperties()
            CreateColumnsFromProperties(dtReturn, objProps)
        End If

        If objFields Is Nothing Then
            'loops through FIELDS to create columns
            objFields = objRec.[GetType]().GetFields()
            CreateColumnsFromFields(dtReturn, objFields)
        End If

        'create an empty row
        Dim dr As DataRow = dtReturn.NewRow()

        'Fill the columns of the row
        For Each pi As PropertyInfo In objProps
            dr(pi.Name) = If(pi.GetValue(objRec, Nothing) Is Nothing, DBNull.Value, pi.GetValue(objRec, Nothing))
        Next
        For Each pi As FieldInfo In objFields
            dr(pi.Name) = If(pi.GetValue(objRec) Is Nothing, DBNull.Value, pi.GetValue(objRec))
        Next

        'add the row to the DataTable
        dtReturn.Rows.Add(dr)
    Next

    'return the DataTable
    Return dtReturn
End Function

This method relies on 2 sub-methods that parse the properties and the fields to create the appropriate column:

Private Sub CreateColumnsFromFields(ByVal pDataTable As DataTable, ByRef pFields As FieldInfo())
    For Each objpi As FieldInfo In pFields
        Dim colType As Type = objpi.FieldType

        If (colType.IsGenericType) AndAlso (colType.GetGenericTypeDefinition() = GetType(Nullable(Of ))) Then
            colType = colType.GetGenericArguments()(0)
        End If

        pDataTable.Columns.Add(New DataColumn(objpi.Name, colType))
    Next
End Sub

Private Sub CreateColumnsFromProperties(ByVal pDataTable As DataTable, ByRef pProps As PropertyInfo())

    For Each objpi As PropertyInfo In pProps
        Dim colType As Type = objpi.PropertyType

        If (colType.IsGenericType) AndAlso (colType.GetGenericTypeDefinition() = GetType(Nullable(Of ))) Then
            colType = colType.GetGenericArguments()(0)
        End If

        pDataTable.Columns.Add(New DataColumn(objpi.Name, colType))
    Next
End Sub

Method usage

Now that we have that method that does the conversion from the results of a LINQ query to DataTable, we can then reuse the 3 samples from the beginning of the article and use it like this:

Dim dt As DataTable = listOfEmployees.ObjectQueryToDataTable()

Isn’t it nice? It will take all the properties and public fields of the listOfEmployees and convert it into a DataTable.

Conclusion

The solution is not always built into the .Net Framework but when you put all the pieces together, it is not overly complicated to build these missing parts yourself.