Code first - Cannot insert the value NULL into column 'Id'

Question

This is my code, very simple...

 var newUser = new User();
        newUser.Id=id;
        newUser.Email = email;
       this.DataContext.Set<User>().Add(newUser);
        this.DataContext.SaveChanges();

The error I get is a sqlexception at this.DataContext.SaveChanges(); stating that:

Cannot insert the value NULL into column 'Id', table 'xxxxx.dbo.Users'; column does not allow nulls. INSERT fails.

I have debugged and found that there is value in Id & Email in newUser at this.DataContext.Set<User>().Add(newUser);

If this is the case, how is the value becoming null?

The error stack trace:

[DbUpdateException: An error occurred while updating the entries. See the inner exception for details.]
   System.Data.Entity.Internal.InternalContext.SaveChanges() +204
   System.Data.Entity.Internal.LazyInternalContext.SaveChanges() +23
   System.Data.Entity.DbContext.SaveChanges() +20

I have not been able to understand or solve this....

Would sincerely appreciate any help in this...

Regards Arnab

Solution

Ok, thanks to Ladislav to point me in the right direction: Adding the attribute [DatabaseGenerated(DatabaseGeneratedOption.None)] solved the problem.

What is the type of id? Could be it that something is going wrong with generating the key in the database? — Wouter de Kort, Oct 22 '11 at 08:15
type is bigint in sqlserver, in User class it is long.. Also when I use normal entity framework without codefirst, it works — Arnab, Oct 22 '11 at 09:04
How did you map the user? EF code first by default expects that Id value is generated in the database (by Identity settings). — Ladislav Mrnka, Oct 22 '11 at 10:32
My User Class has two fields of which Id has [key] attribute. In db I have made Id primary key but have stated IsIdentity =no , so the value is not going to be generated in the db. @Ladislav : what u r saying may be the problem, how do I solve this? I can not make isIdentity=yes — Arnab, Oct 22 '11 at 11:07
ok, thanks to Ladislav to point me in the right direction: Adding the attribute [DatabaseGenerated(DatabaseGeneratedOption.None)] solved the problem. — Arnab, Oct 22 '11 at 11:40

score 24 · Answer 1 · edited Jul 28 '17 at 02:27

24

Referring to this post it seems that entity framework expects by default that you insert into identity column.

To solve this try:

modelBuilder.Entity<BOB>()
    .HasKey(p => p.Id)
    .Property(p => p.Id)
    .StoreGeneratedPattern = StoreGeneratedPattern.None;

builder.Entity<BOB>().MapSingleType().ToTable("BOB");

or decorate your key in the POCO with:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)] //Fixed typo
public Int64 PolicyID { get; set; }

edited Jul 28 '17 at 02:27

ransems

641
7
19

answered Mar 25 '13 at 13:00

Ahmed Eid Yamany

799
6
8

1

I believe the enum flag for that attribute is "DatabaseGeneratedOption" and not "DatabaseGenerationOption" – Marcus Cunningham Apr 15 '16 at 14:49
It worked for me too can any one explain why it happened how this attribute solved the issue. – Shrivallabh May 30 '16 at 08:32

score 7 · Answer 2 · edited May 23 '17 at 12:25

I ran into the same problem, found your question, and then noticed this Entity Framework 4.1 code-first KeyAttribute as non-identity column question ( which has an attribute that solved my problem ).

If you define User as:

public class User
{
    [DataMember, Required, Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public long ID { get; set; }

    [DataMember]
    public string Email { get; set; }
}

The key here is putting the attributes:

Key
DatabaseGenerated

onto the ID column. Apparently the issue we are fighting is that Entity Framework by default expects to do inserts with keys being identities.

score 2 · Answer 3 · answered Aug 01 '13 at 23:25

I fixed this by setting StoreGeneratedPattern on the column properties to Computed.

Step by step below:

Open your EDMX (double click in Visual Studio)
Right click on the column that causes the problem, select properties.
Change StoreGeneratedPattern to be Computed.

From the IDE enter image description here

Hope that helps somebody.

Code first - Cannot insert the value NULL into column 'Id'

3 Answers3

Linked