How do I use SELECT GROUP BY in DataTable Select Expression

Question

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

User · Answer

dt AsEnumerable        GroupBy r   gt  new   Col1   r  Col1    Col2   r  Col2           Select g   gt                var row   dt NewRow             row  PK     g Min r   gt  r Field lt int gt   PK             row  Col1     g Key Col1          row  Col2     g Key Col2           return row               CopyToDataTable

User · Answer

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   gt  new  Col1   r  Col1    Col2   r  Col2             Select g   gt  g OrderBy r   gt  r  PK    First            CopyToDataTable

User · Answer

This solution sort by Col1 and group by Col2  Then extract value of Col2 and display it in a mbox   var grouped   from DataRow dr in dt Rows orderby dr  Col1   group dr by dr  Col2    string x       foreach  var k in grouped  x     string  k ElementAt 0   Col2      Environment NewLine  MessageBox Show x

User · Answer

dt   dt AsEnumerable   GroupBy r   gt  r Field lt int gt   ID    Select g   gt  g First    CopyToDataTable

[c#] How do I use SELECT GROUP BY in DataTable.Select(Expression)?

Examples related to c#

Examples related to datatable

Examples related to duplicates