How do I use SELECT GROUP BY in DataTable.Select(Expression)?

I try to remove the duplicate rows by select a first row from every group. For Example

PK     Col1     Col2
1        A        B
2        A        B
3        C        C
4        C        C

I want a return:

PK     Col1     Col2
1        A        B
3        C        C

I tried following code but it didn't work:

DataTable dt = GetSampleDataTable(); //Get the table above.
dt = dt.Select("SELECT MIN(PK), Col1, Col2 GROUP BY Col1, Col2);

Solution 1:

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

Solution 2:

dt = dt.AsEnumerable().GroupBy(r => r.Field<int>("ID")).Select(g => g.First()).CopyToDataTable();

Solution 3:

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