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