Entity Framework - MySQL - Null Comparison!

Question

Whats wrong with following code? :(

int? parentFolderId = null;
if( this.Request.QueryString ["folderId"] != null )
    parentFolderId = Convert.ToInt32( this.Request.QueryString ["folderId"] );
f = ( from x in this.EntityDataContext.folders
        where x.parent_id == parentFolderId
        select x ).ToList();

It returns nothing! Though there ARE records in database with parent_id as NULL.

However, when I explicitly state NULL; it works!

f = ( from x in this.EntityDataContext.folders
        where x.parent_id == null
        select x ).ToList();

What could be the issue?

PS: I hate working with mysql using Entity Framework .... every damn simple thing has million issues!

not even this is working: where x.parent_id == ( parentFolderId == null ? null : parentFolderId ) — effkay, Dec 25 '09 at 07:22

score 3 · Answer 1 · answered Dec 25 '09 at 11:08

3

Long Shot

f = ( from x in this.EntityDataContext.folders
                where ((parentFolderId!=null && x.parent_id == parentFolderId)
                        ||(parentFolderId==null && x.parent_id == null))
                select x ).ToList();

Yeah, this seams wired, and I guess your first example should work just fine with MsSql. Maybe it's time to file a bug to authors of Linq to MySql ?

answered Dec 25 '09 at 11:08

Alexander Taran

6,655
2
39
60

@effkay @Alexander: This is a problem with [Entity Framework itself](http://stackoverflow.com/questions/682429/how-can-i-query-for-null-values-in-entity-framework). – BlueRaja - Danny Pflughoeft Sep 30 '10 at 17:34

Ali Ersöz · Answer 2 · 2009-12-25T07:48:59.107

I had this kind of problem in sql server and in sql server the generated query looks like "parent_id = null" when you are working on a nullable field. And that query returns nothing even parent_id is null.

The tricky way in here is, you should force EF to create a query like "parent_id is null" and the code I tried in linq was;

if(parentFolderId.HasValue)
{
    f = ( from x in this.EntityDataContext.folders
                where x.parent_id == parentFolderId
                select x ).ToList();
}
else
{
    f = ( from x in this.EntityDataContext.folders
                where !x.parent_id.HasValue
                select x ).ToList();
}

I know this does not seem a perfect way to do this but, this is how I could get rid of that issue.

This seems to be the best solution - EF seems to be doing its own "magic" in the background with LINQ. We had huge problems with EF on one of our projects and have since ditched it for nHibernate. — Rich, Dec 29 '09 at 17:02

score 0 · Answer 3 · answered Dec 25 '09 at 07:37

0

You'll probably find there's an issue involving using DBNull or something similar. I would think that in the second case (where you explicitly state "null") that Linq is automatically transforming it to DBNull in the background.

perhaps try something along the lines of:

where x.parent_id == ( parentFolderId == null ? DBNull.Value : parentFolderId )

Hope that puts you on the right track!

answered Dec 25 '09 at 07:37

Rich

398
2
7

already tried this: Cannot convert lambda expression to type 'string' because it is not a delegate type. Neither this is working :( . where x.active == 1 && x.parent_id == ( parentFolderId == null ? null : parentFolderId ). I HATE to add an if/else statement :( – effkay Dec 25 '09 at 08:04

score 0 · Accepted Answer · answered Feb 19 '10 at 13:26

0

This is connector bug and I have reported at mysql.

answered Feb 19 '10 at 13:26

effkay

794
2
17
36

It was an EF bug and it's going to be fixed in .net 4.5 http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/suggestions/1015361-incorrect-handling-of-null-variables-in-where-cl?ref=title#suggestion-1015361 – D.Rosado Jun 13 '12 at 11:10

Entity Framework - MySQL - Null Comparison!

4 Answers4