Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

Linq Sum Query

New to linq. Basically I need to write a query  that does 2 sums using entity framework and returns a list.  (A sum of fields from TableA and a Sum of fields from TableB) The fields will be decimals.

Also, I need to handle for the fact that there may not be a row that meets the criteria. (where claimid=claimid and unitid=unitid) . Meaning TableA could bring back 0 rows or TableB could bring back 0 rows and therefore there is nothing to sum. So in this case the result would be 0.

Below is what I have but for some reason it does not seem to bring the correct total back for Table B. Any and all advice or help would be most appreciated.

Jason

        private List<SubroRcvdEntryTotals> GetSubroEntryTotals(int currentClaim)
        {
            return (from a in _subContext.HeUnits
                    where a.Claim == currentClaim
                    join b in _subContext.HeSubroUnits on new { a.Claim, a.UnitId } equals new { b.Claim, b.UnitId } into ps
                    from b in ps.DefaultIfEmpty()
                    select new SubroRcvdEntryTotals
                    {
                        SubroClaim = a.Claim,
                        Unit = a.Unit,
                        RequestedTotal = a.Repair + a.ActualCashValue + a.LossOfUse + a.AdminFee + a.Appraiser + a.DiminValue + a.Other + a.Agency,
                        ReceivedTotal = b.Repair ?? (Decimal)0
                          + b.ActualCashValue ?? (Decimal)0
                          + b.LossOfUse ?? (Decimal)0
                          + b.AdminFee ?? (Decimal)0
                          + b.Appraiser ?? (Decimal)0
                          + b.DiminValue ?? (Decimal)0
                          + b.Interest ?? (Decimal)0
                          + b.Other ?? (Decimal)0
                          + b.Agency ?? (Decimal)0

                        
                    }
                      ).ToList();



     
        }

Open in new window

0
jazzcatone
Asked:
jazzcatone
1 Solution
 
jazzcatoneAuthor Commented:
OK so now I have the calculation for TableB working. However, like I said before I need to handle for a null. If there are no rows in tableB I'm screwed as it blows up with

 The cast to value type 'Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."}

Any and all advice on how to handle for this would be greatly appreciated.

--Jason

        private List<SubroRcvdEntryTotals> GetSubroEntryTotals(int currentClaim)
        {
            
            return (from a in _subContext.HeUnits
                    where a.Claim == currentClaim
                    join b in _subContext.HeSubroUnits on new { a.Claim, a.UnitId } equals new { b.Claim, b.UnitId } into ps
                    from b in ps.DefaultIfEmpty()
                    select new SubroRcvdEntryTotals
                    {
                        SubroClaim = a.Claim,
                        Unit = a.Unit,
                        RequestedTotal = a.Repair + a.ActualCashValue + a.LossOfUse + a.AdminFee + a.Appraiser + a.DiminValue + a.Other + a.Agency,


                                ReceivedTotal = b.Repair.Value+ b.LossOfUse.Value + b.AdminFee.Value+ b.Appraiser.Value + b.DiminValue.Value + b.Interest.Value+ b.Other.Value+ b.Agency.Value
                    }
                      ).ToList();



        
        }

Open in new window

0
 
Mihai StancescuSoftware Engineer Commented:
Hi,

Try verifying if b is null when you sum ReceivedTotal .

Like so:
private List<SubroRcvdEntryTotals> GetSubroEntryTotals(int currentClaim)
        {
            
            return (from a in _subContext.HeUnits
                    where a.Claim == currentClaim
                    join b in _subContext.HeSubroUnits on new { a.Claim, a.UnitId } equals new { b.Claim, b.UnitId } into ps
                    from b in ps.DefaultIfEmpty()
                    select new SubroRcvdEntryTotals
                    {
                        SubroClaim = a.Claim,
                        Unit = a.Unit,
                        RequestedTotal = a.Repair + a.ActualCashValue + a.LossOfUse + a.AdminFee + a.Appraiser + a.DiminValue + a.Other + a.Agency,
                        ReceivedTotal = b == null ? 0 : (b.Repair.Value+ b.LossOfUse.Value + b.AdminFee.Value+ b.Appraiser.Value + b.DiminValue.Value + b.Interest.Value+ b.Other.Value+ b.Agency.Value)
                    }).ToList();        
        }

Open in new window


Hope this helps,
Mishu
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now