如何使用LINQ查询此分层数据？

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

我有3种对象:Agency,BusinessUnit和Client(每个都有各自的表)

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

在层次结构方面,代理商拥有BusinessUnits,BusinessUnits拥有客户。

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

我有3个C#POCO对象来表示它们(我通常选择新的{},而不是使用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; }
}

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

您可以看到代理商包含BusinessUnit列表,BusinessUnits包含客户列表。

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

我在数据库中也有一个名为BAC_Map的映射表,它说哪个拥有哪个,它看起来像这样:

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.

如何构建查询,以便查询并返回代理列表?这意味着,我希望每个代理商都设置其BusinessUnit对象列表,并且我希望BusinessObjects列表设置其客户端列表。

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

我可以做基本的LINQ查询,但这对于Map表和多个来说有点过头了吗?查询。

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?

我怎样才能构建像GetAllAgencies()这样的方法,它不仅可以查询所有代理商,还可以查询代理商拥有的BusinessUnit,以及那些BusinessUnits拥有的客户端?

---

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?

编辑:任何提示或信息表示赞赏。我需要加入吗?这是否需要多个查询才能返回代理商列表,并填充其子成员?

3 个解决方案

#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.

如果将linq上的所有四个表(Agency,BusinessUnit,Client,Map)放到sql设计器上,并从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();

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.

如果提供了适当的过滤器(参见注释掉的where子句),那么只有表中所需的部分才会被拉入内存。这是标准的SQL加入工作。

#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:

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

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:

请注意,我在第二个查询中调用了map.ToList()。这实际上导致了单个有效的查询。我的初始尝试不包括.ToList(),并导致九个单独的查询产生相同的结果。 .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]

Here is a screenshot of the results:

以下是结果的屏幕截图:

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

替代文字http://img411.imageshack.us/img411/5003/agencybusinessunitclien.png

#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).

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

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.

这里有两个选择。一种是将具有层次结构的整个表拉入内存并在其上使用LINQ to Objects。在SQL中保留“details”表。如果您的实体少于几千个,这可能是最有效的方法。您可以在缓存中保留表的单个副本,并在必要时刷新它们。当您需要从数据库中获取单个记录的更详细数据时,可以将该实体从缓存层次结构重新附加到新的DataContext并获取它。

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.

另一种选择是在数据库中使用更复杂的关系模型。仅按性质存储父级需要递归,但您可以使用邻接列表模型构建可以跨越多个继承级别的单个查询。这将意味着您的LINQ to SQL查询变得不那么直观(查询Entity.Right和Entity.Left并不像Parent或Children那么漂亮......)但是您可以在一个查询中执行可能需要数百或数千的查询字面递归方法。