LINQ to Entities and null strings

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 querye = e ?? "";// In the querywhere 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

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;