Click here to Skip to main content
15,945,603 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hi all
I am trying to convert a result that I got from my entity model using LINQ to a datatable whithout looping the result and creating the data table records one by one.

Are you looking for this
' trace array is a collection of traces
Dim results = From trace In traceArray Where trace.Source = "blah"
dataGridView1.AutoGenerateColumns = True
dataGridView1.DataSource = results.ToList()
Share this answer
Well, I am not exactly an Entity Framework expert, but I know that sorting in a Winforms DataGridView with the "Entity Queries" is not as easy as when using DataTables. So I decide to create a common function to convert "Entity Queries" in DataTables.
In this function I am using the Reflection namespace to support this kind of work.
So far, in terms of conversion, not had any problems with this routine, I'm listing below these comments.
But I must warn you that by using this function, you can lose (and will probably lose) a little performance, especially when the queries return large data sets.
The code is in VB.Net, and is quite simple, actually it is to convert data almost by brute force. It is inelegant, uncommented, I would say rough, but it has worked relatively well.
Adopt it, with appropriate changes in the customization of an "Entity", an idea may be acceptable.

A sample can be something like this:


Me.DataGridView1.DataSource = EQToDataTable((
                                        From c As Logradouros In ctx.Logradouros
                                            c.Localidades.Localidade.ToUpper = (Nome.Text.Trim.ToUpper) Or
                                            c.CEP.ToUpper = (Nome.Text.Trim.ToUpper) Or
                                            c.Logradouro.ToUpper = (Nome.Text.Trim.ToUpper)
                                        Select [UF] = c.UFs.Nome,
                                               [Localidade] = c.Localidades.Localidade,
                                               [Bairro_inicial] = c.Bairros.Bairro,
                                               [Bairro_final] = c.Bairros1.Bairro,
                                               [Tipo] = c.Logradouros_Tipos.Nome,
                                               [Logradouro] = c.Abreviado,
                                               [Complemento] = c.Complemento,
                                               [CEP] = c.CEP
                                        Order By UF,


And finally, the code.

Public Function EQToDataTable(ByVal parIList As System.Collections.IEnumerable) As System.Data.DataTable
    Dim ret As New System.Data.DataTable()
        Dim ppi As System.Reflection.PropertyInfo() = Nothing
        If parIList Is Nothing Then Return ret
        For Each itm In parIList
            If ppi Is Nothing Then
                ppi = DirectCast(itm.[GetType](), System.Type).GetProperties()
                For Each pi As System.Reflection.PropertyInfo In ppi
                    Dim colType As System.Type = pi.PropertyType
                    If (colType.IsGenericType) AndAlso
                       (colType.GetGenericTypeDefinition() Is GetType(System.Nullable(Of ))) Then colType = colType.GetGenericArguments()(0)
                    ret.Columns.Add(New System.Data.DataColumn(pi.Name, colType))
            End If
            Dim dr As System.Data.DataRow = ret.NewRow
            For Each pi As System.Reflection.PropertyInfo In ppi
                dr(pi.Name) = If(pi.GetValue(itm, Nothing) Is Nothing, DBNull.Value, pi.GetValue(itm, Nothing))
        For Each c As System.Data.DataColumn In ret.Columns
            c.ColumnName = c.ColumnName.Replace("_", " ")
    Catch ex As Exception
        ret = New System.Data.DataTable()
    End Try
    Return ret
End Function

I hope this can help you. And excuse me for my poor English.
Share this answer
1st thanks.
no I have some UI methods that take a datatable as argument so I want to Query my DB using Entity framework then convert the result to Datatable.
Share this answer
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900