views:

335

answers:

4

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!

A: 

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!

Rich
effkay
+1  A: 

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.

yapiskan
yea; this is the ONLY ugly way left :( ....
effkay
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
+2  A: 

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 ?

Alexander Taran
yup. I have already filled a bug: http://bugs.mysql.com/bug.php?id=49936
effkay
@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
A: 

This is connector bug and I have reported at mysql.

effkay