我该如何使用LINQ查询这个分层数据？

Question

我有三种对象：机构（Agency）、事业部（BusinessUnit）和客户（Client）（每个都有自己的表）。

从层次结构上看，机构拥有事业部，而事业部则拥有客户。

我有三个 C# POCO 对象来代表它们（通常我会选择使用 new {} 创建它们，而不是使用 LINQ 生成的类）：

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; }
}

您可以看到，机构包含业务单元的列表，而业务单元包含客户的列表。

我还在数据库中有一个名为BAC_Map的映射表，它表示谁拥有谁，并且看起来像这样：

如何构建查询，以便可以查询并返回机构列表？这意味着我希望每个机构都有其BusinessUnit对象集合，并且我希望BusinessObjects列表具有其客户列表集合。

我可以进行基本的LINQ查询，但是对于Map表和多个查询，这有点超出了我的能力范围。

我该如何构建像GetAllAgencies（）这样的方法，它不仅查询所有机构，还填充其所拥有的BusinessUnits以及这些BusinessUnits所拥有的客户？

编辑：任何提示或信息都将不胜感激。我需要做连接吗？这需要是多个查询来返回已填充其子成员的机构列表吗？

- KingNestor

Answer 1

如果您将 Linq to SQL 设计器上的四个表（Agency、BusinessUnit、Client、Map）全部删除，并从 Map 到其他三个表绘制关系，则 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();

如果提供了适当的过滤器（参见已注释的where子句），则只会将所需部分的表拉入内存。这是标准的SQL连接操作。

- Amy B

---

@David B，这看起来非常有前途！我很好奇，你能详细说明一下被注释掉的where语句吗？鉴于我的要求，你认为我应该放置什么条件呢？ - KingNestor

如果你真的需要“全部”机构，那么就不应该进行过滤。通常情况下，你并不需要数据库中的所有数据，所以提供任何条件都会限制记录的读取和传输。 - Amy B

@David B，当我输入您查询的第一部分时：from a in m.Agency，我立即收到一个错误提示，指出：“在源类型为Linq.Table<DomainModel.Bac_Map>的查询表达式中，不允许后续 from 子句中使用类型为'DomainModel.Agency'的表达式。在调用“Select Many”时，类型推断失败。” - KingNestor

我想我的问题是，如何在链中更高层次的一个对象中包含属性？例如，如果机构包含“名称”和IEnumberable<BusinessUnit>，该怎么办？ - KingNestor

@David B，假设我按照你的方式选择了AgencyName路线。在第二个查询中，我如何访问select new Agency() {}部分中的AgencyName？您是基于AgencyID进行分组的，我明白，但是我如何从该分组“g”中访问AgencyName，而我又从第一个查询中包含它呢？ - KingNestor

Answer 2

我在SQL Server数据库中创建了您的表，并尝试在LinqPad中重新创建您的场景。最终我得到了以下LINQ语句，它们基本上会产生与您的POCO类相同的结构：

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();

请注意，我在第二个查询中调用了map.ToList()。这实际上导致了一个单一的、高效的查询。我的最初尝试没有包括.ToList()，结果产生了9个单独的查询来产生相同的结果。.ToList()版本生成的查询如下：

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]

这是结果的屏幕截图： alt text http://img411.imageshack.us/img411/5003/agencybusinessunitclien.png

- jrista

Answer 3

如果您正在使用直接LINQ to SQL进行此操作，则无法在没有某种递归的情况下完成此操作，无论是自己完成还是将其隐藏在扩展方法后面。递归SQL非常糟糕（许多往返，许多单个查询）。

这里有两个选项。一个是将整个表（s）与层次结构一起加载到内存中，并在其上使用LINQ to Objects。将“详细信息”表留在SQL中。如果您的实体少于几千个，则这可能是最有效的方法。您可以在缓存中保留表（s）的单个副本，并在必要时刷新它们。当您需要为单个记录从DB获取更详细的数据时，您可以将该实体从缓存的层次结构重新附加到新的DataContext并获取它。

另一个选择是在您的数据库中使用更复杂的关系模型。仅通过存储父级本身就需要递归，但您可以使用邻接列表模型构建一个单个查询，该查询可以跨越许多继承级别。这意味着您的LINQ to SQL查询变得不太直观（针对Entity.Right和Entity.Left进行查询不如Parent或Children...），但您可以在一个查询中完成可能需要数百或数千次的字面递归方法。

- Rex M