First I have to say, that I am a newby using LINQ. Actually I never used before, but I am having a task where I need to filter a DataTable, using values that will come from a List. So I will like to know if it's possible in LINQ to query on a Datatable using values in the List as Filter values. Some one can give me some hint's
Thank you.
Data sources that implement the IEnumerable<T> generic interface can be queried through LINQ. Calling AsEnumerable on a DataTable returns an object which implements the generic IEnumerable<T> interface, which serves as the data source for LINQ to DataSet queries.
You can use query expression syntax or method-based query syntax to perform queries against single tables in a DataSet, against multiple tables in a DataSet, or against tables in a typed DataSet.
Can we use linq to query against a DataTable? Explanation: We cannot use query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. We need to use the AsEnumerable() extension for DataTable.
The best way to do this depends on what you plan to do with the filtered results. Do you need the results back as DataTable for further operations, or are you databinding to the results?
Take the example below, which returns a (bindable) enumerator of matching DataRows
//create sample table with sample rows
DataTable table = new DataTable();
table.Columns.Add("id", typeof(int));
for (int i = 1; i < 11; i++)
{
DataRow row = table.NewRow();
row[0] = i;
table.Rows.Add(row);
}
//filter the table by id (in a list)
List<int> rowIds = new List<int> { 1, 3, 6 };
IEnumerable<DataRow> matchingRows = from DataRow row in table.Rows
where rowIds.Contains((int)row[0])
select row;
If you need a DataTable you could import the rows into another table:
DataTable filteredTable = table.Clone();
foreach (DataRow filteredRow in matchingRows)
{
filteredTable.ImportRow(filteredRow);
}
I usually give myself extension methods when I need to treat a pre-LINQ object as a LINQ-ready object. For example, you're looking to query on a DataRowCollection (DataTable.Rows property) which you're probably not using for anything more than a list of DataRows. I would make an extension method that does this transformation for you (DataRowCollection to List<DataRow>
). I also usually use an extension method to get values safely when I don't care to throw an exception if for some reason an invalid key name is supplied for the column. Then, you can make an extension method that takes a list of ints as id's and a field name that contains the id to return what you want. When all is said and done, this is done with one line of code. Here's the class with all your extension methods.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication11
{
public static class SystemDataHelpers
{
public static List<DataRow> RowList(this DataTable table)
{
List<DataRow> list = new List<DataRow>();
foreach (DataRow row in table.Rows)
list.Add(row);
return list;
}
public static object GetItem(this DataRow row, string field)
{
if (!row.Table.Columns.Contains(field))
return null;
return row[field];
}
public static List<DataRow> GetRows(this DataTable table, List<int> ids, string fieldName)
{
Func<DataRow, bool> filter = row => ids.Contains((int)row.GetItem(fieldName));
return table.RowList().Where(filter).ToList();
}
}
}
Then, other than setting up your variables (which you wouldn't need to do...you already have them), the real work is done with one line of code (EDIT: a single method call):
DataTable table = new DataTable();
List<int> rowIds = new List<int> { 1, 2, 3, 4 };
string idFieldName = "row_id";
List<DataRow> selected = table.GetRows(rowIds, idFieldName);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With