Null strings and LINQ to Entities

asp.net c# entity-framework linq-to-entities sql-server

Question

On an ASP.NET 4.0 web application that uses EF 4.0 as its database backend, I've seen some odd behavior. Basically, I have a database that keeps track of users' requests for password resets (it has a reset key of typebyte[] a type expirationDateTime a foreign key to a andUser consisting ofstring Email and string Name Some individuals don't have an established email address, therefore for aPasswordRequest request , request.Email is null .

Here is the issue. This is flawlessly functional:

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 anticipated number of outcomes (which is non-zero since there are entries withnull emails).

However, this consistently returns a blank collection whene 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 could get to function correctly (and which logically makes no 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 completely at a loss. Any thoughts?

1
15
11/11/2011 7:26:57 AM

Accepted Answer

In essence, this is a handling of nulls incompatibility between SQL and C#. Although you don't have to use two queries, you do need to:

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

Although there may be a helper function to make things simpler, it mostly stems from SQL's null handling where

where X = Y

will match if both X and Y are nulls, not. The equivalent phrase in C# would be true (instead).

You may have to follow suit foru unless the database does not allow for non-nullability, as well.

If you're okay with null and empty strings being treated equally, here's a quick hack you could at least try:

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

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

That, in my opinion, will carry out null coalescing on the email column ande , thus you never compare null with anything in the end.

35
11/11/2011 7:42:28 AM

Popular Answer

I have saw a few articles explaining the same problem. Unfortunately, I haven't had to deal with this problem yet. But it's very fascinating.

Here:

LINQ syntax for string values that are neither empty or null

How do I use Contains in LINQ to SQL with Null strings?

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow