Nullables in lambda expressions in LINQ to Entities – today’s gotcha

It took me a while to figure out what caused this problem, so I thought I’d share it here.

I was trying to write a query in VB to grab customers who have orders placed after July 1, 2007.

The query looks like this:

Dim q = From cust In nwentities.Customers _
  Where (cust.Orders.Any(Function(o) _
     o.OrderDate > New DateTime(2007, 7, 1)))

But it would not compile. I have Option Strict On and the error was

Option Strict On disallows implicit conversions from ‘Boolean?’ and ‘Boolean’.

What the heck was this “Boolean?”. Not google-able, that’s for sure!

Then I noticed that OrderDate was being defined as a “Date?”.

What I’m seeing is the shortcut for Nullables in VB. It’s really hard to google for that. But if you look it’s everywhere! Such as in this post by Bill McCarthy. (See Bill? I found it all by myself! :-))

OrderDate is nullable. “Date?” means nullable date.

And the VB’s compiler is casting the entire expression to a nullable Boolean then telling me “Yo! A Nullable boolean is NOT the same as a boolean! Sorry”.

Note that the designer showed Nullable as false but the actual database has Nullable=true.

So, while I can write a regular query with this model, such as:

  Dim ords = From ord In nwentities.Orders Where ord.OrderDate > New DateTime(2007, 7, 1)

and I can write my exact query in C# with no worries:

   var q=from cust in nw.Customers where cust.Orders.Any(o=>o.OrderDate>new DateTime(2007,7,1)) select cust;

I believe that in my scenario, I just need to rethink my query. But later…

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

2 thoughts on “Nullables in lambda expressions in LINQ to Entities – today’s gotcha

  1. Hello,

    Solution as follows:

    Dim q = From cust In nwentities.Customers _

    Where (cust.Orders.Any(Function(o) _

    if(o.OrderDate,#1500/1/1#) > New DateTime(2007, 7, 1)))

    –OR

    Dim q = From cust In nwentities.Customers _

    Where (cust.Orders.Any(Function(o) _

    if(o.OrderDate.HasValue,o.OrderDate.Value,#1500/1/1#) > New DateTime(2007, 7, 1)))

    the translated SQL will use the COALESCE() if to SQL Server.

    rgds

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.