How to GroupBy and Order by multiple fields with LINQ-CodePudding

I have a DataTable (dtResult) with 4 fields, id, store, sku and qty. Unfortunately there are a lot of duplicates in the DataTable that I want to remove (qtys are diff, but other fields are the same).

I want to sort the DataTable by id asc, store asc, sku asc, and group by id, store and sku so I would have a list of unique records.

IDEALLY I would like to overwrite the existing DataTable with the results of the query, and qty can just be 0 for everything. I have the sort, and currently I'm putting it into a new DataTable:

var dtUniqueResults = dtResult.AsEnumerable()
    .OrderBy(r => r.Field<string>("id"))
    .ThenBy(r => r.Field<string>("store"))
    .ThenBy(r => r.Field<string>("sku"))
    .CopyToDataTable();

I don't understand how to group with LINQ. I think I need to add something like this, but it's not working.

var dtUniqueResults = dtResult.AsEnumerable()
    .GroupBy(n => n.Field<string>("id"),
             n => n.Field<string>("store"),
             n => n.Field<string>("sku")
    )
    .OrderBy(r => r.Field<string>("id"))
    .ThenBy(r => r.Field<string>("store"))
    .ThenBy(r => r.Field<string>("sku"))
    .CopyToDataTable();

I've read a lot of posts, and I see several ways of doing it. However it seems the two that are suggested the most are these, but they seem so different it just confuses me more.

GroupBy( x => new { x.Column1, x.Column2 })

AND

GroupBy(x=> new { x.Column1, x.Column2 }, (key, group) => new 
{ 
  Key1 = key.Column1,
  Key2 = key.Column2,
  Result = group.ToList() 
});

CodePudding user response：

If you need to filter out duplicates, try the following query:

var dtUniqueResults = dtResult.AsEnumerable()
    .GroupBy(n => new 
        { 
            Id = n.Field<string>("id"),
            Store = n.Field<string>("store"),
            Sku = n.Field<string>("sku")
        }
    )
    .SelectMany(g => g.Take(1)) // get from group only one record
    .CopyToDataTable();