How can I query this hierarchical data using LINQ?

Question

I have 3 kinds of objects: Agency, BusinessUnit and Client (each with their own respective table)

In terms of hierarchy, Agencies own BusinessUnits, and BusinessUnits own Clients.

I have 3 C# POCO Objects to represent them (I usually select new {} into them, rather than use the LINQ generated classes):

public class Agency
{
    public IEnumerable<BusinessUnit> BusinessUnits { get; set; }
}

public class BusinessUnit
{
    public IEnumerable<Client> Clients { get; set; }
}

public class Client
{
    public int NumberOfAccounts { get; set; }
    public Decimal AmountOfPlacement { get; set; }
    public Decimal AvgBalance { get; set; }
    public Double NeuPlacementScore { get; set; }
}

You can see that Agencies contain a list of BusinessUnits, and BusinessUnits contain a list of Clients.

I also have a mapping table called BAC_Map in the database which says which owns which, and it looks something like this:

How can I construct a query, so I can query for and return a list of Agencies? Meaning that, I want each Agency to have its list of BusinessUnit objects set, and I want the list of BusinessObjects to have its list of Clients set.

I can do basic LINQ queries, but this is a little over my head concerning the Map table and the multiple? queries.

How could I construct a method like GetAllAgencies() which would query, for not only all agencies, but populate its BusinessUnits that Agency owns, and the Clients those BusinessUnits own?

---

Edit: Any tips or info is appreciated. Do I need to do joins? Does this need to be multiple queries to return an Agency list, with its submembers populated?

Answer 1

If you drop all four tables (Agency, BusinessUnit, Client, Map) on the linq to sql designer, and draw relationships from Map to the other three, there will be some useful properties on Map.

  //construct a query to fetch the row/column shaped results.
var query = 
  from m in db.map
  //where m.... ?
  let a = m.Agency
  let b = m.BusinessUnit
  let c = m.Client
  // where something about a or b or c ?
  select new {
    AgencyID = a.AgencyID,
    AgencyName = a.Name,
    BusinessUnitID = b.BusinessUnitID,
    ClientID = c.ClientID,
    NumberOfAccounts = c.NumberOfAccounts,
    Score = c.Score
  };
  //hit the database
var rawRecords = query.ToList();

  //shape the results further into a hierarchy.    
List<Agency> results = rawRecords
  .GroupBy(x => x.AgencyID)
  .Select(g => new Agency()
  {
    Name = g.First().AgencyName,
    BusinessUnits = g
    .GroupBy(y => y.BusinessUnitID)
    .Select(g2 => new BusinessUnit()
    {
      Clients = g2
      .Select(z => new Client()
      {
        NumberOfAccounts = z.NumberOfAccounts,
        Score = z.Score
      })
    })
  })
  .ToList();

If approriate filters are supplied (see the commented out where clauses), then only the needed portions of the tables will be pulled into memory. This is standard SQL joining at work here.

Answer 2

I created your tables in a SQL Server database, and tried to recreate your scenario in LinqPad. I ended up with the following LINQ statements, which basically result in the same structure of your POCO classes:

var map =   from bac in BAC_Maps
            join a in Agencies on bac.Agency_ID equals a.Agency_ID
            join b in BusinessUnits on bac.Business_Unit_ID equals b.Business_Unit_ID
            join c in Clients on bac.Client_ID equals c.Client_ID
            select new 
            { 
                AgencyID        =   a.Agency_ID,
                BusinessUnitID  =   b.Business_Unit_ID,
                Client          =   c
            };

var results =   from m in map.ToList()
                group m by m.AgencyID into g
                select new 
                {
                    BusinessUnits = from m2 in g
                                    group m2 by m2.BusinessUnitID into g2
                                    select new
                                    {
                                        Clients = from m3 in g2
                                                select m3.Client
                                    }
                };

results.Dump();

Note that I called map.ToList() in the second query. This actually resulted in a single, efficient query. My initial attempt did not include .ToList(), and resulted in nine separate queries to produce the same results. The query generated by the .ToList() version is as follows:

SELECT [t1].[Agency_ID] AS [AgencyID], [t2].[Business_Unit_ID] AS [BusinessUnitID], [t3].[Client_ID], [t3].[NumberOfAccounts], [t3].[AmountOfPlacement], [t3].[AvgBalance], [t3].[NeuPlacementScore]
FROM [BAC_Map] AS [t0]
INNER JOIN [Agencies] AS [t1] ON [t0].[Agency_ID] = [t1].[Agency_ID]
INNER JOIN [BusinessUnits] AS [t2] ON [t0].[Business_Unit_ID] = [t2].[Business_Unit_ID]
INNER JOIN [Clients] AS [t3] ON [t0].[Client_ID] = [t3].[Client_ID]

Here is a screenshot of the results:

alt text http://img411.imageshack.us/img411/5003/agencybusinessunitclien.png