c# - 如何在具有最大值的 linq 中构建具有语句？

Question

我有一组包含交易数据的对象。该数据集具有 3 个级别的父子关系，级别为:项目 > 阶段 > 事务。
对于每个项目和阶段，都会有多个交易。

class CostElement
{
    string ProjectNumber { get; set; }
    string Stage { get; set; }
    string DerivedCostElement { get; set; }
    DateTime? Date { get; set; }
    decimal? Amount { get; set; }
}

我想要找到的是一个唯一的成本元素列表( DerivedCostElement )，并计算它在任何给定的单个项目中使用的最大次数。 IE。计算每个项目出现的阶段数，然后选择最大值。

到目前为止我尝试过的是:

//count all cost elements per project
var aggregateQuery = from ce in _costElements
                     group ce by new { ce.ProjectNumber, ce.Stage, ce.DerivedCostElement }
                     into g
                     select new
                     {
                         g.Key.DerivedCostElement,
                         g.Key.ProjectNumber,
                         g.Key.Stage
                     };

//select cost elements having max count
var countQuery = from g in aggregateQuery
                 group g by new { g.DerivedCostElement, g.ProjectNumber }
                 into grp
                 select new CostElementCount
                 {
                     CostElement = grp.Key.DerivedCostElement,
                     ProjectNumber = grp.Key.ProjectNumber,
                     Count = grp.Count()
                 };

return countQuery.ToList();

这在大多数情况下是有效的，我最终会得到每个项目的成本要素列表和每个项目的最大出现次数。然而结果仍然是每个项目，我想要的是一个 唯一列表中只有 最大 值(value)。

有人可以帮我获得正确的结果吗？
此外，任何有关如何更有效或更简洁地编写此内容的建议将不胜感激。

Answer 1

我不确定如何更有效地编写前两个查询，但我认为在 return 语句之前添加以下行会得到您想要的结果。

countQuery = countQuery.OrderByDescending(x => x.Count).GroupBy(x => x.CostElement).Select(g => g.First());

或者保持语法更一致

countQuery = from cq in countQuery
             orderby cq.Count descending
             group cq by cq.CostElement
                 into grp
             select grp.First();

这是我使用的测试数据...

_costElements = new List<CostElement>();
_costElements.Add(new CostElement() { ProjectNumber = "a", Stage = "a", DerivedCostElement = "ce6" });
_costElements.Add(new CostElement() { ProjectNumber = "a", Stage = "b", DerivedCostElement = "ce1" });
_costElements.Add(new CostElement() { ProjectNumber = "a", Stage = "x", DerivedCostElement = "ce1" });
_costElements.Add(new CostElement() { ProjectNumber = "a", Stage = "b", DerivedCostElement = "ce2" });
_costElements.Add(new CostElement() { ProjectNumber = "a", Stage = "b", DerivedCostElement = "ce3" });
_costElements.Add(new CostElement() { ProjectNumber = "a", Stage = "c", DerivedCostElement = "ce3" });
_costElements.Add(new CostElement() { ProjectNumber = "a", Stage = "d", DerivedCostElement = "ce3" });
_costElements.Add(new CostElement() { ProjectNumber = "b", Stage = "e", DerivedCostElement = "ce1" });
_costElements.Add(new CostElement() { ProjectNumber = "b", Stage = "f", DerivedCostElement = "ce1" });
_costElements.Add(new CostElement() { ProjectNumber = "c", Stage = "g", DerivedCostElement = "ce1" });
_costElements.Add(new CostElement() { ProjectNumber = "c", Stage = "h", DerivedCostElement = "ce1" });
_costElements.Add(new CostElement() { ProjectNumber = "c", Stage = "h", DerivedCostElement = "ce2" });
_costElements.Add(new CostElement() { ProjectNumber = "c", Stage = "i", DerivedCostElement = "ce2" });
_costElements.Add(new CostElement() { ProjectNumber = "d", Stage = "j", DerivedCostElement = "ce2" });
_costElements.Add(new CostElement() { ProjectNumber = "d", Stage = "k", DerivedCostElement = "ce1" });
_costElements.Add(new CostElement() { ProjectNumber = "d", Stage = "l", DerivedCostElement = "ce1" });
_costElements.Add(new CostElement() { ProjectNumber = "d", Stage = "m", DerivedCostElement = "ce1" });

我想出的结果(对 countQuery 结果进行了一些重新排序)是......

成本元素:ce1 项目编号:d 计数:3

成本元素:ce2 项目编号:c 计数:2

成本元素:ce3 项目编号:一个 计数:3

成本元素:ce6 项目编号:a 计数:1

我认为这是所有成本要素的列表，以及项目并计算其中每个要素出现最多的位置。该输出来自我添加到 CostElementCount 的 ToString()

public string ToString()
{
    return string.Format("CostElement: {0} ProjectNumber: {1} Count: {2}", CostElement, ProjectNumber, Count);
}