Linq Group By & Sum Query On Multiple Columns

Question

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);

I have 10 Columns in My Database Like

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

Now What I want is to get the sum of Ration_Card_Counts and Its Type from its Type

Expected Output :

CardType_Name
CardType_Count

Well the above code works fine but I want to optimize it in max possible way as this will be inside a Loop and there are about 1.5 million records.

Thanks

You can try Union but may be you need to write a SQL query if you care about performance — Thomas, Dec 14 '15 at 11:28
thanks @Thomas for the reply is their any suitable suggestion to modify the database if possible — Arijit Mukherjee, Dec 14 '15 at 11:29
You can write a store procedure and call it from your code, what are you using to connect to your database , entity framework ? — Thomas, Dec 14 '15 at 11:31
The schema is not normalized. Why not create just a table with `TypeId` and `Count` and other for `TypeId` and `TypeName`? It might make your other queries faster/simpler. — Piyush Parashar, Dec 14 '15 at 11:32
@PiyushParashar every row will have more than 1 type and counts — Arijit Mukherjee, Dec 14 '15 at 11:36
Where to start... There are about 10 obvious code-smells here. You need to change your database schema for a start. Also ... 1) 'DRY' your code 2) Why do you have ToList everywhere? Run a query profiler over this - long winded things will be happening. 3) Why do you have 5 "types" as separate columns? What happens when you introduce type6? 4) var GodownRCCounts = GodownRCCount; -- strange and confusing... 5) Even if separate repeated columms were a good idea, doesn't your code scream "use a loop" to you? 6) if you need to optimise something with '15 lakhs records', you have bigger issues. — Jon Rea, Dec 14 '15 at 11:41
ArijitMukherjee - That is precisely what I am talking about. The schema is not not normalized. Please read more about normal forms in database. Will all rows have all the values? In that case you end up with empty columns that will still be checked in your queries. And like @JonRea suggested, what happens when you have one more Ration Card? You have to update the whole table. — Piyush Parashar, Dec 14 '15 at 11:48
@JonRea can you explain a bit about DRY your code 3. Cos Every may or may not have 4 different cards types and number of counts — Arijit Mukherjee, Dec 14 '15 at 12:31
@PiyushParashar yes most of the cases all rows will have all the values Yes in case of 1 new ration card type i have to update the table where as for now we only have 4 types and i have kept 1 for future use — Arijit Mukherjee, Dec 14 '15 at 12:33

score 1 · Answer 1 · answered Dec 14 '15 at 12:10

Union should operate faster than AddRange! you can try the following:

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

In general from grouping I would go with Thomas! Database grouping is far better for me, since you are fetching the data required aggregated and therefore the data transferred over the network if much less!

Yes indeed! In this case Concat would be faster than Union since you know that the lists contain unique values. As when you do a "UNION ALL" in SQL if this was done from SQL — Michael Kanios, Dec 14 '15 at 13:31

score 0 · Answer 2 · answered Dec 14 '15 at 11:55

You can rewrite the same query using SQL and put some indexes(performance) :

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

I am not sure but this query make me think about UNPIVOT maybe you can investigate in this direction too.

Linq Group By & Sum Query On Multiple Columns

2 Answers2