最快的方法使用的DataContext从LINQ查询数据表填写 [英] 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.以下版本编译,但返回一个空集。确切的错误是的值不能为空。
参数名:源的(是的,我已经检查有数据):
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>;
在此<一个例子href=\"http://stackoverflow.com/questions/16/how-do-i-fill-a-dataset-or-a-datatable-from-a-linq-query-resultset\">thread似乎并没有在这种情况下工作无论是。
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>
的为
关键字进行安全投,而不是一个转换,这好像你可能会认为它在做什么。在为
关键词语义相同,这样做的:
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")
});
除非的版本
时,它不能投你的查询对象(这是一个的IQueryable&LT不会抛出异常; T&GT;
,其中 T
是一个匿名类型)到的IEnumerable&LT; DataRow的&GT;
(它ISN 'T)。
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).
不幸的是,没有内置的方法,我所知道的,将采取具体类型(如在本例中您的匿名类型)的枚举,并把它变成一个数据表
。写作人会不会太复杂,因为你基本上是需要反思地获取属性,然后遍历集合,并使用这些属性在数据表
以创建列。我会在几分钟后一个例子。
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查询数据表填写的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!