使用 group by 子句时，如何在 Linq 查询中进行嵌套投影？

Question

我正在尝试处理从 SQL 返回的分组数据。 我正在编写的方法是为“案例状态概述”屏幕提供数据。 它必须生成一个嵌套的 XML 文档。

现在，我可以用简单的方法做到这一点，但我正在尝试了解是否可以使用 linq“group by”语句，然后投影已经嵌套的数据。 （最简单的方法就是从数据库中以表格形式提取数据，然后对其进行 for 循环，形成用于输出的 Xml 文档）

以下是数据层次结构：

每个 Case 都有一个 DebtType，每个 DebtType 都有一个 Client 。

下面是检索数据的 SQL：

SELECT   ClientNames.ClientID                                             ,
         ClientNames.ClientCode                                           ,
         ClientNames.ClientName                                           ,
         DebtTypes.DebtTypeID                                             ,
         DebtTypes.DebtTypeShortDesc                                      ,
         DebtTypes.DebtTypeLongDesc                                       ,
         Cases.CurrentStateCode                                           ,
         SUM(1 - CAST(Cases.CaseClosed AS INT))  AS OpenCaseCount         ,
         SUM(CAST(Cases.CaseClosed AS     INT))  AS ClosedCaseCount       ,
         SUM(CAST(Cases.CaseOnHold AS     INT))  AS OnHoldCaseCount       ,
         SUM(CAST(Cases.CaseReferred AS   INT))  AS ReferredCaseCount     ,
         COUNT(Cases.CaseID)                     AS TotalCaseCount        ,
         SUM(Cases.CaseTotalPaid)                AS TotalAmountPaid       ,
         SUM(Cases.CaseCurrentOutstandingAmount) AS TotalAmountOutstanding,
         SUM(Cases.CaseTotalDebtWrittenOff)      AS TotalAmountWrittenOff ,
         SUM(Cases.CaseTotalDebtCancelled)       AS TotalAmountCancelled
FROM     ClientNames
         INNER JOIN ClientDebtTypes
         ON       ClientNames.ClientID = ClientDebtTypes.ClientID
         INNER JOIN DebtTypes
         ON       ClientDebtTypes.DebtTypeID = DebtTypes.DebtTypeID
         INNER JOIN Cases
         ON       ClientDebtTypes.ClientDebtTypeID = Cases.CaseClientDebtTypeID
GROUP BY ClientNames.ClientID       ,
         ClientNames.ClientCode     ,
         ClientNames.ClientName     ,
         DebtTypes.DebtTypeID       ,
         DebtTypes.DebtTypeShortDesc,
         DebtTypes.DebtTypeLongDesc ,
         Cases.CurrentStateCode
ORDER BY ClientNames.ClientID,
         DebtTypes.DebtTypeID,
         CurrentStateCode

使用 Linqer 将其转换为：

from clientnames in db.ClientNames
join clientdebttypes in db.ClientDebtTypes on clientnames.ClientID equals clientdebttypes.ClientID
join debttypes in db.DebtTypes on clientdebttypes.DebtTypeID equals debttypes.DebtTypeID
join cases in db.Cases on new { ClientDebtTypeID = clientdebttypes.ClientDebtTypeID } equals new { ClientDebtTypeID = cases.CaseClientDebtTypeID }
group new {clientnames, debttypes, cases} by new {
  clientnames.ClientID,
  clientnames.ClientCode,
  clientnames.ClientName1,
  debttypes.DebtTypeID,
  debttypes.DebtTypeShortDesc,
  debttypes.DebtTypeLongDesc,
  cases.CurrentStateCode
} into g
orderby
  g.Key.ClientID,
  g.Key.DebtTypeID,
  g.Key.CurrentStateCode
select new {
  ClientID = (System.Int32?)g.Key.ClientID,
  g.Key.ClientCode,
  g.Key.ClientName1,
  DebtTypeID = (System.Int32?)g.Key.DebtTypeID,
  g.Key.DebtTypeShortDesc,
  g.Key.DebtTypeLongDesc,
  g.Key.CurrentStateCode,
  OpenCaseCount = (System.Int64?)g.Sum(p => 1 - Convert.ToInt32(p.cases.CaseClosed)),
  ClosedCaseCount = (Int32?)g.Sum(p => Convert.ToInt32(p.cases.CaseClosed)),
  OnHoldCaseCount = (Int32?)g.Sum(p => Convert.ToInt32(p.cases.CaseOnHold)),
  ReferredCaseCount = (Int32?)g.Sum(p => Convert.ToInt32(p.cases.CaseReferred)),
  TotalCaseCount = (Int64?)g.Count(p => p.cases.CaseID != null),
  TotalAmountPaid = (System.Decimal?)g.Sum(p => p.cases.CaseTotalPaid),
  TotalAmountOutstanding = (System.Decimal?)g.Sum(p => p.cases.CaseCurrentOutstandingAmount),
  TotalAmountWrittenOff = (System.Decimal?)g.Sum(p => p.cases.CaseTotalDebtWrittenOff),
  TotalAmountCancelled = (System.Decimal?)g.Sum(p => p.cases.CaseTotalDebtCancelled)
}

现在正如我提到的，我可以停在那里并使用 for 循环来创建 Xml 数据。 但我正在尝试创建一个嵌套组 (IGrouping>) 然后以嵌套格式投影数据。

现在我们使用 LinqToXsd 为 out Xml 文档创建强类型包装器，但本质上这意味着 out 输出类型是：

private class ClientSummary
{
    public string ClientName { get; set; }
    public IList<DebtTypeSummary> DebtTypes { get; set; }
}

private class DebtTypeSummary
{
    public string DebtType { get; set; }
    public IList<StateCodeSummary> StateCodes { get; set; }
}

private class StateCodeSummary
{
    public string StateCode { get; set; }
    public int TotalCount { get; set; }
    public decimal TotalAmountPaid { get; set; }
    //etc
    //etc
    //etc
}

现在我编写了以下 Linq：

var grouping = from cases in db.Cases
              join clientdebttypes in db.ClientDebtTypes on cases.CaseClientDebtTypeID equals clientdebttypes.ClientID
              join debttypes in db.DebtTypes on clientdebttypes.DebtTypeID equals debttypes.DebtTypeID
              group cases by new ClientDebtTypePair() { ClientDebtType = clientdebttypes, DebtType = debttypes } into casesByClientDebtTypes
              join clientnames in db.ClientNames on casesByClientDebtTypes.Key.ClientDebtType.ClientName equals clientnames
              group casesByClientDebtTypes by clientnames;

var projected = from casesByClientDebtTypes in grouping
            let client = casesByClientDebtTypes.Key
            select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
              {
                  Client = new Client()
                  {
                      ClientID = client.ClientID,
                      DisplayName = client.ClientName1,
                  },
                  DebtTypes = from cases in casesByClientDebtTypes
                              let debttype = cases.Key.DebtType
                              select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType.DebtTypesLocalType()
                              {
                                   DebtType = new DebtType()
                                   {
                                        DebtTypeID = debttype.DebtTypeID,
                                         Description = debttype.DebtTypeLongDesc,
                                          DisplayName = debttype.DebtTypeShortDesc,
                                   },
                                    StatesCodes = from cases2 in cases
                                                  select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType.DebtTypesLocalType.StatesCodesLocalType()
                                                  {
                                                       ClosedCasesCount = cases2.Sum(p => Convert.ToInt32(p.cases.CaseClosed))

它连接并分组数据库表，然后尝试将结果投影为 ClientSummary（类名称不同，但这是因为上面是输出类的简化视图）。当我一直深入到 Cases 表时，我完全失败了，我发现我并不真正理解如何执行聚合函数。它们似乎只在 IGrouping 上可用，看来我只是感到困惑。

我还需要确保摘要是在服务器端计算的，撤回数百万个案例会很糟糕。

有人可以帮我解决这个问题吗？这可能吗？

问候，

詹姆斯。

--------### UPDATE 1 ###-------

好的，今天又开始研究这个问题了。 我决定使用 Linq2SQL 来提取包 2D 数据，然后使用 Linq2Objects 重新格式化它。

这是我开始的内容：

var sql = from clientnames in db.ClientNames
      join clientdebttypes in db.ClientDebtTypes on clientnames.ClientID equals clientdebttypes.ClientID
      join debttypes in db.DebtTypes on clientdebttypes.DebtTypeID equals debttypes.DebtTypeID
      join cases in db.Cases on new { ClientDebtTypeID = clientdebttypes.ClientDebtTypeID } equals new { ClientDebtTypeID = cases.CaseClientDebtTypeID }
      group new { clientnames, debttypes, cases } by new
      {
          clientnames.ClientID,
          clientnames.ClientCode,
          clientnames.ClientName1,
          debttypes.DebtTypeID,
          debttypes.DebtTypeShortDesc,
          debttypes.DebtTypeLongDesc,
          cases.CurrentStateCode
      } into g
      orderby
        g.Key.ClientID,
        g.Key.DebtTypeID,
        g.Key.CurrentStateCode
      select new
      {
          Client = new Client{ ClientID = g.Key.ClientID, DisplayName = g.Key.ClientName1 },
          DebtType = new DebtType{ DebtTypeID = g.Key.DebtTypeID, DisplayName = g.Key.DebtTypeShortDesc, Description = g.Key.DebtTypeLongDesc },
          StateSummary = new LoadCaseStatusOverviewScreenOutput.ClientsLocalType.DebtTypesLocalType.StatesCodesLocalType()
          {
              StateCode = g.Key.CurrentStateCode,
              OpenCasesCount = g.Sum(p => 1 - Convert.ToInt32(p.cases.CaseClosed)),
              ClosedCasesCount = g.Sum(p => Convert.ToInt32(p.cases.CaseClosed)),
              OnHoldCasesCount = g.Sum(p => Convert.ToInt32(p.cases.CaseOnHold)),
              ReferredCasesCount = g.Sum(p => Convert.ToInt32(p.cases.CaseReferred)),
              TotalCasesCount = g.Count(p => p.cases.CaseID != null),
              TotalAmountPaid = g.Sum(p => p.cases.CaseTotalPaid),
              TotalAmountOutstanding = g.Sum(p => p.cases.CaseCurrentOutstandingAmount),
              TotalAmountWrittenOff = g.Sum(p => p.cases.CaseTotalDebtWrittenOff),
              TotalAmountCancelled = g.Sum(p => p.cases.CaseTotalDebtCancelled),
          }
      };
var res = sql.ToList();

output.Clients = (from results in res
              group results by results.Client into resultsByClient
              from resultsByDebtType in
                  (from results in resultsByClient
                   group results by results.DebtType)
              group resultsByDebtType by resultsByClient.Key into resultsByDebtTypeByClient
              select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
              {
                  Client = resultsByDebtTypeByClient.Key,
                  DebtTypes = (from resultsByDebtType in resultsByDebtTypeByClient
                               select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType.DebtTypesLocalType()
                               {
                                   DebtType = resultsByDebtType.Key,
                                   StatesCodes = (from results in resultsByDebtType
                                                  let summary = results.StateSummary
                                                  select results.StateSummary).ToList()
                               }).ToList()
              }).ToList();

它运行，但为每个结果生成一个客户/债务类型/摘要集。因此，尽管本例中只有一个客户端，但我最终会得到 1300 个客户端，而且全部相同。 我将其简化为：

output.Clients = (from results in res
             group results by results.Client into resultsByClient
             select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
             {
                  Client = resultsByClient.Key,
                  DebtTypes = null,
             }).ToList();

产生 1300 个客户。接下来我尝试了这个：

output.Clients = (from results in res
             group results by results.Client.ClientID into resultsByClient
             select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
             {
                  Client = new Client { ClientID = resultsByClient.Key },
                  DebtTypes = null,
             }).ToList();

这产生了一个客户（万岁！）。除非我丢失了所有客户信息（嘘！） 猜测是因为它是通过参考而不是内容来比较客户，所以我写了以下内容：

public partial class Client
{
    public static bool operator ==(Client left, Client right)
    {
        return left.ClientID == right.ClientID;
    }

    public static bool operator !=(Client left, Client right)
    {
        return left.ClientID != right.ClientID;
    }

    public override int GetHashCode()
    {
        return ClientID;
    }
}

那什么也没做。它反复调用 GetHashCode()，我捏造了它以强制它为任何匹配的 ClientID 返回相同的哈希代码，但它仍然创建了 1300 个客户端组。

问候，

詹姆斯。

--------### UPDATE 2 ###-------

好吧，我想我应该尝试让 Linq2Sql 只输出简单的值进行分组：

g.Key.ClientID,
g.Key.ClientName1,
g.Key.DebtTypeID,
g.Key.DebtTypeShortDesc,
g.Key.DebtTypeLongDesc,

然后将测试 Linq2Objects 更改为：

output.Clients = (from results in res
              group results by new { ClientID = results.ClientID, DisplayName = results.ClientName1 } into resultsByClient
              select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
              {
                  Client = new Client { ClientID = resultsByClient.Key.ClientID, DisplayName = resultsByClient.Key.DisplayName },
                  DebtTypes = null,
              }).ToList();

有效。因此，匿名类型按照我希望的方式进行比较，通过内容而不是引用（显然） 这不会：

output.Clients = (from results in res
              group results by new SiDemClient { ClientID = results.ClientID, DisplayName = results.ClientName1 } into resultsByClient
              select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
              {
                  Client = resultsByClient.Key,//new Client { ClientID = resultsByClient.Key.ClientID, DisplayName = resultsByClient.Key.DisplayName },
                  DebtTypes = null,
              }).ToList();

仍然会创建 1300 个组。

因此，匿名类型以一种我不理解的神奇方式进行比较。如何使我的 Client 类像匿名类型一样进行比较？

问候，

詹姆斯。

--------### 找到解决方案 ###-------

--------### 非常感谢 Enigmativity ###- ------

我需要重写 Equals() 方法而不是实现 == 运算符。 现在分组工作正常了，我有一个很棒的 Xml 文档需要重新显示！

public partial class SiDemClient
{
    public override bool Equals(object obj)
    {
        if (obj is SiDemClient)
        {
            return this.ClientID.Equals(((SiDemClient)obj).ClientID);
        }
        return false;
    }


    public override int GetHashCode()
    {
        return ClientID;
    }
}

非常感谢，

詹姆斯。

Answer 1

当您重写GetHashCode时，您还必须重写Equals。 == & != 运算符是无关紧要的。

尝试一下：

public partial class Client
{
    public override bool Equals(object obj)
    {
        if (obj is Client)
        {
            return this.ClientID.Equals(((Client)obj).ClientID);
        }
        return false;
    }

    public override int GetHashCode()
    {
        return this.ClientID.GetHashCode();
    }
}

看看是否有帮助。