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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

Hassan Gulzar

Related Questions

Linq to Entities vs. Linq to SQL vs. Entity Framework

I've seen several articles on using a database in a WPF.Net 4 application. I believe the primary two technologies are:...SQL with Linq (L2S)...Enterprise Framework (EF)...I sometimes see mention of the Linq to Entities (L2E) technology, but I can never figure out what makes L2E different from EF....So, my question is: Are there any differences betw...
ado.net entity-framework linq-to-entities linq-to-sql
asked by Mike B.

LINQ and Entity Framework are used to check for null values in C# variables.

In VB...Dim entry = myDB.Entries.Where(Function(e) e.Email = entry.Email).SingleOrDefault()...If entry IsNot Nothing Then...' we have a value...else...' we dont have a value...End If
c# entity-framework lambda linq var
asked by William Contestabile

The LINQ expression node type 'Invoke' is not supported in the entity framework's LINQ to Entities.

Can somebody assist me in resolving my problem? I'm use the code below:...public IEnumerable<InvoiceHeader> Getdata(Expression<Func<InvoiceHeader, bool>> predicate) { return AccountsContext.InvoiceHeaders.Include("Company").Include("Currency") .Include("BusinessPartnerRoleList").Include("DocumentType") .Where(predicate); } .....
c# entity-framework expression linq predicate
asked by zaheer abbas

In Entity Framework, check for null values in Linq queries.

If you are evaluating against a...null... value, you must first contrast it with...null... caused by a bug....var field = from field in table where (value == null ? field.property == null : field.property == value) select field;
asp.net entity-framework linq null
asked by Devin Burke

LINQ-To-Entity (LINQ to Entities does not support the given type member ".)

I'm having trouble comprehending this issue:...Here is the T-SQL query I must use LINQ to Entities to implement....select r.ReviewID, b.BusinessID, b.Name as BusinessName ,r.Description from gb_business.Review r inner join gb_business.Business b on r.BusinessID = b.BusinessID inner join gb_listing.RegionalService rs on b.BusinessID = rs.BusinessID ...
c# entity-framework lambda linq-to-entities
asked by itisinteresting

Is Null, Is Not Null in Entity Framework Linq

As far as I can determine, the issue seems to be that the query's condition was not properly written. Let's look at an example of what will be appended:...The Data...Id = 1, Param1 = null, Param2 = null Id = 2, Param1 = 'a' param2 = null Id = 3, Param1 = null, Param2 = 'b' Id = 4, Param1 = 'a' param2 = 'c' ...You will only get the Id 1 using th...
c# entity-framework isnull linq optional-parameters
asked by Stephan

Entity Framework - Handle null value in Linq

The issue is that EF cannot convert the ...operator for null-coalescing... to SQL. Personally, I don't see anything wrong with using an if statement to verify the outcome before running the next query. But if you don't want to do that, why not try something like this instead as your result will always be a single query:...var wsdetails = (from asse...
c# entity-framework linq linq-to-sql
asked by James

Dynamic LINQ DateTime Comparison String Building - Linq To Entities

Using the data ...here... and a slightly modified string structure, I was able to get it running....This method worked well for me:...ContactList.Where("DateAdded >= DateTime(2013, 06, 18)") ...Note that technique is completely ineffective with...DateTimeOffset... columns.
c# dynamic-linq entity-framework linq string
asked by Richard Rout

Cannot implicitly convert type System.Linq.IQueryable<string> to string

I have two tables called CrewMembers and Rank. On the basis of the CrewMember id, I want to retrieve the name of the rank that is listed in the Rank table. I provided the crewId as an input, and the method would use that information to produce the rank of that particular crew member. Here's how my code looks:... public string GetRank(int Crew...
c# entity-framework iqueryable linq
asked by prers

Why EF6 LINQ does not generate proper "is null" SQL for null string variable comparison?

ZZZ_tmp
c# entity-framework-6 linq-to-entities
asked by Zoltán Tamási

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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