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 BusinessUnits { get; set; } } public class BusinessUnit { public IEnumerable 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 wan…

Accepted Answer

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.

How can I query this hierarchical data using LINQ?

Solution

Problem