LINQ to Entities and null strings
Solution 1
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.
Solution 2
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
Solution 3
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;
hydroiodic
Updated on June 04, 2022Comments
-
hydroiodic almost 2 years
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 typeDateTime
, and a foreign key to aUser
containing astring Email
andstring Name
). Some users do not have an email address set, so for aPasswordRequest request
,request.Email
isnull
.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
isnull
: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?
-
Hassan Gulzar over 12 yearsThe Email field in your actual table, is it declared as a NULL column?
-
Marius over 12 yearsCan you post the generated sql?
-
-
hydroiodic over 12 yearsAha! I though it might come to that, but I wasn't sure exactly what caused that. Thanks for clarifying!
-
Hassan Gulzar over 12 yearsNicely done! The where condition is a bit loose. So I guess putting in all three possible values for
e
make sense. -
hydroiodic over 12 yearsUseful links. Thanks for posting!
-
Losbear over 11 yearsI 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
-
sinelaw almost 11 yearsThis 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 almost 11 yearsOk, found it, and it seems to be fixed in some newer versions of EF: data.uservoice.com/forums/…