Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 593
Giuseppe.Turitto Avatar asked Aug 12 '09 22:08

Giuseppe.Turitto


People also ask

How can you load data into a DataSet so that it can be queried using LINQ?

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.

How can LINQ queries be performed against multiple tables in a DataSet?

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?

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.


2 Answers

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);
}
like image 104
Mark Glasgow Avatar answered Sep 29 '22 10:09

Mark Glasgow


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);
like image 40
Rich Avatar answered Sep 29 '22 09:09

Rich