| Bug #49936 | Comparing NULLABLE type for NULL value in database | ||
|---|---|---|---|
| Submitted: | 25 Dec 2009 7:49 | Modified: | 26 May 2011 17:21 |
| Reporter: | fahad khalil | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / NET | Severity: | S1 (Critical) |
| Version: | 6.2.1 | OS: | Windows |
| Assigned to: | Julio Casal | CPU Architecture: | Any |
| Tags: | entity framework, linq | ||
[25 Dec 2009 7:59]
fahad khalil
Sorry, in the table, parent_id is not a FK with folders.
[25 Dec 2009 21:03]
MySQL Verification Team
Thank you for the bug report. Could you please verify if you are applying the rules to handle NULL values correctly as mentioned in the Manual: http://dev.mysql.com/doc/refman/5.1/en/problems-with-null.html Thanks in advance.
[26 Dec 2009 12:15]
fahad khalil
Yes, I am handling correctly. When I run following query in database, it is returning results. SELECT * FROM folders WHERE parent_id IS NULL However, following query does not return any results. SELECT * FROM folders WHERE parent_id = '' The issue is with Passing the NULL object or using keyword NULL, as you might see from my original post. Thanks for attending the issue & Merry Christmas.
[3 Feb 2010 7:35]
Tonci Grgin
Fahad, I'm discussing your report with Wlad, it is not forgotten.
[12 Feb 2010 12:55]
Tonci Grgin
Fahad, can you please attach a test case for your problem? Do add and DDL/DML statements if necessary.
[13 Mar 2010 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[21 Aug 2010 18:17]
Alexander Sidorov
Hi guys!
Actual developer proviced version doesn't correctly compare to null values. Here is a workaround I have to use:
if (parentCategoryId.HasValue)
return ctx.Categories.Where(c => c.ParentCategoryId == parentCategoryId).ToList();
else
return ctx.Categories.Where(c => !c.ParentCategoryId.HasValue).ToList();
[26 May 2011 17:21]
Julio Casal
This is not a Connector/Net bug, but a misunderstanding in the NULL semantics used in LINQ-to-Entities queries. For the provided scenario, the sql query will be this: SELECT `Extent1`.`Id`, `Extent1`.`ParentId`, `Extent1`.`Title` FROM `folders` AS `Extent1` WHERE `Extent1`.`ParentId` = NULL Which will not return any results, because in the server NULL is not equal to NULL, as NULL means "a missing unknown value" as described in these pages: http://dev.mysql.com/doc/refman/5.5/en/working-with-null.html http://dev.mysql.com/doc/refman/5.5/en/problems-with-null.html To produce your expected SQL (WHERE `Extent1`.`ParentId` IS NULL) you can try this alternate approach: List<folders> f = (from x in this.EntityDataContext.folders where parentFolderId == null ? x.parent_id == null : x.parent_id == parentFolderId select x).ToList(); The issue is currently reported in the Microsoft Connect site: http://connect.microsoft.com/data/feedback/details/607404/entity-framework-and-linq-to-sql... And is also open for discussion and voting in the User Voice site: http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/sug...
Description: When comparing nullable type with assigned null value - in a LINQ query; it does not yield any results. However, when you explicitly specify NULL, it returns values. This is really painful as developers will have to check for nulls using if/else. How to repeat: 1 - Table Schema: <folders> Id - int Title - varchar parent_id - int - FK with <folders>.id 2 - Add following records: 1 - Test - NULL 2 - Test - NULL 3 - Generate Entity Model in Visual Studio 2008. 4 - Query following: int? parentFolderId = null; List<folders> f = ( from x in this.EntityDataContext.folders where x.parent_id == parentFolderId select x ).ToList(); It will NOT return any results. Suggested fix: Try this: List<folders> f = ( from x in this.EntityDataContext.folders where x.parent_id == null select x ).ToList();