Click here to Skip to main content
Click here to Skip to main content
Go to top

Convert LINQ to Entity Result to a DataTable

, 22 Mar 2011
Rate this:
Please Sign up or sign in to vote.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mohammad Al Hoss
Software Developer CME Offshore
Lebanon Lebanon
No Biography provided

Comments and Discussions

 
QuestionHow Can Use This? Pinmemberr. salehi29-May-13 7:19 
AnswerRe: How Can Use This? PinmemberMohammad Al Hoss5-Jun-13 20:56 
GeneralMy vote of 5 PinmemberDean Henderson3-Jul-12 11:17 
GeneralReason for my vote of 5 very helpful.it makes my code run. Pinmemberawm00122-Mar-11 3:03 
GeneralNot extension methods PinmvpHenry Minute21-Mar-11 2:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140922.1 | Last Updated 22 Mar 2011
Article Copyright 2011 by Mohammad Al Hoss
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid