CODEWITHSUNDEEP
c#linq
Krunal Sisodiya
Krunal Sisodiya

Reputation: 11

Getting wrong sum and count in linq

This is my table structure

ID   A     B     C     D

1    null  10    5     null
2    3     5     null  D2
3    8     null  2     D2
4    null  4     3     D1
5    4     6     1     D2

This is c# class and its property to store query result.

public class GrillTotals
{
        public int? SumOfA {get; set;}

        public int? SumOfB{get; set;}

        public int? SumOfC{get; set;}

        public int? CountOfD1{get; set;}

        public int? CountOfD2{get; set;}
}

What I expect is:

SumOfA = 15
SumOfB = 20
SumOfC = 11
CountOfD1 = 1
CountOfD2 = 3

What I am getting is : SumOfA = null, SumOfB = null, SumOfC = null, CountOfD1 = 0, CountOfD2 = 0

Here is a code what I have tried. 

var _FinalResult = from s in dbContext.tblSchedules
 group s by new
 {
     s.A,
     s.B,
     s.C,
     s.D,
 } into gt
 select new GrillTotals
 {
     SumOfA = gt.Sum(g => g.A),
     SumOfB = gt.Sum(g => g.B),
     SumOfC = gt.Sum(g => g.C),
     CountOfD1 = gt.Count(g => g.D == "D1"),
     CountOfD2 = gt.Count(g => g.D == "D2"),
 };

Try to correct me if I am doing something wrong or incorrectly.Any help will be appreciated.

Upvotes: 1

Views: 1007

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205859

You should not be grouping by the fields you want to calculate aggregates. When you group by them, every aggregate (Sum, Min, Max etc) will return the value itself (and Count 1 or 0 depending of the condition).

From what I see you are trying to return several aggregates with single SQL query. If that's correct, it's possible by using group by constant technique.

Just replace

 group s by new
 {
     s.A,
     s.B,
     s.C,
     s.D,
 } into gt

with

group s by 1 // any constant
into gt

Upvotes: 2

Michael
Michael

Reputation: 3641

Try this:

 var _FinalResult = from s in dbContext.tblSchedules
 group s by new
 {
     s.A,
     s.B,
     s.C,
     s.D,
 } into gt
 select new GrillTotals
 {
     SumOfA = gt.Sum(g => g.A ?? 0),
     SumOfB = gt.Sum(g => g.B ?? 0),
     SumOfC = gt.Sum(g => g.C ?? 0),
     CountOfD1 = gt.Count(g => g.D == "D1"),
     CountOfD2 = gt.Count(g => g.D == "D2"),
 };

Upvotes: 0

Related Questions