How do I use SELECT GROUP BY in DataTable.Select(Expression)?
DataTable
's Select
method only supports simple filtering expressions like {field} = {value}
. It does not support complex expressions, let alone SQL/Linq statements.
You can, however, use Linq extension methods to extract a collection of DataRow
s then create a new DataTable
.
dt = dt.AsEnumerable()
.GroupBy(r => new {Col1 = r["Col1"], Col2 = r["Col2"]})
.Select(g => g.OrderBy(r => r["PK"]).First())
.CopyToDataTable();
dt = dt.AsEnumerable().GroupBy(r => r.Field<int>("ID")).Select(g => g.First()).CopyToDataTable();
dt.AsEnumerable()
.GroupBy(r => new { Col1 = r["Col1"], Col2 = r["Col2"] })
.Select(g =>
{
var row = dt.NewRow();
row["PK"] = g.Min(r => r.Field<int>("PK"));
row["Col1"] = g.Key.Col1;
row["Col2"] = g.Key.Col2;
return row;
})
.CopyToDataTable();