none
Problem with Linq and Nulls RRS feed

  • Question

  • I have a bunch of Linq to SQL queries.  They are not really important to the actualy problem that I have, but I have included them at the bottom in case they really are.

     

    Anyway, what I have is a bunch of linq to SQL queries that I'm executing:

     

    var eventResults = eventInfo.ToList();

    var checkResults = checks.ToList();

    var deductionResults = deductions.ToList();

     

    Then after the above 3 line I'm executing this line

    var results = from r in eventResults

    select new {EventId = r.EventId, EventDescription = r.EventDescription,

    ShipStartDate = r.ShipStartDate, ShipEndDate = r.ShipEndDate, EventTypeId = r.EventTypeId,

    TotalEventCost = r.TotalEventCost, FixedAmount = r.FixedAmount,

    VariableChecksPaid = checkResults.Where(x => x.EventId == r.EventId).Sum(x => x.VariableAmount),

    FixedChecksPaid = checkResults.Where(x => x.EventId == r.EventId).Sum(x => x.FixedAmount),

    VariableDeductionsCleared = deductionResults.Where(x => x.EventId == r.EventId).Sum(x => x.VariableAmount),

    FixedDeductionsCleared = deductionResults.Where(x => x.EventId == r.EventId).Sum(x => x.FixedAmount)

    };

     

    When I try to execute that query results.ToList() I'm getting an error that says it can't put a null value into a decimal data type.  The last 4 fields in the query; VariableChecksPaid, FixedChecksPaid, VariableDeductionsCleared, FixedDeductionsCleared, are looking up information from the other queries and they very like might not contain any data.  So my question is how can I make it now that I want the type that is created to allow null, i.e. decimal?, for the last 4 columns.

     

     

    Below are the queries that are in the first part of the method, in case they are important

     

    var excludedEventTypes = new List<int>()

    {4, 305, 142, 202, 5, 61, 107, 100, 805, 990, 991, 163, 980, 106, 314};

    var dc = new Trade.DataContext();

    dc.Log = new OceanSpray.Data.DebuggerWriter();

    var startDate = (from cal in dc.Calendars

    where cal.MonthSeqId == startMonthSeqId

    select cal.CalendarDate).Min();

    var endDate = (from cal in dc.Calendars

    where cal.MonthSeqId == endMonthSeqId

    select cal.CalendarDate).Max();

    var shipTos = (from st in dc.BaseValuesMatrixDayT1s

    where st.PlanningBrokerId == planningBrokerId

    && st.PlanningCustomerId == planningCustomerId

    && st.TradeSystemId == tradeSystemId

    select new { st.ShipToId, st.PlanningProductId, st.TradeSystemId }).Distinct();

    var eventFunds = from ef in dc.EventCustDayT1s

    join ship in shipTos on new { ef.ShipToId, ef.PlanningProductId } equals new { ship.ShipToId, ship.PlanningProductId}

    where ((ef.ShipStartDate >= startDate && ef.ShipStartDate <= endDate) ||

    (ef.ShipEndDate >= startDate && ef.ShipEndDate <= endDate))

    && ef.Deleted == false

    && !excludedEventTypes.Contains(ef.EventTypeId)

    group ef by new { ef.EventId, ef.EventDescription, ef.ShipToId,

    ef.PlanningProductId, ef.ShipStartDate, ef.ShipEndDate,

    ef.EventTypeId} into result

    select new { result.Key.EventId, result.Key.EventDescription, result.Key.ShipToId,

    result.Key.PlanningProductId, result.Key.ShipStartDate, result.Key.ShipEndDate,

    result.Key.EventTypeId, FixedAmount = result.Sum(x => x.FixedAmount),

    TotalEventCost = result.Sum(x => x.EventTypeId == 100 || x.ShipEndDate < DateTime.Now ?

    x.TotalEventCost - x.OffInvoiceAmount :

    (x.TotalCases < 0 ? 0 : x.TotalCases -

    x.EventReconAdjustmentCases < 0 ? 0 : x.EventReconAdjustmentCases)

    * x.OffInvoiceRate + x.AltConfigCost)

    };

    var checks = from ch in dc.CheckRequestHeaders

    join cd in dc.CheckRequestDetailDays on ch.Id equals cd.CheckRequestHeaderId

    join e in eventFunds on new { cd.ShipToId, cd.PlanningProductId, cd.EventId }

    equals new { e.ShipToId, e.PlanningProductId, e.EventId }

    where ch.PaymentStatusId != 4 && ch.PaymentStatusId != 6

    group cd by new { cd.EventId, cd.ShipToId, cd.PlanningProductId } into result

    select new { result.Key.EventId, result.Key.ShipToId, result.Key.PlanningProductId,

    VariableAmount = result.Sum(x => x.VariableAmount),

    FixedAmount = result.Sum(x => x.FixedAmount)

    };

    var deductions = from dh in dc.DeductionRequestHeaders

    join dd in dc.DeductionRequestDetailDays on dh.Id equals dd.DeductionRequestHeaderId

    join e in eventFunds on new {dd.ShipToId, dd.PlanningProductId, dd.EventId }

    equals new {e.ShipToId, e.PlanningProductId, e.EventId }

    where dh.PaymentStatusId != 4 && dh.PaymentStatusId != 6

    group dd by new {dd.EventId, dd.ShipToId, dd.PlanningProductId } into result

    select new { result.Key.EventId, result.Key.ShipToId, result.Key.PlanningProductId,

    VariableAmount = result.Sum(x => x.VariableAmount),

    FixedAmount = result.Sum(x => x.VariableAmount)

    };

     

    var eventInfo = from e in eventFunds

    group e by new { e.EventId, e.EventDescription, e.ShipStartDate, e.ShipEndDate, e.EventTypeId } into result

    select new

    {

    result.Key.EventId, result.Key.EventDescription, result.Key.ShipStartDate, result.Key.ShipEndDate,

    result.Key.EventTypeId,

    TotalEventCost = result.Sum(x => x.TotalEventCost),

    FixedAmount = result.Sum(x => x.FixedAmount),

    };

    Thursday, February 28, 2008 9:29 PM

Answers

  • You have to use a concrete type instead of an anonymous type in your select statement (i.e. select new SomeTypeIDeclared { ... } ). It is there you can declare the member's type as decimal?

    Thursday, February 28, 2008 9:35 PM

All replies

  • You have to use a concrete type instead of an anonymous type in your select statement (i.e. select new SomeTypeIDeclared { ... } ). It is there you can declare the member's type as decimal?

    Thursday, February 28, 2008 9:35 PM
  • This looks like the same problem I reported on Microsoft Connect which has been "escalated" since November, 2007. If it is, please add a validation.

    https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=311216

     

    Thursday, February 28, 2008 9:47 PM
  • Hello Paul,

     

    I suggest you can convert the result into [decimal?] type. For example:

     

                int[] a = new int[] { 1, 2, 3, 4, 5, 6, 7};

                var result = from r in a

                             select new { e1= r as int?};

     

    Hope this helps.

    Regards,

     

    Wen Yuan

    Monday, March 3, 2008 9:45 AM