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

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

the error is: The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable(System.Collections.Generic.IEnumerable)'. There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'. — Uri Klar, Jan 20 '13 at 07:57
possible duplicate of [Exception using CopyToDataTable with LINQ query?](http://stackoverflow.com/questions/1072120/exception-using-copytodatatable-with-linq-query) — , Jan 20 '13 at 08:07
not exactly... i'm not looking for a solution for the error but for the right way to select only two columns out of a row and enter the result into a datatable — Uri Klar, Jan 20 '13 at 08:21

score 10 · Accepted Answer · answered Jan 21 '13 at 02:26

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

Leax · Answer 2 · 2013-07-10T07:08:31.377

8

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

edited Jul 10 '13 at 07:08

answered Feb 18 '13 at 16:01

Leax

111
2
8

score 8 · Answer 3 · answered Apr 07 '13 at 13:54

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…

plus one for using lambda expression – ashveli Apr 23 '19 at 11:40 — ashveli, Apr 23 '19 at 11:40

score 7 · Answer 4 · answered Jan 20 '14 at 07:04

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

Adil · Answer 5 · 2013-01-20T08:15:18.067

3

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.

edited Jan 20 '13 at 08:15

answered Jan 20 '13 at 08:03

Adil

146,340
25
209
204

Hi. I know my code is incorrect. the question is what is the correct way to select only two columns and not the whole row. – Uri Klar Jan 20 '13 at 08:20
I have added link to article in my answer to do that, http://msdn.microsoft.com/en-us/library/bb669096.aspx – Adil Jan 20 '13 at 08:22
Can i leave the type as a datarow but simply filter specific columns? – Uri Klar Jan 20 '13 at 08:29

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

5 Answers5

Linked