vb.net iif condition in linq query where clause

Question

Whats wrong with the below query, I am getting this error: Nullable object must have a value.

  Dim subscriptionUsers = From inv In dataContext.Invoices.ToList Join u In dataContext.Users _
                                    On inv.Subscription Equals u.Subscription _
                                    Where inv.Id.Value = invoiceID _
                                    And Not u.Fund.Title.Contains("AGM") _
                                    And DirectCast(IIf(Not u.EndDate.HasValue, IIf(u.StartDate.Value <= inv.EndDate.Value, True, False), _
                                                    IIf((u.StartDate.Value >= inv.StartDate.Value And u.StartDate.Value <= inv.EndDate.Value) Or _
                                                (u.EndDate.Value >= inv.StartDate.Value And u.EndDate.Value <= inv.EndDate.Value) Or _
                                                (u.StartDate.Value < inv.StartDate.Value And u.EndDate.Value > inv.EndDate.Value), True, False)), Boolean) _
                                Group By Key = u.Fund.Title Into Group _
                                Select Fund = Key, UsersCount = Group.Count, Users = Group.ToList, _
                                SubFunds = (From a In dataContext.Allocations Where a.Fund.Title = Key Select a.Department.Title Distinct)

If I remove the u.EndDate.Value in the condition then it works fine.

Here is the stack trace:

at System.Nullable1.get_Value() at SDBReports.InvoiceAllocationReportUserControl._Lambda$__4(VB$AnonymousType_02 $VB$It) at System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext() at System.Linq.Lookup2.Create[TSource](IEnumerable1 source, Func2 keySelector, Func2 elementSelector, IEqualityComparer1 comparer) at System.Linq.GroupedEnumerable4.GetEnumerator() at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()

p.s. I have used SPMetal to generate the entity classes on SharePoint lists.

For more clarity:

do not get confused with u.EndDate.Value and inv.EndDate.Value

here is the true part for u.EndDate.Hasvalue:

 IIf((u.StartDate.Value >= inv.StartDate.Value And u.StartDate.Value <= inv.EndDate.Value) Or _
                                                (u.EndDate.Value >= inv.StartDate.Value And u.EndDate.Value <= inv.EndDate.Value) Or _
                                                (u.StartDate.Value < inv.StartDate.Value And u.EndDate.Value > inv.EndDate.Value), True, False)

and here is the false part

IIf(u.StartDate.Value <= inv.EndDate.Value, True, False)

I would assume those dates are nullable, and that one of them *is* null. Accessing `Value` on a null nullable is a no-no. — dlev, Aug 25 '11 at 16:36
I am checking if the date field has any value right? u.EndDate.HasValue — Ken, Aug 25 '11 at 16:37
The `IIf` function does not short-circuit. [Use `If` instead.](http://stackoverflow.com/questions/2409893/does-the-vb-net-if-operator-cause-boxing) — Dan Tao, Aug 25 '11 at 16:37
Why are you using `IIf(u.StartDate.Value <= inv.EndDate.Value, True, False)` rather than just the expression `u.StartDate.Value <= inv.EndDate.Value`? — Jon Skeet, Aug 25 '11 at 16:37
@Jon Skeet I was tell him the same for an improvement, but that wouldn't still fix the error. — PedroC88, Aug 25 '11 at 16:40
@PedroC88: Indeed - but simplifying the code may help to make it easier to see the wood for the trees :) — Jon Skeet, Aug 25 '11 at 16:50

Konrad Rudolph · Accepted Answer · 2011-08-25T16:45:23.797

Use If instead of IIf. This is short-circuited (like C#’s conditional operator) and will work because the conditions are only evaluated if HasValue is True. IIf is deprecated. Never use it.
Furthermore, an expression like If(condition, True, False) is nonsensical. Replace it with just condition.
Finally, you need to use AndAlso instead of And – once again, for short-circuiting to happen. In fact, always use AndAlso and OrElse in conditionals. Use And and Or only when doing bit operations.
The DirectCast is also unnecessary.

This leaves us with a much simplified expression:

If(Not u.EndDate.HasValue, u.StartDate.Value <= inv.EndDate.Value), _
    (u.StartDate.Value >= inv.StartDate.Value AndAlso u.StartDate.Value <= inv.EndDate.Value) OrElse _
    (u.EndDate.Value >= inv.StartDate.Value AndAlso u.EndDate.Value <= inv.EndDate.Value) OrElse _
    (u.StartDate.Value < inv.StartDate.Value AndAlso u.EndDate.Value > inv.EndDate.Value))

But this expression is still much too complex. You should split this up but first assigning the values inside the nullables to some temporary variable using Let inside the query.

I believe the OP still has it wrong, though. He's checking `Not u.EndDate.HasValue`, and then using `u.EndDate.Value` in the `TruePart` argument. That's guaranteed not to work, right? Unless I'm missing something. — Dan Tao, Aug 25 '11 at 16:42
@Dan I can’t be sure of course – but the OP is using a *different* `EndDate`, namely of `inv` instead of `u`. — Konrad Rudolph, Aug 25 '11 at 16:44
@Dan Let me think. Uhm, maybe because the expression is ridiculously complex and completely confusing? — Konrad Rudolph, Aug 25 '11 at 16:51

score 1 · Answer 2 · answered Aug 25 '11 at 16:44

1

IIF evaluates both results, even though it only returns one. So if one result would cause an exception (like accessing .Value of a nullable when there is none) you will get an error even if you check for .HasValue at the beginning of the IIF

answered Aug 25 '11 at 16:44

King

339
1
5

score 0 · Answer 3 · answered Aug 25 '11 at 17:16

Just to expand on the IIF evaluation both parts.

Try some code like this:

IIF (True, msgbox("True"), msgbox("false"))

You'll get two message boxes show up. One that says "True" and one that says "false". Even though it's clear you should only trigger the True section.

vb.net iif condition in linq query where clause

3 Answers3