CODEWITHSUNDEEP
c#linqdatatable
Dimitry
Dimitry

Reputation: 287

C# Datatable - group by multiple columns with linq

I have a Datatable like this

| Supplier  | Product | Price1 | Price2 | Price3 | ... | PriceN |
|-----------|---------|--------|--------|--------|-----|--------|
| Supplier1 | Orange  | 100    | 105    | 150    | ... | 180    |
| Supplier1 | Orange  | 110    | 130    | 140    | ... | 180    |
| Supplier2 | Orange  | 200    | 250    | 270    | ... | 350    |
| Supplier2 | Orange  | 250    | 270    | 320    | ... | 270    |

I want to group rows as next:

| Supplier  | Product | Price1  | Price2  | Price3  | ... | PriceN  |
|-----------|---------|---------|---------|---------|-----|---------|
| Supplier1 | Orange  | 100-110 | 105-130 | 140-150 | ... | 180     |
| Supplier2 | Orange  | 200-250 | 250-270 | 270-320 | ... | 270-350 |

Count of columns like "PriceN" can be arbitrary. How can I do this with LINQ?

Upvotes: 0

Views: 10522

Answers (4)

user8128167
user8128167

Reputation: 7676

To group by multiple columns in lambda expression use:

var groupedSupplier = supplier.GroupBy(s => s.Supplier,
                                       s => s.Product)

For details, please see chapter 11 of "C# in Depth" by Jon Skeet, Chapter 11 "Query expressions and LINQ to Objects: 11.6 Groupings and continuations", http://csharpindepth.com/

Upvotes: 0

rbr94
rbr94

Reputation: 2277

You can use GroupBy and JOIN to concatenate the values of the price columns:

var groupedSupplier = supplier.GroupBy(s => new { s.Supplier, s.Product })
                              .Select(supplier => supplier.Supplier, 
                                                  supplier.Product,
                                                  supplier.Price1 = string.Join(",", supplier.Select(x => x.Price1)),
                                                  supplier.Price2 = string.Join(",", supplier.Select(x => x.Price2)),
...);

Upvotes: 0

Mostafiz
Mostafiz

Reputation: 7352

You can group by Supplier and Product as

var result = from x in data
             group x by new { x.Supplier, x.Product }
             select x;

or

var result = data.GroupBy(x => new { x.Supplier, x.Product });

similarly you can use any number of property in group clause

Upvotes: 2

Hamid Jolany
Hamid Jolany

Reputation: 880

you have to group by it separately in another lambda expression

result.tolist().GroupBy(p=> p.x,p.x2,p.x3 ...);

Upvotes: 0

Related Questions