CODEWITHSUNDEEP
c#sql-serverentity-framework
Ravid Goldenberg
Ravid Goldenberg

Reputation: 2299

cannot insert the value null into column ... when the value is not null

in my c# code I have an insert into my DB, but it throws an exception

Cannot insert the value NULL into column 'BoxID', table 'Moroccanoil_Replicated.dbo.Boxes'; column does not allow nulls. INSERT fails.

however when debugging this piece of code I see that the box is actually not null, therther more when i test the same exact insert in my SQL SM It works just fine.

Edit:

For some reason this is the query that is actually being executed according to the profiler :

exec sp_executesql 
  N'INSERT [dbo].[Boxes]([MasterBoxID], [DateTime], [Reported], [StationID])
  VALUES (NULL, @0, @1, @2)
  SELECT [BoxID]
  FROM [dbo].[Boxes]
  WHERE @@ROWCOUNT > 0 AND [BoxID] = scope_identity()',
  N'@0 datetime2(7),@1 bit,@2 int',@0='2015-11-30 13:37:46.4714394',@1=0,@2=7

Does anyone know why is this and how to fix it?

More relevant information :

Here is the code in debug mode with the inserted values: enter image description here

And the same insert in SQL that works just fine: enter image description here

Upvotes: 3

Views: 3871

Answers (2)

Dinch
Dinch

Reputation: 612

Like mentioned, EF does not pass ID column to the database. If you have a case that you don't want to have auto increment value, below is an example

public class PackingInventory
{

    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ID { get; set; }

Upvotes: 1

DevilSuichiro
DevilSuichiro

Reputation: 1059

As mentioned in comments:

Most likely EF won't pass the ID parameter because it thinks the value is created by the database. This means you should turn off DatabaseGeneratedOptions.Identity for this column.

With DatabaseGeneratedOption.Identity, EF won't pass the ID value to the database because it suspects it to be given by DBMS. This can lead to this issue or an DbUpdateConcurrencyException, when it suspects an object with a given Id value to be in the database, but it was actually altered by DBMS.

Upvotes: 7

Related Questions