Linq on DataTable: select a specific column in a datatable, not the whole table

Question

I am running a LINQ query on a datatable in C #.

I want to select specific columns, not the entire row, and enter the result in a datatable . How can i do this?

My code is:

 public DataTable getConversions(string c_to, string p_to) { var query = from r in matrix.AsEnumerable() where r.Field<string>("c_to") == c_to && r.Field<string>("p_to") == p_to select r; DataTable conversions = query.CopyToDataTable(); 

Answer 1

If you already know in advance how many columns your new DataTable will have, you can do something like this:

 DataTable matrix = ... // get matrix values from db DataTable newDataTable = new DataTable(); newDataTable.Columns.Add("c_to", typeof(string)); newDataTable.Columns.Add("p_to", typeof(string)); var query = from r in matrix.AsEnumerable() where r.Field<string>("c_to") == "foo" && r.Field<string>("p_to") == "bar" let objectArray = new object[] { r.Field<string>("c_to"), r.Field<string>("p_to") } select objectArray; foreach (var array in query) { newDataTable.Rows.Add(array); } 

Answer 2

Here I get only three specific columns from mainDataTable and use a filter

 DataTable checkedParams = mainDataTable.Select("checked = true").CopyToDataTable() .DefaultView.ToTable(false, "lagerID", "reservePeriod", "discount"); 

Answer 3

Try Accessing a DataTable is the easiest way , which can help you get the perfect idea for accessing a DataTable, DataSet using Linq ...

Consider the following example: suppose a DataTable , as shown below.

 DataTable ObjDt = new DataTable("List"); ObjDt.Columns.Add("WorkName", typeof(string)); ObjDt.Columns.Add("Price", typeof(decimal)); ObjDt.Columns.Add("Area", typeof(string)); ObjDt.Columns.Add("Quantity",typeof(int)); ObjDt.Columns.Add("Breath",typeof(decimal)); ObjDt.Columns.Add("Length",typeof(decimal)); 

Here is the DatTable code, here we assume that there is some data in this DataTable, and we need to bind the Grid representation of a particular one by processing some data, as shown below.

Area | Quantity | Breath | Length | Price = Quantity * Breath * Length

Then we need to run the following query, which will give us the exact result as necessary.

 var data = ObjDt.AsEnumerable().Select (r => new { Area = r.Field<string>("Area"), Que = r.Field<int>("Quantity"), Breath = r.Field<decimal>("Breath"), Length = r.Field<decimal>("Length"), totLen = r.Field<int>("Quantity") * (r.Field<decimal>("Breath") * r.Field<decimal>("Length")) }).ToList(); 

We just need to assign this data variable as a data source.

Using this simple Linq query, we can get all of our solutions, as well as complete all other LINQ queries with this ...

Answer 4

LINQ is very efficient and easy to use in lists, not in DataTable. I see that in the above answers there is a loop (for, foreach) which I will not use.

Thus, it is best to select a column from the DataTable column, just use the DataView to filter the column and use it as you like.

Find here how to do it.

 DataView dtView = new DataView(dtYourDataTable); DataTable dtTableWithOneColumn= dtView .ToTable(true, "ColumnA"); 

Now the DataTable dtTableWithOneColumn contains only one column (ColumnA).

Answer 5

Your select statement returns a sequence of anonymous type, not a sequence of DataRows. CopyToDataTable () is only available on IEnumerable<T> , where T is or comes from a DataRow . You can select the r row object to call CopyToDataTable on it.

 var query = from r in matrix.AsEnumerable() where r.Field<string>("c_to") == c_to && r.Field<string>("p_to") == p_to select r; DataTable conversions = query.CopyToDataTable(); 

You can also implement CopyToDataTable , where the generic type T is not a DataRow.