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);
There is actually a (subtle) difference between the two. Imagine you have the following code in File1.cs:
// File1.cs
using System;
namespace Outer.Inner
{
class Foo
{
static void Bar()
{
double d = Math.PI;
}
}
}
Now imagine that someone adds another file (File2.cs) to the project that looks like this:
// File2.cs
namespace Outer
{
class Math
{
}
}
The compiler searches Outer before looking at those using directives outside the namespace, so it finds Outer.Math instead of System.Math. Unfortunately (or perhaps fortunately?), Outer.Math has no PI member, so File1 is now broken.
This changes if you put the using inside your namespace declaration, as follows:
// File1b.cs
namespace Outer.Inner
{
using System;
class Foo
{
static void Bar()
{
double d = Math.PI;
}
}
}
Now the compiler searches System before searching Outer, finds System.Math, and all is well.
Some would argue that Math might be a bad name for a user-defined class, since there's already one in System; the point here is just that there is a difference, and it affects the maintainability of your code.
It's also interesting to note what happens if Foo is in namespace Outer, rather than Outer.Inner. In that case, adding Outer.Math in File2 breaks File1 regardless of where the using goes. This implies that the compiler searches the innermost enclosing namespace before it looks at any using directive.
Best Answer
I figured out a way to do this, though it may not be optimal in all situations. Just doing a .Distinct() on the LINQ query does, in fact, produce a "distinct" in the resulting SQL query when used without .Count(). If I cause the query to be enumerated by using .Distinct().ToList() and then use the .Count() method on the resulting in-memory collection, I get the result I want.
This is not exactly equivalent to what I was originally doing with the Criteria query, since the counting is actually being done in the application code, and the entire list of IDs must be sent from the DB to the application. In my case, though, given the small number of distinct IDs, I think it will work, and won't be too much of a performance bottleneck.
I do hope, however, that a true CountDistinct() LINQ operation will be implemented in the future.