Linq clause when datetime might be null

Question

I have a SQL table also copied in a DataTable to improve performance with the following structure:

*id_configuration* integer <br>
*id_customer* integer <br>
*id_location* integer   <br>
*validfrom* datetime     <br>
*validuntil* datetime   **ALLOW NULL** <br>
*deviceid*  integer  <br>
*deviceport* integer     <br>

When a configuration changes, I'm inserting another record with validfrom = start date and valid until = NULL and I'm updating the previous record with valituntil = startdate.addseconds(-1)

I need to find the "id_customer" for a specific date, even if the configuration is active (validuntil = NULL) like:

SELECT id_customer 
FROM MyTable 
WHERE
    id_location = 11 
    AND deviceid = 122 
    AND deviceport = 3 
    AND validfrom > '2014-08-22 10:30:00' 
    AND (validuntil < '2014-08-22 10:30:00' or validuntil is NULL)

I'm trying to query the DataTable with the following LINQ query:

Dim cfg = From u In MyDataTable 
    Where (u("id_location") = 11 And_ 
    u("deviceid") = 122 And_ 
    u("deviceport") = 3 And_ 
    u("validfrom") > '2014-08-22 10:30:00' And_ 
    ((u.Field(Of Nullable(Of DateTime))("validuntil") Is Nothing) Or u("validuntil") < '2014-08-22 10:30:00') 
    Select u("id_customer")

But I'm receiving the following error:

Operator '>' is not defined for type 'DBNull' and type 'Date'.

try `u.Field(Of Nullable(Of DateTime))("validuntil").Value < '2014-08-22 10:30:00'` ? — Tim.Tang, Aug 22 '14 at 09:06

score 1 · Accepted Answer · edited May 23 '17 at 11:43

Use Not nullable.HasValue instead, you should also always use AndAlso and OrElse instead And and Or. It's also more readable and more efficient to use a variable via Let:

Dim cfg = From u In MyDataTable 
          Let validuntil = u.Field(Of DateTime?)("validuntil")
          Where ... AndAlso _
          (Not validuntil.HasValue OrElse validuntil.Value < New Date(2014,08,22))
          Select u.Fiueld(Of Int32)("id_customer")

What is the difference between And and AndAlso in VB.NET?

Linq clause when datetime might be null

1 Answers1