CODEWITHSUNDEEP
vb.netlinqsharepoint-2010
Ken
Ken

Reputation: 744

vb.net iif condition in linq query where clause

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)

Upvotes: 2

Views: 8653

Answers (3)

Eric Burdo
Eric Burdo

Reputation: 814

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.

Upvotes: 0

Konrad Rudolph
Konrad Rudolph

Reputation: 545618

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

Upvotes: 5

King
King

Reputation: 339

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

Upvotes: 1

Related Questions