Why is this query throwing an exception?

Question

I have a User table that has a BIT NULL column called NxEnabled, and I can't change. Some of the records actually contain a NULL in that column.

I don't want to have a Nullable in C#, so it makes sense comparing it to true in the projection (the code is simplified, but this generates the same error):

context.Users.Where(x => x.Id == 4)
             .Select(x => new 
                  { 
                      Id = x.Id,
                      Name = x.Name,
                      Enabled = x.NxEnabled == true
                  });

Why does this query throw an

InvalidOperationException: The null value cannot be assigned to a member with type System.Boolean which is a non-nullable value type.`

when enumerated?

edit: Sorry for the typo. The query was actually more complex but the where clause is not the problem.

Answer 1

When you compare true to your .IsEnabled field, LINQ to SQL passes the true as a parameter in the query. The problem with that is that the translation will return NULL:

SELECT [t0].[Id], [t0].[Name],
    (CASE 
        WHEN [t0].[NxEnabled] = @p0 THEN 1
        WHEN NOT ([t0].[NxEnabled] = @p0) THEN 0
        ELSE NULL
     END) AS [Enabled]
FROM [Users] AS [t0]

Because of how SQL92 defines how comparing to NULL works, both WHEN conditions are false. It is explained in this this blog post [Disclaimer: the post is mine]

Use Enabled = NxEnabled ?? false instead as a work-around.

Answer 2

Typo -- you need the double-equals ==:

.Where(x => x.Id == 4)