none
Trying to handle for possible Nulls RRS feed

  • Question

  • 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 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,

    //This below is the calculation for TableB and does not work

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



    Jason

    Tuesday, March 4, 2014 8:13 PM

Answers

  • Hi Jazzcatone;

    It might be that b itself is null and you are not testing for that. Is your code throwing and exception? Please give this modified code a try and see if it rectifies the issue.

    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)
                      ? 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
                      : (Decimal)0
                }).ToList();
    }

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Marked as answer by Jazzcatone Wednesday, March 5, 2014 10:21 PM
    Tuesday, March 4, 2014 8:57 PM

All replies

  • Hi Jazzcatone;

    It might be that b itself is null and you are not testing for that. Is your code throwing and exception? Please give this modified code a try and see if it rectifies the issue.

    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)
                      ? 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
                      : (Decimal)0
                }).ToList();
    }

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Marked as answer by Jazzcatone Wednesday, March 5, 2014 10:21 PM
    Tuesday, March 4, 2014 8:57 PM
  • The error I was getting was

    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."}

    What you said was exactly the case. TableB was null. I just didn't know how to check/handle for it . Thank you very much.

    Jason


    Jason

    Tuesday, March 4, 2014 10:32 PM
  • Hi Jason;

    Glad that has corrected the issue. If that concluded the solution to the question please close the thread by excepting the solution as the answer.

    Thank you and have a great day.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Wednesday, March 5, 2014 7:35 PM