Nullable Guid in Linq to Sql gives unexpected result
I have a simple SQL table that defines a set of hierarchical categories and subcategories - note that ParentCategoryId can be null for top level categories ...
CREATE TABLE [dbo].[Category](
[CategoryId] [uniqueidentifier] NOT NULL,
[ParentCategoryId] [uniqueidentifier] NULL,
[Name] [nvarchar](50) NOT NULL
)
If I then build a Linq expression to find a specific category by name and ParentCategoryId, I find that I cannot get the correct result if I set the Guid? variable to null:
Guid? parentCategoryId = null;
var category = dc.Categories
.Where(c => (
(c.Name == "Fred") &&
(c.ParentCategoryId == parentCategoryId)
));
This does not give the same result as:
var category = dc.Categories
.Where(c => (
(c.Name == "Fred") &&
(c.ParentCategoryId == null)
));
From what I can find online, others have had this problem, but I haven't been able to find a workaround to fix the problem.
Any ideas would be much appreciated. Thank.
More Information Here are LINQ generated SQL statements for Firstly Guid? null and then for a simple null parameter:
-- With Guid? null parameter : return an empty record set
DECLARE @p0 NVarChar(1000) SET @p0 = 'Fred'
DECLARE @p1 UniqueIdentifier SET @p1 = null
SELECT [t0].[CategoryId], [t0].[ParentCategoryId], [t0].[Name], [t0].[Timestamp]
FROM [dbo].[cad_ScoCategory] AS [t0]
WHERE ([t0].[Name] = @p0) AND ([t0].[ParentCategoryId] = @p1)
-- With null parameter - returns a single (correct) record
DECLARE @p0 NVarChar(1000) SET @p0 = 'Fred'
SELECT [t0].[CategoryId], [t0].[ParentCategoryId], [t0].[Name], [t0].[Timestamp]
FROM [dbo].[cad_ScoCategory] AS [t0]
WHERE ([t0].[Name] = @p0) AND ([t0].[ParentCategoryId] IS NULL)
As you can see, the first option is comparing the ParentCategoryId to a null parameter, where when the second method checks the ParentCategoryId IS NULL, this is correct
source to share
No, unfortunately, this is a fairly common problem. A workaround is to explicitly compare to null:
.Where(c => c.Name == Fred &&
((c.ParentCategoryId == parentCategoryId) ||
(c.ParentCategoryId == null && parentCategoryId == null)))
Alternatively, do validation outside of the query (to change which filter is used):
var category = dc.Categories.Where(c => c.Name == "Fred");
category = parentCategoryId == null
? category.Where(c => c.ParentCategoryId == null)
: category.Where(c => c.ParentCategoryId == categoryId);
source to share