I have the following datatable

The Col1 and Col2 is the unique key so the first and the second row is duplicated. The Col3 differ so I would like to have an option to setup condition which row to select. The condition could be for example Take grouped row where Col3 is the greatest. The result would be.

How to use LINQ for that? I tried variations of following

Datatable setup

var datatable = new DataTable();
            
datatable.Columns.Add("Col1");
datatable.Columns.Add("Col2");
datatable.Columns.Add("Col3");

// this row should not be in the result
var row1 = datatable.NewRow(); 
row1.ItemArray = new[] { "A", "B", "1" };
datatable.Rows.Add(row1);

var row2 = datatable.NewRow();
row2.ItemArray = new[] { "A", "B", "2" };
datatable.Rows.Add(row2);

var row3 = datatable.NewRow();
row3.ItemArray = new[] { "A", "C", "1" };
datatable.Rows.Add(row3);

Query attempt

It can be query expression or fluent based, it does not matter.

var result =
from record in datatable.AsEnumerable()
group record by new { record.Field<string>("Col1"), record.Field<string>("Col2") } into g
let row =
(
     from groupedItem in groupedItems
     order by groupedItem.Field<string>("Col3") desc
     select groupedItem
).First()
select row;

When there is record.Field, I am getting an exception

Invalid anonymous type member declarator

I'm not experienced in complicated LINQ queries, how can I solve this?

CodePudding user response：

it seems like compiler is not able to infer the type of the columns you have in the datatable, try naming them in anonymous properties like:

 group record by new 
                 { 
                    Col1 = record.Field<string>("Col1"),
                    Col2 = record.Field<string>("Col2") 
                 }

CodePudding user response：

The working solution, thanks to @Ehsan Sajjad is

var result =
from record in datatable.AsEnumerable()
group record by new { 
    Col1 = record.Field<string>("Col1"), 
    Col2 = record.Field<string>("Col2"),
} into g
let selectedItem =
(
    from item in g
    orderby item.Field<string>("Col3") descending
    select item
).First()
select selectedItem;