.net GroupBy()无法按预期工作，除非我首先将各个项加载到内存中

hsvhsicv 于 2023-10-21 发布在 .NET

关注(0)|答案(1)|浏览(117)

我有下面的查询，我期待着一些结果。但我只有一个结果。

var results = await DbContext.Trucks
    .Where(t => t.Departure.HasValue)
    .Select(t => new
    {
        t.Departure!.Value.Year,
        t.Departure!.Value.Month,
        Quantity = t.InboundQuantity + t.ToTransfers.Sum(x => x.Quantity) - t.FromTransfers.Sum(x => x.Quantity)
    })
    .GroupBy(t => new { t.Year, t.Month })
    .ToDictionaryAsync(g => new DateTime(g.Key.Year, g.Key.Month, 1), g => g.Sum(x => x.Quantity));

结果 *

results Count = 1
+ [0]   {[{7/1/2021 12:00:00 AM}, 3842405714]}

如果我重写查询，将所有行加载到内存中，然后分组，那么我将得到预期数量的结果。

var test = DbContext.Trucks
    .Where(t => t.Departure.HasValue)
    .Select(t => new
    {
        t.Departure!.Value.Year,
        t.Departure!.Value.Month,
        Quantity = t.InboundQuantity + t.ToTransfers.Sum(x => x.Quantity) - t.FromTransfers.Sum(x => x.Quantity)
    })
    .ToList();

var test2 = test
    .GroupBy(t => new { t.Year, t.Month })
    .ToDictionary(g => new DateTime(g.Key.Year, g.Key.Month, 1), g => g.Sum(x => x.Quantity));

结果 *

test2   Count = 27
+ [0]   {[{1/1/2022 12:00:00 AM}, 95309170]}
+ [1]   {[{2/1/2022 12:00:00 AM}, 79737674]}
+ [2]   {[{3/1/2022 12:00:00 AM}, 173875538]}
+ [3]   {[{7/1/2021 12:00:00 AM}, 125219196]}
+ [4]   {[{8/1/2021 12:00:00 AM}, 110993954]}
+ [5]   {[{10/1/2021 12:00:00 AM}, 129282632]}
+ [6]   {[{11/1/2021 12:00:00 AM}, 160927222]}
+ [7]   {[{12/1/2021 12:00:00 AM}, 117813444]}
+ [8]   {[{9/1/2021 12:00:00 AM}, 26260420]}
+ [9]   {[{4/1/2022 12:00:00 AM}, 161695966]}
+ [10]  {[{5/1/2022 12:00:00 AM}, 184781001]}
+ [11]  {[{6/1/2022 12:00:00 AM}, 146985217]}
+ [12]  {[{7/1/2022 12:00:00 AM}, 84260160]}
+ [13]  {[{8/1/2022 12:00:00 AM}, 89769875]}
+ [14]  {[{10/1/2022 12:00:00 AM}, 187958697]}
+ [15]  {[{11/1/2022 12:00:00 AM}, 175034225]}
+ [16]  {[{9/1/2022 12:00:00 AM}, 108421771]}
+ [17]  {[{1/1/2023 12:00:00 AM}, 179720773]}
+ [18]  {[{12/1/2022 12:00:00 AM}, 110000415]}
+ [19]  {[{2/1/2023 12:00:00 AM}, 210461751]}
+ [20]  {[{3/1/2023 12:00:00 AM}, 232239876]}
+ [21]  {[{4/1/2023 12:00:00 AM}, 176482465]}
+ [22]  {[{5/1/2023 12:00:00 AM}, 218732769]}
+ [23]  {[{6/1/2023 12:00:00 AM}, 186324324]}
+ [24]  {[{7/1/2023 12:00:00 AM}, 151700649]}
+ [25]  {[{8/1/2023 12:00:00 AM}, 142653374]}
+ [26]  {[{9/1/2023 12:00:00 AM}, 75763156]}

我可能错过了一些愚蠢的事情，但我不知道该怎么解释。
为什么第一个查询不像预期的那样工作？

更新

如果有人好奇的话，我可以使用以下语法使这个查询正确工作。请注意GroupBy()的第二个参数和ToDictionaryAsync()调用的一个微小更改。

var results = await query
    .Where(t => t.Departure.HasValue)
    .Select(t => new
    {
        t.Departure!.Value.Year,
        t.Departure!.Value.Month,
        Quantity = t.InboundQuantity + t.ToTransfers.Sum(x => x.Quantity) - t.FromTransfers.Sum(x => x.Quantity)
    })
    .GroupBy(t => new { t.Year, t.Month }, x => x.Quantity)
    .ToDictionaryAsync(g => new DateTime(g.Key.Year, g.Key.Month, 1), g => g.Sum());

.net

来源：https://stackoverflow.com/questions/77268057/groupby-isnt-working-as-expected-unless-i-first-load-the-individual-items-int

1条答案

按热度按时间

rsaldnfx1#

ToDictionaryAsync试图在客户端枚举IGrouping<,>。在调用ToDictionaryAsync之前添加其他Select：

var results = await DbContext.Trucks
    .Where(t => t.Departure.HasValue)
    .Select(t => new
    {
        t.Departure!.Value.Year,
        t.Departure!.Value.Month,
        Quantity = t.InboundQuantity + t.ToTransfers.Sum(x => x.Quantity) - t.FromTransfers.Sum(x => x.Quantity)
    })
    .GroupBy(t => new { t.Year, t.Month })
    .Select(g = new 
    { 
        g.Key.Year, 
        g.Key.Month,
        Quantity = g => g.Sum(x => x.Quantity)
    })
    .ToDictionaryAsync(x => new DateTime(x.Year, x.Month, 1), g => x.Quantity);

更新，因为这里我们有EF Core SQL生成限制，准备了解决方案：

var trucks = DbContext.Trucks
    .Where(t => t.Departure.HasValue);

var toSummary = 
    from t in trucks
    from tr in t.ToTransfers
    group tr by new
    {
        t.Departure!.Value.Year,
        t.Departure!.Value.Month,        
    } into g
    select new 
    {
        g.Key.Year,
        g.Key.Month,
        ToQuantity = g.Sum(x => x.Quantity)
    };

var fromSummary = 
    from t in trucks
    from tr in t.FromTransfers
    group tr by new
    {
        t.Departure!.Value.Year,
        t.Departure!.Value.Month,        
    } into g
    select new 
    {
        g.Key.Year,
        g.Key.Month,
        FromQuantity = g.Sum(x => x.Quantity)
    };

var query = 
    from t in trucks
    from to in toSummary
        .Where(to => t.Year == t.Departure!.Value.Year && t.Month == t.Departure!.Value.Month)
        .DefaultIfEmpty()
    from f in fromSummary
        .Where(f => f.Year == t.Departure!.Value.Year && f.Month == t.Departure!.Value.Month)
        .DefaultIfEmpty()
    select new 
    {
        g.Key.Year, 
        g.Key.Month,
        Quantity = t.InboundQuantity + ((int?)to.ToQuantity) ?? 0 - ((int?)f.FromQuantity) ?? 0
    }

var results = await query
    .ToDictionaryAsync(x => new DateTime(x.Year, x.Month, 1), g => x.Quantity);

赞(0）回复(0）举报 2023-10-21

我来回答

.net GroupBy()无法按预期工作，除非我首先将各个项加载到内存中

更新

1条答案

相关问题

热门标签

最新问答