LINQ to Entities and null strings

Question

I've got quite a strange thing happening on an ASP.NET 4.0 web application using EF 4.0 as its database backend. Essentially, I've got a table that stores users' password reset requests (containing a reset key of type byte[], an expiry of type DateTime, and a foreign key to a User containing a string Email and string Name). Some users do not have an email address set, so for a PasswordRequest request, request.Email is null.

Here's the problem. This works perfectly fine:

string u = Request["u"];
string e = Request["e"];

var requests = from r in context.PasswordRequests
               where r.User.Name == u && r.User.Email == null && r.Expiry >= DateTime.Now
               select r;

I get the expected number of results (nonzero, since there are entries with null emails).

But this always returns an empty collection when e is null:

string u = Request["u"];
string e = Request["e"];

var requests = from r in context.PasswordRequests
               where r.User.Name == u && r.User.Email == e && r.Expiry >= DateTime.Now
               select r;

The only thing that I got to work properly (which doesn't logically make any sense) is this:

string u = Request["u"];
string e = Request["e"];

IQueryable<PasswordRequest> requests;

if (e == null)
    requests = from r in context.PasswordRequests
               where r.User.Name == u && r.User.Email == null && r.Expiry >= DateTime.Now
               select r;
else
    requests = from r in context.PasswordRequests
               where r.User.Name == u && r.User.Email == e && r.Expiry >= DateTime.Now
               select r;

I'm absolutely stumped. Any ideas?

The Email field in your actual table, is it declared as a NULL column? — DoomerDGR8, Nov 11 '11 at 07:35

score 37 · Accepted Answer · answered Nov 11 '11 at 07:42

37

Basically this is a mismatch between SQL and C# when it comes to the handling of nulls. You don't need to use two queries, but you need:

where r.User.Name == u && (r.User.Email == e ||
                           (e == null && r.User.Email == null))

It's annoying, and there may be a helper function to make life easier, but it fundamentally comes from SQL's null handling where

where X = Y

will not match if both X and Y are null. (Whereas in C# the equivalent expression would be true.)

You may need to do the same for u as well, unless that is non-nullable in the database.

One small trick you could at least try if you're happy with null and empty strings being handled the same way is:

// Before the query
e = e ?? "";

// In the query
where r.User.Name == u && (r.User.Email ?? "") == e

I believe that will perform null coalescing on both the email column and e, so you never end up comparing null with anything.

answered Nov 11 '11 at 07:42

Jon Skeet

1,421,763
867
9,128
9,194

1

Aha! I though it might come to that, but I wasn't sure exactly what caused that. Thanks for clarifying! – hydroiodic Nov 11 '11 at 07:44
Nicely done! The where condition is a bit loose. So I guess putting in all three possible values for `e` make sense. – DoomerDGR8 Nov 11 '11 at 07:44
I had a problem where my database contained a INT column that allowed nulls, so some of the other solutions wouldn't work for me (most solutions deal with strings) - your last bit of code saved my life! thanks! Here's what i ended up using: (w.AccountID ?? _AccountID) == _AccountID – Losbear Nov 16 '12 at 17:07
This should be considered a bug in Linq to Entities (or Linq to sql - it happens there too). Is this open on Connect? I couldn't find anything. – sinelaw Jun 03 '13 at 16:19
3

Ok, found it, and it seems to be fixed in some newer versions of EF: http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/suggestions/1015361-incorrect-handling-of-null-variables-in-where-cl?ref=title%23suggestion-1015361 – sinelaw Jun 03 '13 at 16:24

score 3 · Answer 2 · edited May 23 '17 at 11:47

3

I have found a couple of articles detailing the same issue. Unfortunately, I haven't faced this issue so far. It is very interesting though.

Here:

LINQ syntax where string value is not null or empty

LINQ to SQL and Null strings, how do I use Contains?

And from MSDN: http://msdn.microsoft.com/en-us/library/bb882535.aspx

edited May 23 '17 at 11:47

Community

1
1

answered Nov 11 '11 at 07:43

DoomerDGR8

4,840
6
43
91

Useful links. Thanks for posting! – hydroiodic Nov 11 '11 at 07:45

score 1 · Answer 3 · answered May 04 '16 at 18:23

If you prefer using method (lambda) syntax as I do, you could do it like this:

var result = new TableName();

using(var db = new EFObjectContext)
{
    var query = db.TableName;

    query = value1 == null 
        ? query.Where(tbl => tbl.entry1 == null) 
        : query.Where(tbl => tbl.entry1 == value1);

    query = value2 == null 
        ? query.Where(tbl => tbl.entry2 == null) 
        : query.Where(tbl => tbl.entry2 == value2);

    result = query
        .Select(tbl => tbl)
        .FirstOrDefault();

   // Inspect the value of the trace variable below to see the sql generated by EF
   var trace = ((ObjectQuery<REF_EQUIPMENT>) query).ToTraceString();

}

return result;

score -1 · Answer 4 · answered Nov 11 '11 at 07:53

If you want to retrieve items from the DB when request['e'] == null

it should have been

var requests = from r in context.PasswordRequests
               where r.User.Name == u && r.User.Email is null && r.Expiry >= DateTime.Now
               select r;

note that == null and is null is different . see --> MSDN Info

Therefore, your last example is sort of valid since you need 2 ways to get data from the DB. i.e. one if email is null and one if email == Request['e']

LINQ to Entities and null strings

4 Answers4