Select Rows from a DataSet using LINQ, where the list of RowsID's are in a List<T>

Question

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.

Solution 1

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

Solution 2

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

Comments

[Enrico]

One way to convert to a datatable is by using .CopyToDataTable(), in this case make sure to check if matchingRows.Any() returns true before doing matchingRows.CopyToDataTable(). This requires System.Data.DataTableExtensions