Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.
share|improve this question
    
First step is to find out which value is null. Then say so here. –  user2864740 Nov 18 '13 at 19:24
    
The null value would be PTD_INCUR_AMT. –  Mark Nov 18 '13 at 19:41
    
And what's the precise error message/symptom? –  user2864740 Nov 18 '13 at 19:47
    
The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type. –  Mark Nov 18 '13 at 19:48
    
It sounds like the database has a NULLABLE column, but the mapping is "NOT NULL". Verify that the type of b.PTD_INCUR_AMT is decimal?. If not, make it so! Also, make sure to put this information into the main post (update it as needed). –  user2864740 Nov 18 '13 at 19:51

1 Answer 1

up vote 0 down vote accepted

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

s.Where(a => a.PD_NO == 'whatever').Sum(b => (decimal?)b.PTD_INCUR_AMT)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.