LINQ Query to DataTable.DataSource
I am trying to perform a LINQ query on a DataTable and show the result in another DataTable. My source DataTable looks something like this:
DataTable myDataTable = new DataTable();
myDataTable.Columns.Add("OrderID", typeof(int));
myDataTable.Columns.Add("Date", typeof(DateTime));
myDataTable.Columns.Add("UnitsPurchased", typeof(int));
The resulting DataTable looks like this when filled:
Order ID Date Units Pur开发者_StackOverflowchased 16548 10/15/09 250 17984 11/03/09 512 20349 01/11/10 213 34872 01/15/10 175
My current LINQ query looks like this:
IEnumerable<DataRow> query = (from row in myDataTable.AsEnumerable()
where row.UnitsPurchased > 200
select new
{
row.OrderID,
row.Date,
row.UnitsPurchased
}) as IEnumerable<DataRow>;
resultDataTable.DataSource = query.CopyToDataTable<DataRow>();
Every time I run this code query is null. I can see that that the as IEnumerable<DataRow>
is the culprit, but it makes no since to me since DataTable.AsEnumerable()
returns an IEnumerable<DataRow>
. Any help would be appreciated.
When you select new { }, you're actually getting an IEnumerable<(Anonymous Type)>
, not IEnumerable<DataRow>
. So your as IEnumerable<DataRow>
will return null, since it can't be directly cast.
Either select new MyDataRow(constructor using values...)
or something, or just do var query =...
without the as
cast. There's an msdn article about using CopyToDataTable with a non-DataRow generic parameter, though I haven't read it in depth, but selecting new DataRows is probably the easier solution.
Why do you have to create a new Anonymous Type. When you can simply do this .
DataTable myDataTable = new DataTable();
myDataTable.Columns.Add("OrderID", typeof(int));
myDataTable.Columns.Add("Date", typeof(DateTime));
myDataTable.Columns.Add("UnitsPurchased", typeof(int));
var datarow1 = myDataTable.NewRow();
datarow1.SetField("OrderID", 16548);
datarow1.SetField("Date", DateTime.Parse("10/10/09"));
datarow1.SetField("UnitsPurchased", 250);
var datarow2 = myDataTable.NewRow();
datarow2.SetField("OrderID", 17984);
datarow2.SetField("Date", DateTime.Parse("11/03/09"));
datarow2.SetField("UnitsPurchased", 512);
var datarow3 = myDataTable.NewRow();
datarow3.SetField("OrderID", 20349);
datarow3.SetField("Date", DateTime.Parse("01/11/10"));
datarow3.SetField("UnitsPurchased", 213);
var datarow4 = myDataTable.NewRow();
datarow4.SetField("OrderID", 34872);
datarow4.SetField("Date", DateTime.Parse("10/01/10"));
datarow4.SetField("UnitsPurchased", 175);
myDataTable.Rows.Add(datarow1);
myDataTable.Rows.Add(datarow2);
myDataTable.Rows.Add(datarow3);
myDataTable.Rows.Add(datarow4);
var filteredTable = myDataTable.AsEnumerable().OfType<DataRow>().Where(row => row.Field<int>("UnitsPurchased") > 200).Select(r => r);
resultDataTable.DataSource = filteredTable.CopyToDataTable();
精彩评论