LINQ到实体查询数据表 [英] LINQ to Entities query to DataTable

问题描述

我需要存储的数据从这个LINQ到实体查询（见下文）返回到数据表，这样我可以使用它作为数据源到一个DataGridView，我该怎么办呢？

I need to store the data returned from this LINQ to Entities query (below) into a DataTable so that I can use it as data source to a DataGridView, how can I do that?

在这种情况下，我使用LINQ到实体查询针对实体框架概念模型，所以分贝是从<$ C $继承的类C> System.Data.Entity.DbContext 。

In this case I'm using LINQ to Entities to query against an Entity Framework conceptual model, so db is a class that inherits from System.Data.Entity.DbContext.

using (TccContext db = new TccContext())
{
    var query = from vendedor in db.Vendedores.AsEnumerable()
                where vendedor.codigo == Convert.ToInt32(textBoxPesquisa.Text)
                select vendedor;
    // I'd like to do something like DataTable dt = query;
}

我试图做到这一点（见下文），但它抛出一个异常在执行过程中[1]。

I've tried to do this (below), but it throws an exception during execution [1].

using (TccContext db = new TccContext())
{
    IEnumerable<DataRow> query = (IEnumerable<DataRow>)(from vendedor in db.Vendedores.AsEnumerable()
                                                        where vendedor.codigo == Convert.ToInt32(textBoxPesquisa.Text)
                                                        select vendedor);

    using (DataTable dt = query.CopyToDataTable<DataRow>())
    {
        this.dataGridViewProcura.Rows.Add(
            dt.Rows[0][0],  // Código
            dt.Rows[0][1],  // Nome
            dt.Rows[0][2]); // Venda Mensal
    }
}

[1]：异常： InvalidCastException的

Unable to cast object of type 'WhereEnumerableIterator`1[Projeto_TCC.Models.Vendedor]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

在此先感谢

Thanks in advance

推荐答案

有一个重要的事情在这里，你投你的LINQ查询（的IEnumerable< D​​ataRow的> ）当您选择了 vendedor ，所以我认为vendedor是 vendedor ，让您的查询将返回的IEnumerable<的实例; vendedor>

There is one important thing here, you are casting your Linq query to (IEnumerable<DataRow>) when you are selecting the vendedor, so I assume that vendedor is an instance of Vendedor, so your query will return an IEnumerable<Vendedor>

这应该解决您的问题，而且还可以尝试使用生成的数据表作为数据源为您的DataGridView？这将是这样的：

That should solve your problem, but also, can you try using the generated DataTable as the DataSource for your DataGridView? It would be something like this:

var query = (from vendedor in db.Vendedores.AsEnumerable()
                   where vendedor.codigo == Convert.ToInt32(textBoxPesquisa.Text)
                   select vendedor);
var dt = query.CopyToDataTable<Vendedor>();
this.dataGridViewProcura.DataSource = dt;

希望我能帮助！

Hope I can help!

修改

作为一个方面（和非常个人化）注意，你可以尝试使用你选择的lambda表达式，它们看起来更漂亮：）

As a side (and very personal) note, you could try using lambdas on your select, they look prettier :)

var pesquisa = Convert.ToInt32(textBoxPesquisa.Text);
var query = db.Vendedores.Where(vendedor => vendedor.codigo == pesquisa);

var dt = query.CopyToDataTable<Vendedor>();
this.dataGridViewProcura.DataSource = dt;

很多吸尘器，你不觉得？

A lot cleaner, don't you think?

编辑2
我刚刚意识到你正在CopyToDataTable为DataRow中所说的话而已，所以最后的（当然不那么干净）的解决办法是模仿的帮手逻辑？

EDIT 2 I've just realized what you said on CopyToDataTable being for DataRow only, so last (admittedly not so clean) solution would be to mimic the logic on the helper?

public DataTable CopyGenericToDataTable<T>(this IEnumerable<T> items)
{
    var properties = typeof(T).GetProperties();
    var result = new DataTable();

    //Build the columns
    foreach ( var prop in properties ) {
        result.Columns.Add(prop.Name, prop.PropertyType);
    }

    //Fill the DataTable
    foreach( var item in items ){
        var row = result.NewRow();

        foreach ( var prop in properties ) {
            var itemValue = prop.GetValue(item, new object[] {});
            row[prop.Name] = itemValue;
        }

        result.Rows.Add(row);
    }

    return result;
}

现在，需要考虑的事情：

• 此解决方案的不会与复杂性


• 自定义生成的表的工作可能有点棘手

• This solution will not work with complex properties
• Customizing the resulting table might be a bit tricky

虽然这可能会解决这个问题，我不认为这是一个很好的方法，但它可能是一个体面的想法的开始！）

While this might solve the issue, I don't think this is a very good approach, but it could be the start of a decent idea :)

我希望能够帮助这个时间

I hope I can help this time!

这篇关于LINQ到实体查询数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！