Aggregate duplicate members of an object in C# using LINQ / Lambda

Question

I have a job to aggregate companies employees existing in 2 different databases using LINQ, the criteria is to find companies with the same name then find departments in these companies with the same name too then merge all employees into a new company object.

I have all companies from the 2 databases in one List<Company>, is their a way to use the Aggregate function to merge companies with the same name, then find departments with same name and merge all their employees? using the Aggregate function seems to be easy to merge "1 level" of data, I'm struggling to go into 3 levels of aggregation (Company>Department>Employee)

Example of my List<Company> AllCompanies object:

Company       Department      Employees   Source
-----------|--------------|-------------|---------
ABC Inc    |  Sales       | Sam         | DB1
           |              | Laura       |
-----------|--------------|-------------|---------
ABC Inc    |  Sales       | Joe         | DB2
-----------|--------------|-------------|---------
ABC Inc    |  Sales       | Joe         | DB1
-----------|--------------|-------------|---------
ABC Inc    |  IT          | Matt        | DB2
-----------|--------------|-------------|---------
XYZ Inc    |  Sales       | Steve       | DB1
-----------|--------------|-------------|---------
XYZ Inc    |  Sales       | Steve       | DB2 
-----------|--------------|-------------|---------
XYZ Inc    |  HR          | Mark        | DB2

I'm trying to convert the above to:

Company       Department      Employees
-----------|--------------|-------------
ABC Inc    |  Sales       | Sam
           |              | Laura
           |              | Joe
           |--------------|-------------
           |  IT          | Matt
-----------|--------------|-------------
XYZ Inc    |  Sales       | Steve
           |--------------|-------------
           |  HR          | Mark

For the sake of this example my match criteria is the name only.

I tried to draw the tables above, pretty neat, but apart from that I didn't try anything cause I didn't know how to do it, which is why I come here seeking for an answer in the first place but thanks for trying to answer my question. — Maya, Mar 15 '13 at 17:58

score 4 · Accepted Answer · answered Mar 15 '13 at 10:28

 from c in allCompanies
 group c by c.Company into departments
 select new {
    Company = departments.Key,
    Departments = from d in departments
                  group d by d.Department into employees
                  select new {
                      Department = employees.Key,
                      Employees = employees.Select(e => e.Employees)
                                           .Distinct()
                  }
 }

Aggregate duplicate members of an object in C# using LINQ / Lambda

1 Answers1