使用 DataContext 从 LINQ 查询填充 DataTable 的最快方法 [英] Fastest way to fill DataTable from LINQ query using DataContext

问题描述

我正在尝试运行 linq 查询，但我需要将结果作为数据表，因为我使用它来将来自不同查询的记录存储在同一视图状态对象中.

I am trying to run a linq query but I need the result as a datatable as I am using that to store records from different queries in the same viewstate object.

下面的 2 个版本可以编译，但返回一个空集.确切的错误是 值不能为空.参数名称:source".(是的，我已经检查过有数据):

The 2 versions below compile, but return an empty set. The exact error is "Value cannot be null. Parameter name: source". (and yes I have checked there is data):

MyDatabaseDataContext db = new MyDatabaseDataContext(conn); 
IEnumerable<DataRow> queryProjects = 
    (from DataRow p in db.STREAM_PROJECTs.AsEnumerable()
    where p.Field<int>("STREAM_ID") == StreamID
    select new
    {
        PROJECT_ID = p.Field<int>("PROJECT_ID"),
        PROJECT_NAME = p.Field<string>("PROJECT_NAME")
    }) as IEnumerable<DataRow>;
DataTable results = queryProjects.CopyToDataTable<DataRow>();

...

//(from p in db.STREAM_PROJECTs.AsEnumerable()
//where p.STREAM_ID == StreamID
//select new
//{
//    p.PROJECT_NAME,
//    p.PROJECT_ID
//}) as IEnumerable<DataRow>;

此线程 在这种情况下似乎也不起作用.

The examples in this thread don't seem to work in this situation either.

我想我可以用老式的方式运行 sql 查询命令，但 linq 不是应该更快吗?

I guess I could just run a sql query command the old-fashioned way, but isn't linq supposed to be quicker?

推荐答案

你的问题是:

as IEnumerable<DataRow>

as 关键字执行安全转换，而不是转换，您可能认为它正在执行转换.as 关键字在语义上与这样做相同:

The as keyword performs a safe cast, not a conversion, which it seems like you might think that it's doing. The as keyword is semantically the same as doing this:

IEnumerable<DataRow> queryProjects = 
    (IEnumerable<DataRow>)(from DataRow p in db.STREAM_PROJECTs.AsEnumerable()
    where p.Field<int>("STREAM_ID") == StreamID
    select new
    {
        PROJECT_ID = p.Field<int>("PROJECT_ID"),
        PROJECT_NAME = p.Field<int>("PROJECT_NAME")
    });

除了带有 as 的版本，当它无法转换您的查询对象(这是一个 IQueryable，其中 T 是一个匿名类型)到 IEnumerable(它不是).

Except the version with as won't throw an exception when it fails to cast your query object (which is an IQueryable<T>, where T is an anonymous type) to an IEnumerable<DataRow> (which it isn't).

不幸的是，我知道没有内置方法可以将具体类型的可枚举(例如本示例中的匿名类型)转换为 DataTable.编写一个不会太复杂，因为您基本上需要反射性地获取属性，然后遍历集合并使用这些属性在 DataTable 中创建列.稍后我会发布一个示例.

Unfortunately, there is no built-in method that I'm aware of that will take an enumerable of a concrete type (like your anonymous type in this example) and turn it into a DataTable. Writing one wouldn't be too complicated, as you'd essentially need to get the properties reflectively then iterate over the collection and use those properties to create columns in a DataTable. I'll post an example in a few.

这样的东西，放置在您使用的命名空间内的静态类中，应该提供一个可以做您想做的事情的扩展方法:

Something like this, placed in a static class within a namespace that you're using, should provide an extension method that will do what you want:

public static DataTable ToDataTable<T>(this IEnumerable<T> source)
{
    PropertyInfo[] properties = typeof(T).GetProperties();

    DataTable output = new DataTable();

    foreach(var prop in properties)
    {
        output.Columns.Add(prop.Name, prop.PropertyType);
    }

    foreach(var item in source)
    {
        DataRow row = output.NewRow();

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

        output.Rows.Add(row);
    }

    return output;
}

这篇关于使用 DataContext 从 LINQ 查询填充 DataTable 的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！