Linq on DataTable: select specific column into dat

Question

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

Answer 1

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

Answer 2

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…

Answer 3

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

Answer 4

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).

Answer 5

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.