Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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); 
like image 704
J - C Sharper Avatar asked Oct 16 '13 15:10

J - C Sharper


People also ask

What is DataRow C#?

A DataRow represent a row of data in data table. You add data to the data table using DataRow object. A DataRowCollection object represents a collection of data rows of a data table.


1 Answers

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(); 
like image 109
D Stanley Avatar answered Sep 28 '22 01:09

D Stanley