How do I use lambda expressions to filter DataRows?

Question

How can I search rows in a datatable for a row with Col1="MyValue"

I'm thinking something like

Assert.IsTrue(dataSet.Tables[0].Rows.
    FindAll(x => x.Col1 == "MyValue" ).Count == 1);

But of course that doesn't work!

Answer 1

You can use LINQ to DataSets to do this:

Assert.IsTrue(dataSet.Tables[0].AsEnumerable().Where(
    r => ((string) r["Col1"]) == "MyValue").Count() == 1);

Note, you can also do this without the call to Assert:

dataSet.Tables[0].AsEnumerable().Where(
    r => ((string) r["Col1"]) == "MyValue").Single();

If the number of rows does not equal one (hence, the call to Single), then an exception will be thrown, and that unhandled exception should fail your test case. Personally, I like the latter, as it has a clearer semantic meaning.

The above can be further whittled down to:

dataSet.Tables[0].AsEnumerable().Single(
    r => ((string) r["Col1"]) == "MyValue");

Additionally, you can take advantage of the Field method on the DataRowExtensions class to simplify type-safe access to the field (as well as providing the extra benefit of converting DBNull to null counterparts in .NET):

dataSet.Tables[0].AsEnumerable().Single(
    r => r.Field<string>("Col1") == "MyValue");

Answer 2

You can use the Select method of the data table to do this, or the Filter Property of the DefaultDataView on the table.

For the Select method:

var rows = dataSet.Tables[0].Select("Col1 = 'MyValue'");

For the DefaultView Filter:

dataSet.Tables[0].DefaultView.Fitler = "Col1 = 'MyValue'";
foreach (var drv in dataSet.Tables[0].DefaultView)
{
    // Do your processing
}

Answer 3

Why use lambda and not select?

  DataRow[] foundRow = ( dataSet.Tables[0].Rows.Select("Col1 = 'MyValue'");

Answer 4

You can try this:

var b=datatable.AsEnumerable.where(p=> p.Field<string>   
("column_name")=="desire_value").CopyToDataTable()

Answer 5

The code you wrote checks that there's only one row which fulfils your search condition. If you actually want the rows, drop the Assert and Count