Code first from Database - Cannot insert the value NULL into column 'Id' but value actually is not null

Question

I try to add new record :

                 entity.Id = Id;


                _bdc.EquifaxAnswers.Add(entity);

                _bdc.SaveChanges();

and Id has exactly defined as primary key and Id in code has value ( unique for table). And EF create sql code for add record:

 INSERT [dbo].[EquifaxAnswers]([FileName], [dtSend], [dtDateTime], [RecordsAll], [RecordsCorrect], 
[RecordsIncorrect], [ResendedId], [dtEmailAbout], [StartDate], [EndDate])
VALUES (@0, @1, @2, NULL, NULL, NULL, NULL, NULL, @3, @4)

And as we can see there Id does not exist, so _bdc.SaveChanges();create Exception:

Failed in 25 ms with error: Cannot insert the value NULL into column 'Id', table 'Equifax.dbo.EquifaxAnswers'; column does not allow nulls. INSERT fails.

Primary key definition:

    public partial class EquifaxAnswers
{

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

Why EF don't add Id to INSERT and how to resolve this problem ?

UPD: Table definition script in database:

CREATE TABLE [dbo].[EquifaxAnswers](
[Id] [int] NOT NULL,
[FileName] [nvarchar](300) NOT NULL,
[dtSend] [datetime] NOT NULL,
[dtDateTime] [datetime] NULL,
[RecordsAll] [int] NULL,
[RecordsCorrect] [int] NULL,
[RecordsIncorrect] [int] NULL,
[ResendedId] [int] NULL,
[dtEmailAbout] [datetime] NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
CONSTRAINT [PK_EquifaxAnswers] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

paul · Accepted Answer · 2020-02-04 10:11:18Z

1

You have specified DatabaseGeneratedOption.None

This means that EF is not expecting the database to generate the Id field, and that you should specify the Id field yourself.

If you want the database to generate the Id field automatically, then alter the column to be an IDENTITY type, and change the code to DatabaseGeneratedOption.Identity

answered Feb 4, 2020 at 10:11

paul

22.1k1 gold badge56 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aleksandr Zolotov Over a year ago

I specify the Id field by myself - but EF even not include this field in INSERT - my question is about this !

Aleksandr Zolotov Over a year ago

And more of this - I have tryed DatabaseGeneratedOption.Identity too - the same exception ...

DevilSuichiro Over a year ago

are you sure EF model is up to date? looks like EF model isnt updated and its still using Identity.

Aleksandr Zolotov Over a year ago

@DevilSuichiro - absolutely sure, because it was always "None" - as I sad - I use Code First from Database - so I cannot change database stucture, I should use what I have

Aleksandr Zolotov · Accepted Answer · 2020-02-05 10:34:58Z

1

Solution:

            modelBuilder.Entity<EquifaxAnswers>()
            .Property(a => a.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

If this doesn't work - just uninstall and install again all EF packages ( I don't know why - but this is work for me) for all dependent projects , latest stable version of course.

Reasons:

By convention, EF will assume that primary key properties are automatically generated. So we need to say EF that we will do it by our code. However,this is not clear why doesn't work DataAnnotations like this:

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

but fluent API is working well. Perhaps the reason is that I use both.

edited Feb 5, 2020 at 10:34

answered Feb 5, 2020 at 9:01

Aleksandr Zolotov

1,1303 gold badges20 silver badges33 bronze badges

1 Comment

John H Over a year ago

The fix for me was to add [DatabaseGenerated(DatabaseGeneratedOption.None)].

Collectives™ on Stack Overflow

Code first from Database - Cannot insert the value NULL into column 'Id' but value actually is not null

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related