Convert LINQ to Entity Result to a DataTable

Introduction

This article helps converting LINQ to Entity result to a data table. This can be very useful if you are moving to entity framework and still want to support existing methods, write a normal Linq to entity syntax, then with a simple extension method convert it to a data table. Note that the Query is executed once on the server and there is no need to loop the result and create the data table which takes too much time.

Using the code

These two extension methods help in getting the data table directly form the result.

Imports System.Data.Objects 
Imports System.Linq.Expressions
Imports System.Data.SqlClient

//Public Module EntityToDataTable

//<extension()> _
Public Function EntityToDatatable(ByVal Result As IQueryable, ByVal Ctx As  ObjectContext) As DataTable
    Try
        Using SQLCon As New SqlConnection(CType(Ctx.Connection, EntityConnection).StoreConnection.ConnectionString)
            Using Cmd As New SqlCommand(CType(Result, ObjectQuery).ToTraceString, SQLCon)
                For Each Param As ObjectParameter In CType(Result, ObjectQuery).Parameters
                    Cmd.Parameters.AddWithValue(Param.Name, Param.Value)
                Next
                Using DA As New SqlDataAdapter(Cmd)
                    Using DT As New DataTable
                        DA.Fill(DT)
                        Return DT
                    End Using
                End Using
            End Using
        End Using
    Catch
        Throw
    End Try
End Function

//<extension()> _
Public Sub EntityToDatatable(ByVal Result As IQueryable, ByVal Ctx As ObjectContext, ByVal DT As DataTable)
     Try
         Using SQLCon As New SqlConnection(CType(Ctx.Connection, EntityConnection).StoreConnection.ConnectionString)
             Using Cmd As New SqlCommand(CType(Result, ObjectQuery).ToTraceString, SQLCon)
                 For Each Param As ObjectParameter In CType(Result, ObjectQuery).Parameters
                     Cmd.Parameters.AddWithValue(Param.Name, Param.Value)
                 Next
                 Using DA As New SqlDataAdapter(Cmd)
                     DA.Fill(DT)
                 End Using
             End Using
         End Using
     Catch
         Throw
     End Try
 End Sub

  //End Module          

Hope it helps.

Development to me is a pleasure more than a job.