将实体框架结果转换为数据表 [英] Converting Entity framework result to datatable

问题描述

大家好
我正在尝试将使用LINQ从我的实体模型获得的结果转换为数据表，而无需循环结果并创建数据表记录. 谢谢.

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.
thanks.

推荐答案

您是否在寻找这个

Hi,
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()
dataGridView1.AutoResizeColumns()
dataGridView1.Refresh()

好吧，我并不是完全是实体框架专家，但是我知道在带有实体查询"的Winforms DataGridView中进行排序并不容易就像使用DataTables一样.因此，我决定创建一个通用函数来转换DataTables中的实体查询".
在此函数中，我正在使用Reflection名称空间来支持此类工作.
到目前为止，在转换方面，此例程没有任何问题，我在这些注释下方列出.
但是我必须警告您，使用此功能可能会损失(可能会损失)一些性能，尤其是在查询返回大数据集时.
该代码在VB.Net中，并且非常简单，实际上它几乎是通过蛮力来转换数据.我想说这很不雅观，没有评论，但它的效果相对不错.
通过采用它，并适当更改实体"的自定义，可以接受一个想法.

样本可以是这样的:

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
                                        Where
                                            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,
                                                 Localidade,
                                                 Bairro_inicial,
                                                 Logradouro,
                                                 CEP
                                    ).ToList).DefaultView

...

最后是代码.

And finally, the code.

Public Function EQToDataTable(ByVal parIList As System.Collections.IEnumerable) As System.Data.DataTable
    Dim ret As New System.Data.DataTable()
    Try
        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))
                Next
            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))
            Next
            ret.Rows.Add(dr)
        Next
        For Each c As System.Data.DataColumn In ret.Columns
            c.ColumnName = c.ColumnName.Replace("_", " ")
        Next
    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.

第一次谢谢.
否，我有一些采用数据表作为参数的UI方法，因此我想使用实体框架查询数据库，然后将结果转换为数据表.

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.

这篇关于将实体框架结果转换为数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！