Postgresql – System.InvalidCastException: Column 'modifiedbyuserid' is null

I had been getting an error

System.InvalidCastException: Column ‘modifiedbyuserid’ is null

when using Entity Framework Core with a Postgresql database, even though the column is defined as nullable int? in the model.

Database: Postgresql version 12.3-1.pgdg100+1

.NET Core project

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.8" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.8" />
    <PackageReference Include="Npgsql" Version="6.0.6" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="6.0.6" />
    <PackageReference Include="Microsoft.IdentityModel.Tokens" Version="6.22.1" />
    <PackageReference Include="System.IdentityModel.Tokens.Jwt" Version="6.22.1" />
    <PackageReference Include="Microsoft.AspNetCore.Http" Version="2.2.2" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc.NewtonsoftJson" Version="6.0.8" />
  </ItemGroup>

</Project>

Database table:

CREATE TABLE Users
(
    UserID BIGSERIAL PRIMARY KEY,
    Username VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    CreateDate TIMESTAMP NOT NULL DEFAULT (NOW() AT TIME ZONE 'UTC'),
    ModifiedDate TIMESTAMP NOT NULL DEFAULT (NOW() AT TIME ZONE 'UTC'),
    "Disabled" BOOL NOT NULL DEFAULT False,
    CreatedByUserID BIGINT,
    ModifiedByUserID BIGINT
);

Model in C#:

public partial class User
{
    public User()
    {
    }

    public long Userid { get; set; }
    public string Username { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public DateTime Createdate { get; set; }
    public DateTime Modifieddate { get; set; }
    public bool Disabled { get; set; }
    public long? Createdbyuserid { get; set; }
    public long? Modifiedbyuserid { get; set; }
}

C# call to get the database row

var user = Context.Users.SingleOrDefault(
                u => u.Username == model.Username
            );

Error:

System.InvalidCastException: Column ‘modifiedbyuserid’ is null

StackTrace

System.InvalidCastException: Column 'modifiedbyuserid' is null.
   at Npgsql.ThrowHelper.ThrowInvalidCastException_NoValue(FieldDescription field)
   at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
   at Npgsql.NpgsqlDataReader.GetInt64(Int32 ordinal)
   at lambda_method750(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at lambda_method751(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
   at BusinessLogic.Repository.Implementations.LoginRepository.Login(LoginRequest model) in BusinessLogic/Repository/Implementations/LoginRepository.cs:line 61
   at API.Controllers.LoginController.Login(LoginRequest model) in API/Controllers/LoginController.cs:line 24
   at lambda_method741(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at API.Middleware.JwtMiddleware.Invoke(HttpContext context) in API/Middleware/JwtMiddleware.cs:line 41
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)