Database Insert Error: "Cannot insert the value NULL into column" even if the value is not NULL

[hankl]

There is a database insert error occurred sometimes "Cannot insert the value NULL into column" even if the value is not null. The column is not primary key. The error will gone after application restarted.

INSERT INTO [CustomClaims] ([Id], [Name], [UserId], [Value]) VALUES (@p2, @p3, @p4, @p5), (@p6, @p7, @p8, @p9), (@p10, @p11, @p12, @p13), (@p14, @p15, @p16, @p17), (@p18, @p19, @p20, @p21), (@p22, @p23, @p24, @p25), (@p26, @p27, @p28, @p29), (@p30, @p31, @p32, @p33), (@p34, @p35, @p36, @p37), (@p38, @p39, @p40, @p41);" 2019-01-21 03:08:22.875 +00:00 [Debug] [Microsoft.EntityFrameworkCore.Database.Command] A data reader was disposed. 2019-01-21 03:08:22.961 +00:00 [Debug] [Microsoft.EntityFrameworkCore.Database.Transaction] Disposing transaction. 2019-01-21 03:08:22.963 +00:00 [Error] [Microsoft.EntityFrameworkCore.Update] An exception occurred in the database while saving changes for context type '"*.AuthServer.Core.Contexts.AuthServerDbContext"'." ""Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Value', table '.dbo.CustomClaims'; column does not allow nulls. INSERT fails. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryHasMoreResults(Boolean& moreResults) at System.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more) at System.Data.SqlClient.SqlDataReader.NextResult() at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader) --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(Tuple2 parameters) at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable1 commandBatches, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList1 entriesToSave) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess) at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)" Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Value', table '**.dbo.CustomClaims'; column does not allow nulls. INSERT fails. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryHasMoreResults(Boolean& moreResults) at System.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more) at System.Data.SqlClient.SqlDataReader.NextResult() at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader) --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(Tuple2 parameters) at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable1 commandBatches, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList1 entriesToSave) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess) at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)

Steps to reproduce

It is hard to reproduce. It happens occasionally. It will disappear after restart and will come back again sometime later.

Further technical details

EF Core version: (2.0.1) Database Provider: (Microsoft.EntityFrameworkCore.SqlServer) Operating system: Azure China web app OR Win10 IDE: (Visual Studio 2017 )

[hankl]

cc @DamianEdwards @davidfowl @ajcvickers

[ajcvickers]

@hankl We will likely require a small, runnable project/solution or complete code listing in order to be able to debug this. However, can you clarify how you know that the value being inserted is not null?

[hankl]

I debug it locally and saw the value is not null but throw the exception when inserting to DB.

[ajcvickers]

@hankl Please post a smmal, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[hankl]

It is really difficulty to reproduce the error when debugging locally. I upgraded EF core version to latest and redeploy our application to production. It seems the error not occurs any more.

[atiba-tlewis]

I just had this problem. This may or may not be relevant to the original issue, but it produces the same symptom.

I had a non-nullable Description property for which I wanted to provide an overridable default that could never possibly return null. I coded it this way:

private string _description;
public string Description {
   get => _description ?? CreateDefaultDescriptionFromOtherProperties();
   set => _description = value;
}

This pattern works as designed... until you use it in EF. This consistently reported that I couldn't insert NULL into the Description column, even though it is impossible for the Description property to return null.

Entity Framework, all the way through EF7, seems to first look to backing fields, and then to properties. The solution? Changing the name of the backing field allowed EF to work as expected:

private string _desc;
public string Description {
   get => _desc ?? CreateDefaultDescriptionFromOtherProperties();
   set => _desc = value;
}

[jomeno]

I have also just run into a similar issue:

"Cannot insert the value NULL into column 'Id', table 'zzz'; column does not allow nulls. INSERT fails."

The "Id" property of the entity is a non-nullable long so it defaults to a zero (0) yet I get above error while trying to save a new table record. I'm using <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.4" />