Linq Query on a Datatable

LINQ query on a DataTable

You can't query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. You need to use the AsEnumerable() extension for DataTable. Like so:

var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("RowNo") == 1
select myRow;

And as @Keith says, you'll need to add a reference to System.Data.DataSetExtensions

AsEnumerable() returns IEnumerable<DataRow>. If you need to convert IEnumerable<DataRow> to a DataTable, use the CopyToDataTable() extension.

Below is query with Lambda Expression,

var result = myDataTable
    .AsEnumerable()
    .Where(myRow => myRow.Field<int>("RowNo") == 1);

C# LINQ query from datatable

This should be fairly easy to do using GroupBy.

dtLists.AsEnumerable()
    .GroupBy(x => new { ListNameID = x.Field<int>("ListNameID"), ListName = x.Field<string>("ListName") })
    .Select(x => new SearchCategories()
    {
        SearchCategoryId = x.Key.ListNameID,
        SearchCategoryName = x.Key.ListName,
        lstSearchTags = x.Select(y => new SearchTags(){ SearchTagId = y.Field<int>("TagId"), SearchTagValue = y.Field<string>("TagValue") }).ToList()
    });

LINQ to DataTable

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.

How I can filter a dataTable with Linq to datatable?

You are better of using DataTable.Select method, but if you have to use LINQ then you can try:

DataTable selectedTable = tb.AsEnumerable()
                            .Where(r => r.Field<string>("Modul") == value)
                            .CopyToDataTable();

This would create a new DataTable based on filtered values.

If you use DataTable.Select

string expression = "Modul =" + value;
DataRow[] selectedRows = tb.Select(expression);

Fill Datatable from linq query

Since the query returns an IEnumerable of Type DataRow, you have to specify what to insert into the datatable, in this case DataRow.

DataTable dt = query.CopyToDataTable<DataRow>();

If you used

var query = //linq query of what you need for new datatable....
DataTable dt = query.CopyToDataTable();

Your table name is dt so select what you need from the original dt

var query = from c in db.something
            where c.othersomething == "onlyyouknow"
            orderby c.othersomething
            select new { NewObject = c.othersomething };

DataTable MyDataTable = new DataTable();
myDataTable.Columns.Add(
    new DataColumn()
    {
        DataType = System.Type.GetType("System.String"),//or other type
        ColumnName = "Name"      //or other column name
    }
);

foreach (var element in query)
{
    var row = MyDataTable.NewRow();
    row["Name"] = element.NewObject;
    myDataTable.Rows.Add(row);
}

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

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

How to use LINQ on a DataTable in Uipath

d1.asEnumerable.where(Function(x1) CInt(x1("salary"))>500).copyToDatatable

or

d1.asEnumerable.where(Function(x1) x1("salary")>500).copyToDatatable
if the salary column is already an integer

You may need to add Assembly reference as System.Data.DataSetExtensions if drop down does not appear after you type d1.asEnumerable.

Select statement on DataTable without where clause using Linq

fullTable
   .AsEnumerable()
   .Select(x => new 
                { 
                   PartnerID = x.Field<int>("PartnerID"), 
                   PartnerName = x.Field<string>("Partner Name")
                })
   .Distinct();

This will create an anonymous type with the two properties you want. You than apply a Distinct to remove the duplicates. Anonymous types handles GetHashCode and Equals for you which Distinct uses to identify duplicates.

AND operator in Linq Query with DataTable

As mentioned, you should use or, not and operator.

By the way, if you want the list of accepted acc_id to be more dynamic (coming from a parameter, for example), you may also do

var ids = new[]{1, 5};//this could be passed as parameter to a method

then

var query = dt.AsEnumerable()
              .Where(m => ids.Contains(m.Field<Int64>("acc_id")));

or of course

   var query = from a in dt.AsEnumerable()
               where ids.Contains(a.Field<Int64>("acc_id"))
               select a

---

Related Topics