How can I get the distinct items of the following data structure in C# / LINQ?

Question

I have the following objects:

public class Agency  
{   
    public int ID; 
    public IEnumerable<BusinessUnit> BusinessUnits;  
}  

public class BusinessUnit
{
    public int ID;  
    public decimal AmountSpent;
    public IEnumerable<Client> Clients;
}

public class Client
{
    public int ID;  
    public decimal AmountSpent;
}

Now, I have a IEnumerable<Agency> which contains several agencies, these agencies contain several BusinessUnits, and those BusinessUnits contain several clients.

It might look something like:

Agency1
++BusinessUnit1 - $20
++++Client1 - $10
++++Client2 - $10

Agency2
++BusinessUnit2 - $20
++++Client1 - $20

Agency3
++BusinessUnit1 - $10
++++Client5 - $5
++++Client6 - $5
++BusinessUnit2 - $25
++++Client1 - $25

Now, what I'm looking to do, is create a list of BusinessUnits and Clients, but for all agencies, without duplicates.

So, I'm looking to turn that list of Agencies into:

All Agencies
++BusinessUnit1 - $30
++++Client1 - $10
++++Client2 - $10
++++Client5 - $5
++++Client6 - $5

++BusinessUnit2 - $45
++++Client1 - $20
++++Client1 - $25

Where I simply have a list of unique BusinessUnits (with their correct AmountSpent total across all instances of that particular BusinessUnit), and with a list of unique clients under them, with the correct AmountSpent total for each instances of that particular client under that business unit.

Is it possible with LINQ that I can I query my IEnumerable<Agency> Agencies list to return my distinct list of BusinessUnit/Client combinations with the correct sums?

Answer 1

Something like this, methinks (untested):

var res = agencies.SelectMany(a => a.BusinessUnits)
                  .GroupBy(b => b.ID)
                  .Select(b => new BusinessUnit { 
                     ID = b.Key, 
                     AmountSpent = c.Sum(b2 => b2.AmountSpent),
                     Clients = b.SelectMany(b2 => b2.Clients)
                                .GroupBy(c => c.ID)
                                .Select(c => new Client { 
                                   ID = c.Key, 
                                   AmountSpent = c.Sum(c2 => c2.AmountSpent) 
                                 })
                   });

There may be a way to optimize away the anonymous class creation. I'll leave that up to you. Assuming that BusinessUnit.AmountSpent is correct we probably don't need to sum up the Client's AmountSpent.

---

On a second read it looks like you might not want to collapse Clients underneath Business Units but keep each instance, in that case you can get rid of b.SelectMany(b2 => b2.Clients) and everything that follows and just use b.Clients.

Answer 2

var groupings =
    from agency in agencies
    from businessUnit in agency.BusinessUnits
    from client in businessUnit.Clients
    group client by businessUnit.ID into clients
    select clients;

var businessUnits =
    from grouping in groupings
    select new
    {
        ID = grouping.Key,
        AmountSpent = grouping.Sum(client => client.AmountSpent),
        Clients = grouping
    };

Console.WriteLine("All Agencies");

foreach (var businessUnit in businessUnits)
{
    Console.WriteLine("++BusinessUnit{0} - ${1}",
                      businessUnit.ID,
                      businessUnit.AmountSpent);

    foreach (var client in businessUnit.Clients)
    {
        Console.WriteLine("++++Client{0} - ${1}", client.ID, client.AmountSpent);
    }
}