linq query repeated columns in a list

Question

I have a table similar to the one below.

Branch      Dept        Product ID  Product Val Product Date
Branch 1        Dept 1      ID 1        1       5/23/2013
Branch 1        Dept 1      ID 2        1       5/23/2013
Branch 1        Dept 2      ID 3        1       5/23/2013
Branch 2        Dept 11     ID 4        1       5/23/2013
Branch 2        Dept 11     ID 5        1       5/23/2013
Branch 2        Dept 11     ID 6        1       5/23/2013
Branch 3        Dept 21     ID 7        1       5/23/2013

I am trying to use LINQ(am a rookie to LINQ) to load this as a collection of objects into an object like:

Products = { Branch1 { Dept1 {ID1,ID2}, 
                       Dept2 {ID3}}, 
             Branch2 { Dept11 {ID4, ID5, ID6}}, 
             Branch3 { Dept21 {ID7 }
           }

And I have trying bit hard working overnight but could not get the right solution. So far I have achieved the following code;

var branches = (from p in ProductsList
    select p.Branch).Distinct();
var products = from s in branches
    select new
    {
        branch = s,
        depts = (from p in ProductsList
            where p.Branch == s
            select new
            {
                dept = p.Dept,
                branch = s,
                prod = (from t in ProductsList
                    where t.Branch = s
                    where t.Dept == p.Dept
                    select t.ProductID)
            })
    };

where ProductsList is the list object of the whole table date List

Any help at the earliest is much appreciated. Thanks in advance!

possible duplicate of [How can I hierarchically group data using LINQ?](http://stackoverflow.com/questions/2230202/how-can-i-hierarchically-group-data-using-linq) — Damith, May 23 '13 at 09:38

Raphaël Althaus · Accepted Answer · 2013-05-23T09:44:36.670

I would go for soemthing like that if you really wanna use linq.

Somethimes, a few foreach are much clearer !

var myDic = ProductList
                .GroupBy(m => m.Branch)
                .ToDictionary(
                    m => m.Key,
                    m => m.GroupBy(x => x.Dept)
                          .ToDictionary(
                              x => x.Key,
                              x => x.Select(z => z.ProductId)));

result will be a

Dictionary<string, Dictionary<string, IEnumerable<string>>>

where first dictionary Key is Branch, inner dictionary key is Dept, and string list are ProductId

which seem to correpond to your wanted result.

Thank you Raphael and Dave. Both the solutions worked and Raphael's suited my scenario better. — user2412777, Sep 23 '13 at 07:28

score 0 · Answer 2 · answered May 23 '13 at 09:41

Something like this, maybe?

Products.
    .Select(prop => prop.Branch)
    .Distinct()
    .Select(b => new 
    {
        Branch = b,
        Departments = Products
            .Where(p => p.Branch == b)
            .Select(p => p.Dept)
            .Distinct()
            .Select(d => new 
            {
                Products = Products
                    .Where(p => p.Department == d)
                    .Select(p => p.ProductID)
                    .Distinct()
            })
    })

linq query repeated columns in a list

2 Answers2