Linq on DataTable: select specific column into datatable, not whole table

I'm running a LINQ query on a datatable in c#.

I want to select specific columns rather than the whole row and enter the result into a datatable. How can i do that??

My Code:

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

-------------Problems Reply------------

If you already know beforehand how many columns your new DataTable would 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);
}

Your select statement is returning a sequence of anonymous type , not a sequence of DataRows. CopyToDataTable() is only available on IEnumerable<T> where T is or derives from DataRow. You can select r the 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.

Try Access DataTable easiest way which can help you for getting perfect idea for accessing DataTable, DataSet using Linq...

Consider following example, suppose we have DataTable like 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 above is the code for DatTable, here we assume that there are some data are available in this DataTable, and we have to bind Grid view of particular by processing some data as shown below.

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

Than we have to fire following query which will give us exact result as we want.

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 have to assign this data variable as Data Source.

By using this simple Linq query we can get all our accepts, and also we can perform all other LINQ queries with this…

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

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

LINQ is very effective and easy to use on Lists rather than DataTable. I can see the above answers have a loop(for, foreach), which I will not prefer.

So the best thing to select a perticular column from a DataTable is just use a DataView to filter the column and use it as you want.

Find it here how to do this.

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

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

Category:c# Views:1 Time:2013-01-20
Tags: linq datatable

Related post

  • How to add a Arraylist in to a specific column of DataTable? 2011-12-17

    How to add a Arraylist in to a specific column of DataTable, for example, add Arraylist of name 'mylist' to the 5th column of the DataTable name mydatatable. I try this code: foreach(string item in mylist) { mydatatable.Rows.Add(item); } But when i a

  • How can i select specific columns from excel sheet in c#? 2011-08-22

    How can I select specific columns from excel sheet rather than all columns string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", txtPath.Text); string query = S

  • Selecting specific column in each row from array 2010-01-21

    I am trying to select specific column elements for each row of a numpy array. For example, in the following example: In [1]: a = np.random.random((3,2)) Out[1]: array([[ 0.75670668, 0.1283942 ], [ 0.51326555, 0.59378083], [ 0.03219789, 0.53612603]])

  • Selecting specific columns in jpa 2 Criteria API? 2010-08-13

    Is there a way to select specific column using the JPA 2 Criteria API? The following is the target SQL Statement: SELECT column1, column2 FROM MyTableThatHasMultipleColumns With Hibernate's Criteria API this can be done using Projections, is there an

  • Rails 2 - named_scope :include, :joins and selecting specific columns 2012-01-03

    I was wondering if it's possible to use :include in named_scope but to specify only specific columns to :include? Currently I use the following: class ProductOverwrite < ActiveRecord::Base belongs_to :product named_scope :with_name, :include =>

  • select specific column only in mysql 2011-02-25

    my table-1 has 20 columns and table-2 has 15 columns. here i want to join two tables by user id . i use this bellow query SELECT * FROM table_checkout_bidpack as t1 inner JOIN table_user_information as t2 ON t1.user_id=t2.user_id This query selects 3

  • LINQ: How to exclude data columns from DataTable.ItemArray query? 2011-06-19

    Given a collection of data rows, IEnumerable of type DataRow, how can I query the collection so as to compute the Sum all the values in the collection (i.e. all the values of all the columns in all the rows) with the exception of two specific columns

  • LINQ to SQL - How to select specific columns and return strongly typed list 2009-07-07

    I'm trying to use LINQ to SQL to select a few specific columns from a table and return the result as a strongly typed list of objects. For Example: var result = (from a in DataContext.Persons where a.Age > 18 select new Person { Name = a.Name, Age

  • Richfaces Datatable - Sort specific column on load 2010-09-24

    Is it possible to sort a datatable by a specific column when it first loads? Or does it need to be done using the DAO? When I put on ORDER BY in my SQL in my DAO, no data at all is returned in the datatable --------------Solutions------------- Solved

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.138 (s). 11 q(s)