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

Answer 3

If you are doing this with direct LINQ to SQL, there is no way to do this without some kind of recursion, whether you do it yourself or you hide it behind an extension method. Recursive SQL is very bad (many round trips, many single queries).

There are two options here. One is to pull the entire table(s) with the hierarchy into memory and use LINQ to Objects on it. Leave the "details" tables in SQL. If you have less than several thousand entities, this is probably the most efficient way to go. You can keep a single copy of the table(s) in cache and refresh them when necessary. When you need to fetch more detailed data from the DB for a single record, you can reattach that entity from your cached hierarchy to a new DataContext and fetch it.

The other option is to use a more complex relationship model in your database. Storing parent only by nature demands recursion, but you can use the adjacency list model to construct a single query which can span many levels of inheritance. This will mean your LINQ to SQL queries become less intuitive (querying against Entity.Right and Entity.Left isn't quite as pretty as Parent or Children...) but you can do in one query what might take hundreds or thousands in the literal recursive approach.