CODEWITHSUNDEEP
linqlinq-to-sql
psdpainter
psdpainter

Reputation: 666

LINQ to Sql group and sum in subquery but account for null values

I have the following query which works as expected until it reaches nullvalues from the database. 

var accounts = from p in db.PSR_FINAL_DATAs
            join c in db.PROJ_CUSTOMs on p.PROJ_ID equals c.PROJ_ID
            where !SqlMethods.Like(p.ACCT_ID, "%704-10-12%") &&
                    !SqlMethods.Like(p.ACCT_ID, "%-01%") &&
                    !SqlMethods.Like(p.ACCT_ID, "%706-10-12%") &&
                    SqlMethods.Like(p.PROJ_ID, "70000.%") &&
                    p.FY_CD == "2014"
            group p by new
            {
                p.ACCT_ID,
                p.PROJ_ID,
                c.PROJ_NAME
            }
                into s
                select new
                {
                    Project = s.Key.PROJ_ID,
                    Account = s.Key.ACCT_ID,
                    ProjectNumber = s.Key.PROJ_NAME,
                    July = s.Where(a => a.PD_NO == 1).Sum(b=> b.PTD_INCUR_AMT),
                    Aug = s.Where(a => a.PD_NO == 2).Sum(b => b.PTD_INCUR_AMT),
                    Sep = s.Where(a => a.PD_NO == 3).Sum(b => b.PTD_INCUR_AMT),
                    Oct = s.Where(a => a.PD_NO == 4).Sum(b => b.PTD_INCUR_AMT),
                    Nov = s.Where(a => a.PD_NO == 5).Sum(b => b.PTD_INCUR_AMT),
                    Dec = s.Where(a => a.PD_NO == 6).Sum(b => b.PTD_INCUR_AMT),
                    Jan = s.Where(a => a.PD_NO == 7).Sum(b => b.PTD_INCUR_AMT),
                    Feb = s.Where(a => a.PD_NO == 8).Sum(b => b.PTD_INCUR_AMT),
                    Mar = s.Where(a => a.PD_NO == 9).Sum(b => b.PTD_INCUR_AMT),
                    Apr = s.Where(a => a.PD_NO == 10).Sum(b => b.PTD_INCUR_AMT),
                    May = s.Where(a => a.PD_NO == 11).Sum(b => b.PTD_INCUR_AMT),
                    June = s.Where(a => a.PD_NO == 12).Sum(b => b.PTD_INCUR_AMT),
                    Total = string.Format("{0:C}", s.Sum(y => y.PTD_INCUR_AMT))
                };

How do I modify the query s.Where(a => a.PD_NO == 1).Sum(b=> b.PTD_INCUR_AMT) to account for null values?

EDIT:

I tried the following s == null ? 0 : s.Where(a => a.PD_NO == 5).Sum(b => b.PTD_INCUR_AMT) but this is the error message I receive: 

The null value cannot be assigned to a member with type System.Decimal which is a non-   nullable value type.

Upvotes: 1

Views: 724

Answers (1)

psdpainter
psdpainter

Reputation: 666

Ok just figured it out...this is the solution: 

s.Where(a => a.PD_NO == 'whatever').Sum(b => (decimal?)b.PTD_INCUR_AMT)

Upvotes: 2

Related Questions