LINQ Recursion for Hierarchy Data

Question

I have a data structure like this:

Category: ID, Name
Manufacturer: ID, Name
Product: ID, CategoryID, ManufacturerID, Name

I'm using EF to call a stored procedure that returns data like this:

CategoryID   CategoryName  ManufacturerID  ManufacturerName ProductID ProductName
=================================================================================
1            C1            1               M1               1         P1
1            C1            1               M1               2         P2
1            C1            2               M2               3         P3
2            C2            1               M1               4         P4
2            C2            3               M3               5         P5

EF generates a data type (MyFlatDataType) that maps the data above.

I would like to use LINQ resursion to construct my objects that can be displayed in a treeview:

C1
  \_ M1
       \_ P1
       \_ P2
  \_ M2
       \_ P3
C2
  \_ M1
       \_ P4
  \_ M3
       \_ P5

Note that I also need to keep the ID (can be either CategoryID, ManufacturerID or ProductID so it's not something like ParentID since these IDs could have the same seed value) for each node. Is this doable?

Answer 1

This should do the trick.

//The data rows you're getting from the stored procedure:
IEnumerable<MyFlatDataType> rows = ...;
//The tree structure you requested:
var categories = rows
    .GroupBy(row => new { row.CategoryID, row.CategoryName })
    .Select(categoryGroup => new
    {
        categoryGroup.Key.CategoryID,
        categoryGroup.Key.CategoryName,
        Manufacturers = categoryGroup
            .GroupBy(row => new { row.ManufacturerID, row.ManufacturerName })
            .Select(manufacturerGroup => new
            {
                manufacturerGroup.Key.ManufacturerID,
                manufacturerGroup.Key.ManufacturerName,
                Products = manufacturerGroup
                    .Select(row => new
                    {
                        row.ProductID,
                        row.ProductName
                    })
                    .ToList()
            })
            .ToList()
    })
    .ToList();

Answer 2

Assuming all collections are already in memory (you said nothing about any orm), then all you have to do is a join:

products
  .Join(cats, x => x.catId, x => x.id, (x, y) => new Projection(){cat = y, p = x})
  .Join(mans, x => x.p.manId, x => x.id, (x, y) => { x.man = y; return x; })
  ;

code sample:

public class Product
{
    public int id;
    public int catId;
    public int manId;
    public string name;
}

public class Man
{
    public int id;
    public string name;
}

public class Cat
{
    public int id;
    public string name;
}

public class Projection
{
    public Cat cat;
    public Man man;
    public Product p;
}


[Fact]
public void Do()
{
    var products = new List<Product>()
       {
           new Product{id=1, catId=1, manId=1, name="1"},
           new Product{id=2, catId=1, manId=1, name="2"},
           new Product{id=3, catId=1, manId=2, name="3"},
           new Product{id=4, catId=2, manId=1, name="4"},
           new Product{id=5, catId=2, manId=3, name="5"},
       };

    var cats = new List<Cat>()
       {
           new Cat() {id = 1, name = "1c"},
           new Cat() {id = 2, name = "2c"}
       };

    var mans = new List<Man>()
       {
           new Man() {id = 1, name = "1m"},
           new Man() {id = 2, name = "2m"},
           new Man() {id = 3, name = "3m"}
       };

    var results = products
        .Join(cats, x => x.catId, x => x.id, (x, y) => new Projection(){cat = y, p = x})
        .Join(mans, x => x.p.manId, x => x.id, (x, y) => { x.man = y; return x; })
        ;

    foreach (var x in results)
    {
        Console.WriteLine("{0} {1} {2} {3} {4} {5}", x.cat.id, x.cat.name, x.man.id, x.man.name, x.p.id, x.p.name);
    }
}