How to GroupBy and Order by multiple fields with LINQ

Question

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

So... .GroupBy (n => new { n.Field<string>("id"), n.Field<string>("store"), n.Field<string>("sku") }) ?? — Dizzy49
– Dizzy49, Commented May 4, 2022 at 9:19
I think error is self explanatory .GroupBy (n => new { Id =n.Field<string>("id") — huMpty duMpty
– huMpty duMpty, Commented May 4, 2022 at 9:56

Svyatoslav Danyliv · Accepted Answer · 2022-05-04 10:05:44Z

1

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

edited May 4, 2022 at 10:05

answered May 4, 2022 at 9:46

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to GroupBy and Order by multiple fields with LINQ

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related