'Nullable object must have a value' with bool? value == true

[NArnott]

When I do a select against a nullable bool, the translated SQL doesn't match.

Steps to reproduce

Consider the following query. Note that the dto's IsPendingConfirmation is of type bool, while siteAccount.IsLocked is of type bool?.

var query = from siteAccount in _db.CustomerSite
            orderby siteAccount.CustomerSiteId descending
            where siteAccount.EmailAddress.Contains("something.com")
            select new CustomerSiteAccountSummaryDto()
            {
                CustomerSiteId = siteAccount.CustomerSiteId,
                Email = siteAccount.EmailAddress,
                IsPendingConfirmation = siteAccount.IsLocked == true,
            };

which generates the following sql

SELECT TOP(@__p_0) [siteAccount].[bigCustomerSiteId] AS [CustomerSiteId], [siteAccount].[strEmailAddress] AS [Email], [siteAccount].[bitLocked] AS [IsPendingConfirmation]
      FROM [tblCustomerSite] AS [siteAccount]
      WHERE CHARINDEX(N'something.com', [siteAccount].[strEmailAddress]) > 0
      ORDER BY [CustomerSiteId] DESC

This sql is wrong because it can return null responses for IsPendingConfirmation even though 'siteAccount.IsLocked == true' should never be able to return null. When null is returned, it then results in the "nullable object' exception.

Note that changing to 'IsPendingConfirmation = siteAccount.IsLocked ?? false,' does produce the correct sql: CAST(COALESCE([siteAccount].[bitLocked], 0) AS bit) AS [IsPendingConfirmation]. I've used 'bool? == true' constructs all over the place, and this works fine in EF.

Exception message: System.InvalidOperationException: Nullable object must have a value.
Stack trace: 
   at lambda_method(Closure , QueryContext , TransparentIdentifier`2 )
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
   at System.Collections.Generic.AsyncEnumerableHelpers.ToArrayWithLength[T](IAsyncEnumerable`1 source, CancellationToken cancellationToken)
   at System.Collections.Generic.AsyncEnumerableHelpers.ToArray[T](IAsyncEnumerable`1 source, CancellationToken cancellationToken)
   at MyController.GetValueByEmail(String emailAddress)
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Further technical details

EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.9.2

[smitpatel]

Possibly related #13908

[alpergunay]

Do you have any solution or workaround for this problem ?

[NArnott]

@alpergunay As I stated above, changing the syntax a bit does produce the correct result, but it is less intuitive in my opinion.

Instead of
IsPendingConfirmation = siteAccount.IsLocked == true
I had to do:
IsPendingConfirmation = siteAccount.IsLocked ?? false

[iamdroppy]

I'm having the same issue, but with a non-nullable value.

var firstSelect = query.Select(s=>new {Quantity = ..., Requested = ...});
var secondSelect = query.Select(s=>new{Total = s.Quantity - s.Requested;})

Quantity and Requested are non nullable int, and the same exception happens. If I remove s.Requested it works.

I can't send the entire code now, which is company's, but later today I'll try to make a reproducible example.

By the way, I'm using Castle Proxies, which is also one of my suspects.

[smitpatel]

@iamdroppy - Which version of EF Core are you using?

[iamdroppy]

@smitpatel I'm on .NET Core 2.2.

I'm confident this may be an issue with Castle Proxy.

I've got a table which is like this (of course, just this column):

Product Name | Count
-----------------------
Abc          | 1
Test         | 10
Test2        | 15

LINQ on Where(s=>true); (using true as it's easier to modify with a breakpoint.

Returns: entire table (correct)

Now, I've done two more tests:

Where(s=>s.Count > 5); returned "Test" and "Test2", which is correct,

and then I tried Where(s=>s.Count < 5) and returned nothing which is definitely awkward.

This is regarding the issue that I've commented above. I have refactored to use one Select and it worked, but it stills make me wonder why.

P.S: this exception happens on non-nullable data, and in this post I'm just mentioning something awkward that also happened with the same query, because if I setted the breakpoint it would show the data, so I'd guess something with Lazy Loading proxies. But why int == int is giving this exception is a real mystery to me.

P.S.S: I appologise for not have sent a working snippet, as I still have to reproduce in a non-company code, and I haven't got time this week yet.

csproj packages:

    <PackageReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.AspNetCore.Razor.Design" Version="2.2.0" PrivateAssets="All" />
    <PackageReference Include="Microsoft.AspNetCore.SignalR" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.2.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Abstractions" Version="2.2.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Analyzers" Version="2.2.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Proxies" Version="2.2.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.2.4" />
    <PackageReference Include="Ninject" Version="3.3.4" />

Maybe it's better if I open another issue.

[smitpatel]

@iamdroppy - Please file a new issue with repro code so we can investigate.