CODEWITHSUNDEEP
c#asp.netlinq
fares Ayyad
fares Ayyad

Reputation: 333

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

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?

Upvotes: 1

Views: 123

Answers (3)

Eldaniz Ismayilov
Eldaniz Ismayilov

Reputation: 856

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

Upvotes: 0

Robert McKee
Robert McKee

Reputation: 21487

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

Upvotes: 0

Zinov
Zinov

Reputation: 4119

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

Upvotes: 2

Related Questions