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, Apr 30 '12 at 10:51
possible duplicate of [Can we filter Datatable with LINQ?](http://stackoverflow.com/questions/5775762/can-we-filter-datatable-with-linq) — Roger Lipscombe, Apr 30 '12 at 10:52
Maybe my code is wrong, how do I get "managers" and "Phone" columns from one DataTable to another without looping thought it? — Wild Goat, Apr 30 '12 at 10:52

Kris Krause · Accepted Answer · 2012-04-30T11:21:21.593

6

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.

edited Apr 30 '12 at 11:21

answered Apr 30 '12 at 10:52

Kris Krause

7,304
2
23
26

Thanks Kris! But I have to get small DataTable as result. – Wild Goat Apr 30 '12 at 10:55
1

@WildGoat: [`CopyToDataTable`](http://msdn.microsoft.com/en-us/library/bb396189.aspx) 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: http://stackoverflow.com/a/9259348/284240 – Tim Schmelter Apr 30 '12 at 11:03

score 2 · Answer 2 · answered Apr 30 '12 at 10:56

2

You could write:

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

answered Apr 30 '12 at 10:56

Matt

14,353
5
53
65

score 1 · Answer 3 · edited Jul 27 '12 at 11:44

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)));
    }

LINQ to DataTable

3 Answers3

Linked