Summing a column gives null in C#/ASP.NET

Question

I have the following sql statement that calculate the sum of the column:

 select coalesce(SUM(cdin_ActMortgageAmnt),0) 
 from CDIndex,company  
 where  comp_companyid=cdin_companyid and comp_idcust like '%10319%' 
 and cdin_Deleted is null and cdin_startunstufdate is not null 
 and cdin_Status='InProgress'

gives me the output like this:

enter image description here

I tried to convert it to LINQ like this:

var sumation = (from com in db.Companies
                join cd in db.CDIndexes on com.Comp_CompanyId equals cd.cdin_CompanyId
                where
                    cd.cdin_Status == "InProgress" &&
                    cd.cdin_startunstufdate == null &&
                    cd.cdin_Deleted == null
                select new {
                    sum = cd.cdin_ActMortgageAmnt 
                }
               );

var summ = sumation.Sum(x => x.sum);

When I put tracePoint beside var summ in debug mode it gives me null when i point to it.

What is the problem?

When you "point to it," is that `var summ...` line the active line, or have you already stepped over it when you observe `null`? — Kenneth K., Dec 12 '16 at 16:39
Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Owen Pauling, Dec 12 '16 at 16:40
@KennethK. What i do excactly is press (F5), the trace point change to yellow arrow so the debugger hit the `var summ` so that i press (F11) to step one line then i point to `var summ` it shows summ | null — fares Ayyad, Dec 12 '16 at 16:44
Compare: `cdin_startunstufdate is not null` and `cd.cdin_startunstufdate == null`. And you're not even trying to filter by `comp_idcust`. Your 2 queries (SQL and LINQ) are different totally. — Jamiec, Dec 12 '16 at 16:45
@Jamiec Thank you very much i handle the two queries to be the same. — fares Ayyad, Dec 12 '16 at 17:03

score 2 · Answer 1 · answered Dec 12 '16 at 16:50

2

On your case you are using coalesce(SUM(cdin_ActMortgageAmnt),0) because some values of cdin_ActMortgageAmnt can be null and you are giving the default value of 0, you need to do the same in your final query. Something like this when you do the select

cd.cdin_ActMortgageAmnt ?? 0

answered Dec 12 '16 at 16:50

Zinov

3,817
5
36
70

` select new { cddd= cd.cdin_ActMortgageAmnt==null?0: cd.cdin_ActMortgageAmnt };` does this solve the problem of null values? – fares Ayyad Dec 12 '16 at 17:01
that just gives you a default value for cdin_ActMortgageAmnt, on this case 0 or the value in case and your query. If you are still getting null is because the data doesn't meet the criteria you specified. Also your like statement should be translated using Contains comp_idcust.Contains("10319") – Zinov Dec 12 '16 at 17:39
1

The coalesce isn't the problem, because the Sum will just ignore null values, so it isn't necessary to make sure the nulls are converted to 0 beforehand. It's likely that the problem is his query looking for din_startunstufdate == null instead of != null like his SQL is doing. – Robert McKee Dec 12 '16 at 20:05

score 0 · Accepted Answer · edited Dec 28 '16 at 15:05

0

this query is appropriate your sql query

     var sumation =db.Companies.Join(db.CDIndexes,
                                        com=>com.Comp_CompanyId,
                                        cd=>cd.cdin_companyid,
                                        (com,cd)=>new {com,cd})
                                   .Where(x=>x.com.comp_idcust.Contains("10319") && x.cd.cdin_Status== "InProgress" &&
                                          cd.cdin_startunstufdate != null &&
                                          cd.cdin_Deleted == null)
                                   .Select(x=>new 
                                   {
                                      sum=x.cd.cdin_ActMortgageAmnt ?? 0
                                   }).ToList()

edited Dec 28 '16 at 15:05

Zinov

3,817
5
36
70

answered Dec 12 '16 at 17:18

Eldaniz Ismayilov

856
6
18

Close, but you've reversed `com=>com.Comp_CompanyId, cd=>cd.cdin_companyid,`, and the `.ToList()` isn't required. – Robert McKee Dec 12 '16 at 17:37
@RobertMcKee how I've reversed ? – Eldaniz Ismayilov Dec 12 '16 at 17:43
It should be `cd=>cd.cdin_companyid, com=>com.Comp_CompanyId,` (inner key selection, then outer key) – Robert McKee Dec 12 '16 at 17:52
you reversed the statements on @RobertMcKee answer, the only addition is your select, it doesn't make sense. Copy & paset – Zinov Dec 12 '16 at 17:52
@Zinov He actually posted his answer before I posted mine, so he didn't copy/paste from me. It's a good answer, just a few minor issues. – Robert McKee Dec 12 '16 at 17:56
@Zinov I writed before RobertMcKee. look at answered time – Eldaniz Ismayilov Dec 12 '16 at 17:57
@RobertMcKee there aren't a few minor issues. You said things is unimportant – Eldaniz Ismayilov Dec 12 '16 at 18:04
Well except that the inner query doesn't have a field called `Comp_CompanyId` and will blow up (The outer query has the field `Comp_CompanyId`). And the `.ToList` will cause the lazy execution to stop, where removing it will allow the datasource to do the `.Sum`, so it's a performance issue. – Robert McKee Dec 12 '16 at 18:10
I didn't saw the post time of your answer. Sorry. About the performance of the query @RobertMcKee is right, once you call ToList() the query is executed, that means that the Sum statement is done in memory instead of the server side ;) – Zinov Dec 12 '16 at 18:42

Robert McKee · Answer 3 · 2016-12-12T17:34:44.943

Here is one way:

var summ = db.Companies.Join(
    db.CDIndexes,
    cd => cd.cdin_CompanyId,
    com => Comp_CompanyId,
    (com, cd) => new { com, cd })
  .Where(z=>z.com.comp_idcust.Contains("10319")) // Added "LIKE"
  .Where(z=>z.cd.cdin_Status == "InProgress")
  .Where(z=>z.cd.cdin_startunstufdate != null) // Reversed your condition
  .Where(z=>z.cd.cdin_Deleted == null)
  .Sum(z=>z.cd.cdin_ActMortgageAmnt);

You can also combine all the Wheres together, but I prefer not to in most cases like this:

var summ = db.Companies.Join(
    db.CDIndexes,
    cd => cd.cdin_CompanyId,
    com => Comp_CompanyId,
    (com, cd) => new { com, cd })
  .Where(z=>z.com.comp_idcust.Contains("10319") // Added "LIKE"
    && z.cd.cdin_Status == "InProgress" 
    && z.cd.cdin_startunstufdate != null // Reversed your condition
    && z.cd.cdin_Deleted == null)
  .Sum(z=>z.cd.cdin_ActMortgageAmnt);

Also, Companies should have a navigation property to CDIndexes in your model, which would make this query very simple. — Robert McKee, Dec 12 '16 at 18:12

Summing a column gives null in C#/ASP.NET

3 Answers3