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

1 answer

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);




All Articles