Linq 分组依据和对多个列进行求和查询

本文关键字：查询 求和 Linq | 更新日期: 2023-09-27 18:26:51

var fpslist = db.FPSinformations.Where(x => x.Godown_Code != null && x.Godown_Code == godownid).ToList();
        var data1 = fpslist.GroupBy(x => x.Ration_Card_Type1)
            .Select(x => new
            {
                CardType_Name = x.Key,
                CardType_Count = x.Sum(y => y.Ration_Card_Count1)
            }).ToList();
        var data2 = fpslist.GroupBy(x => x.Ration_Card_Type2)
            .Select(x => new
            {
                CardType_Name = x.Key,
                CardType_Count = x.Sum(y => y.Ration_Card_Count2)
            }).ToList();
        var data3 = fpslist.GroupBy(x => x.Ration_Card_Type3)
            .Select(x => new
            {
                CardType_Name = x.Key,
                CardType_Count = x.Sum(y => y.Ration_Card_Count3)
            }).ToList();
        var data4 = fpslist.GroupBy(x => x.Ration_Card_Type4)
            .Select(x => new
            {
                CardType_Name = x.Key,
                CardType_Count = x.Sum(y => y.Ration_Card_Count4)
            }).ToList();
        var data5 = fpslist.GroupBy(x => x.Ration_Card_Type5)
            .Select(x => new
            {
                CardType_Name = x.Key,
                CardType_Count = x.Sum(y => y.Ration_Card_Count5)
            }).ToList();
        var GodownRCCount = data1.Where(x => x.CardType_Name != null).ToList();
        var GodownRCCounts = GodownRCCount;
        GodownRCCount = data2.Where(x => x.CardType_Name != null).ToList();
        GodownRCCounts.AddRange(GodownRCCount);
        GodownRCCount = data3.Where(x => x.CardType_Name != null).ToList();
        GodownRCCounts.AddRange(GodownRCCount);
        GodownRCCount = data4.Where(x => x.CardType_Name != null).ToList();
        GodownRCCounts.AddRange(GodownRCCount);
        GodownRCCount = data5.Where(x => x.CardType_Name != null).ToList();
        GodownRCCounts.AddRange(GodownRCCount);

我的数据库中有 10 列，例如

Ration_Card_Type1
Ration_card_count1
Ration_Card_Type2
Ration_card_count2
Ration_Card_Type3
Ration_card_count3
Ration_Card_Type4
Ration_card_count4
Ration_Card_Type5
Ration_card_count5

现在我想要的是从其类型中获取Ration_Card_Counts及其类型的总和

预期输出 ：

CardType_Name
CardType_Count

好吧，上面的代码工作正常，但我想以最大可能的方式优化它，因为这将在循环中并且大约有 150 万条记录。

谢谢

Linq 分组依据和对多个列进行求和查询

Union 的运行速度应该比 AddRange 快！您可以尝试以下操作：

var data = (from g in fpslist.GroupBy(x => x.Ration_Card_Type1).Select(x => new
            {
                CardType_Name = x.Key,
                CardType_Count = x.Sum(y => y.Ration_Card_Count1)
            }).Union(
              fpslist.GroupBy(x => x.Ration_Card_Type2).Select(x => new
              {
                  CardType_Name = x.Key,
                  CardType_Count = x.Sum(y => y.Ration_Card_Count2)
              })).Union(
              fpslist.GroupBy(x => x.Ration_Card_Type3).Select(x => new
              {
                  CardType_Name = x.Key,
                  CardType_Count = x.Sum(y => y.Ration_Card_Count3)
              })).Union(
             fpslist.GroupBy(x => x.Ration_Card_Type4).Select(x => new
             {
                 CardType_Name = x.Key,
                 CardType_Count = x.Sum(y => y.Ration_Card_Count4)
             })).Union(
             fpslist.GroupBy(x => x.Ration_Card_Type5).Select(x => new
             {
                 CardType_Name = x.Key,
                 CardType_Count = x.Sum(y => y.Ration_Card_Count5)
             }))
                        select g).ToList();
一般来说，从

分组中，我会和托马斯一起去！数据库分组对我来说要好得多，因为您正在获取所需的聚合数据，因此通过网络传输的数据(如果少得多(！

您可以使用SQL重写相同的查询并放置一些索引(性能(：

SELECT Ration_Card_Type = Ration_Card_Type1, Ration_Card_Count = sum(Ration_card_count1)
FROM 
   FPSinformations
GROUP BY
    Ration_Card_Type1
UNION
SELECT Ration_Card_Type = Ration_Card_Type2, Ration_Card_Count = sum(Ration_card_count2)
FROM 
   FPSinformations
GROUP BY
    Ration_Card_Type2
UNION
SELECT Ration_Card_Type = Ration_Card_Type3, Ration_Card_Count = sum(Ration_card_count3)
FROM 
   FPSinformations
GROUP BY
    Ration_Card_Type3
UNION
SELECT Ration_Card_Type = Ration_Card_Type4, Ration_Card_Count = sum(Ration_card_count4)
FROM 
   FPSinformations
GROUP BY
    Ration_Card_Type4
UNION
SELECT Ration_Card_Type = Ration_Card_Type5, Ration_Card_Count = sum(Ration_card_count5)
FROM 
   FPSinformations
GROUP BY
    Ration_Card_Type5

我不确定，但这个查询让我想到了 UNPIVOT，也许你也可以朝这个方向调查。

相关文章：