Entity Framework CTP 4. "Cannot insert the value NULL into column" - Even though there is no NULL value

Question

Im using EF CTP 4. I have a simple console app (for testing purposes) that is using EF to insert some data into a SQL database.

I have come to a problem where by upon inserting the item

using(var context = GetContext())
{
   BOB b = new BOB();
   b.Id = 1;

   context.Bobs.Add(b);
   context.SaveChanges();
}

It throws the error: {"Cannot insert the value NULL into column 'Id', table 'TestDB.dbo.BOB'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated."}

The Table just has 1 field of Id int NOT NULL which is the primary key and is not an auto incremented Id.

On the creation of the DataContext I have this configuration, which yes does get fired.

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<BOB>().HasKey(b => b.Id);
    builder.Entity<BOB>().MapSingleType().ToTable("BOB");
}

I have also pre-populated this table and then through the debugger been able to via watch load up this BOB object... so I am really stumped, as for being able to load up my BOB shows that all is right... however upon inserting a new one it crashes...

Answer 1

Ran into this issue as well. For me my Dev DB Table for some reason didn't bring over the Key so EF didn't define my ID as a key.

Note to anyone with this issue: Make sure that your table that you trying to insert a new record into with an ID field is actually defined in your Context class.

modelBuilder.Entity<CustomerEmail>(entity =>
        {
            entity.HasKey(e => e.Id)
                .HasName("PK_tblCustomerEmail");

            entity.Property(e => e.Customer).IsUnicode(false);

            entity.Property(e => e.Email).IsUnicode(false);
        });

Answer 2

I'm using EF 4.1, Model First and came across this problem. Here's how I solved it:

When using the Model Designer surface, when you create an Entity, you have to define a Key property, it defaults to Id, int32.

In my situation, I've chosen to use Guids for the Id, so I'd switch the int32 to Guid. But if you examine this Id after you create the entity, I saw that the Id's 'StoreGeneratedPattern' had 'identity' selected. At first I didn't think that was a problem, but when I examined the SQL being used to insert into the database, it was a bit weird in that it wasn't sending my Id. Frustrating!

But once I went back and changed the 'StoreGeneratedPattern' from 'identity' to 'none', regenerated the db and rebuilt the project, this strange message stopped happening:

Cannot insert the value NULL into column 'Id', table 'TestDB.dbo.BOB'; column does not allow nulls. INSERT fails. The statement has been terminated.

FYI - upon viewing the sql some more it seems that if you have identity chosen for StoreGeneratedPattern, the EF saves the object to the db (sans Id), then immediately fetches back the identity and saves that back to your object. i.e. this choice for StoreGeneratedPattern relies on the db to generate your Id, NOT your code!

Answer 3

In my case EntityFramework generated this code inside Context.cs class:

modelBuilder.Entity<MODEL_OF_TABLE>(entity =>
{
    entity.Property(e => e.Id).ValueGeneratedNever(); // <= this line must remove
    ...
}

after remove this line, problem solved.

Answer 4

If you are using database first approach then first delete the respective entity from the edmx diagram and then Update the model from database , this will surely resolve your issue

Answer 5

You could also use

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

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

Answer 6

I had a similar situation but in my case even setting Identity to off didn't help.

The problem was connected with Primary Key, which I've missed to add in my Entity Model.

Here is the script which was generating the model:

 CREATE TABLE [im].[SomeGroup]
 (
    [Id] INT NOT NULL IDENTITY(1,1), -- this is mandatory for EF
    [OtherGroupId] INT NOT NULL,
    [Title] NVARCHAR(512) NOT NULL
 )

The C# code for above is:

Insert(new SomeGroup
{
  // I'm not providing Id here, cause it will be auto-generated
  SomeGroupId = otherGroup.Id,
  Title = otherGroup.Title
});

Here is also some explanation of that.

Answer 7

I know that this question is somehow dated and an accepted solution has been already found, however I thought it would be useful if I share my findings.

I had this error today because I was using two different instances of the same DataContext. I was creating a new model with some properties - values for these properties were loaded from the database using one instance of the DataContext, then I was trying to push this newly created model into database calling first Add() and then SaveChanges() on a different instance of the DataContext. After I started using the same instance for both getting the values for the properties and actually adding and saving the new object - everything started working.

Answer 8

i have the same issue here and it's really an ugly solution.

 [Key]
public Int64 PolicyID { get; set; }

this is NOT an auto generated number

then i hit the same error.

EF Code First CTP5

after apply this:

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

then it will work.

Answer 9

It happened to me when I had a primary key missing on the respected column (the identity column) in the db's schema. I exported data between SQL servers, using SSMS Export tool and creating a new database, but didn't realize that it's exporting only the data, without keys.

Answer 10

Have you tried explicitly specifying the StoreGeneratedPattern?

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

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