LINQ to DataTable

Question

I have big DataTable I want to get subset of this DataTable represented as DataTable either. Briefly saying how do I select particular columns in DataTable.

I was trying something like this but it doesn't work...

DataTable dTable = new DataTable(); ... ... ... DataTable dt = from field in dTable where field.Field<string>("Manager") where field.Field<string>("Phone") select field;

Maybe my code is wrong, how do I get "managers" and "Phone" columns from one DataTable to another without looping thought it?

Would you say what's your purpose to help you, by your current code I can't understand what you want (two nested where?) — Saeed Amiri
– Saeed Amiri, Commented Apr 30, 2012 at 10:51
Maybe my code is wrong, how do I get "managers" and "Phone" columns from one DataTable to another without looping thought it? — Wild Goat
– Wild Goat, Commented Apr 30, 2012 at 10:52

Kris Krause · Accepted Answer · 2012-04-30 11:21:21Z

Reference the DataTableExtensions -

http://msdn.microsoft.com/en-us/library/system.data.datatableextensions.asenumerable.aspx

Then...

var whatever = dTable.AsEnumerable();

Then per the MSDN example...

var productNames = from products in table.AsEnumerable() select products.Field<string>("ProductName");

Edit/update: Unfortunately I do not think there is a built in direct cast back to a DataTable with a different schema. You have to use a DataTable? I believe it... as it might be too much effort to refactor and test your code. Good luck and keep us posted as working with strongly-typed lists are much more fun.

@WildGoat: CopyToDataTable is what you're looking for. Unfortunately the resulting DataTable must have the same schema(fields) as the original DataTable. So in short: it's not possible. The long version which also works with any kind of anonymous types: stackoverflow.com/a/9259348/284240

Matt · Accepted Answer · 2012-04-30 10:56:20Z

You could write:

 var query = from row in dTable.AsEnumerable() select new { manager = row.Field<string>("Manager"), phone = row.Field<string>("Phone") };

Bill the Lizard · Accepted Answer · 2012-07-27 11:44:45Z

You can execute LINQ query on DataTable Or Specific table on DataSet with the help of the AsEnumerable.

Here is example this might be helpful.

 DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataColumn dc; DataRow dr; ds.DataSetName = "products"; dt.TableName = "product"; dc = new DataColumn("product_id",long.MaxValue.GetType()); dt.Columns.Add(dc); dc = new DataColumn("product_name"); dt.Columns.Add(dc); dr = dt.NewRow(); dr["product_id"] = 1; dr["product_name"] = "Monitor"; dt.Rows.Add(dr); dr = dt.NewRow(); dr["product_id"] = 2; dr["product_name"] = "Mouse"; dt.Rows.Add(dr); dr = dt.NewRow(); dr["product_id"] = 3; dr["product_name"] = "KeyBoard"; dt.Rows.Add(dr); dr = dt.NewRow(); dr["product_id"] = 4; dr["product_name"] = "LCD"; dt.Rows.Add(dr); ds.Tables.Add(dt); IEnumerable<DataRow> objResult1 = from tbl in dt.AsEnumerable() where tbl.Field<long>(0) <= 2 select tbl; Response.Write("<b>Query Results 1</b>"); foreach (DataRow row in objResult1) { Response.Write(string.Format("<br/>Product ID: {0} , Product Name: {1}", row.Field<long>(0), row.Field<string>(1))); } IEnumerable<DataRow> objResult2 = from tbl in ds.Tables[0].AsEnumerable() let product_name = tbl.Field<string>(1) where product_name.StartsWith("Key") || product_name.StartsWith("Mo") select tbl; Response.Write("<br/><br/><b>Query Results 2</b>"); foreach (DataRow row in objResult2) { Response.Write(string.Format("<br/>Product ID: {0} , Product Name: {1}", row.Field<long>(0), row.Field<string>(1))); }

Collectives™ on Stack Overflow

LINQ to DataTable

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related