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.