Why is EF trying to insert NULL in id-column?

Question

I am writing my project using Entity Framework 4.0 (Model first). At the beginning of the project, I faced with this problem: I am trying to insert the filled object in the database, but I get an exeption:

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

Code:

    Category usingCategory = new Category("Using Forums", "usingforums", 0);
    using (Context)
    {
        Context.Categories.AddObject(usingCategory);
        Context.SaveChanges();
    }

I checked this object, and I am sure that it is filled.

Just in case:

public Category(string name, string urlName, int index)
{
    CategoryId = Guid.NewGuid();
    Name = name;
    UrlName = urlName;
    CategoryIndex = index;
}

What is going on?

Possible duplicate of [Entity Framework CTP 4. "Cannot insert the value NULL into column" - Even though there is no NULL value](https://stackoverflow.com/questions/4444407/entity-framework-ctp-4-cannot-insert-the-value-null-into-column-even-though) — Sayed Abolfazl Fatemi, Nov 19 '19 at 12:12

score 53 · Accepted Answer · edited May 23 '17 at 12:18

53

Have a look at this: https://stackoverflow.com/a/5338384/171703 - entity framework might be assuming that your CategoryId field is an identity and therefore passing null to the database expecting it to fill it for you.

edited May 23 '17 at 12:18

Community

1
1

answered Jan 13 '12 at 18:06

akiller

2,462
22
30

2

It's good, but I use model-first, but in this question the problem has been solved in the scope of code-first. – Kovpaev Alexey Jan 13 '12 at 18:09
@KovpaevAlexey The accepted answer in that question is for code first, but the specific linked answer in that question is for model first. – Jan 13 '12 at 18:14
@hvd, this problem has solved when I added generating id on DB-side. – Kovpaev Alexey Jan 13 '12 at 18:36
2

yep - EF assumes it's going to be genereated/incremented at the DB level, so it passed your `Id` col as `NULL`, even when there's a value in it. FYI, I think this answer is more specifically helpful for this issue: http://stackoverflow.com/a/5101369/1985648 – Don Cheadle Dec 13 '15 at 22:39
4

TL;DR - add this to your key if it isn't auto-generated: [DatabaseGenerated(DatabaseGeneratedOption.None)] – Jacob Morris Dec 01 '16 at 13:41
and whats the solution, dont point me to another thread, why are you so lazy ? – oren revenge Feb 18 '22 at 10:13

score 44 · Answer 2 · answered Feb 06 '17 at 23:28

44

I ran into this today and had to regenerate my EF classes from the database.

After doing that, I found that EF added:

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

to this"Id" field that used to be an Identity column in the SQL but was changed to be app-assigned.

I think if you don't have that attribute EF won't actually send the ID to the database ('convention over configuration')

answered Feb 06 '17 at 23:28

D.B. Fred

550
4
6

2

Worked for me too: didn't need to regenerate the classes, just added the attribute to the property and it worked. – sluiper Sep 27 '17 at 06:54

score 7 · Answer 3 · answered Apr 03 '17 at 04:56

7

I had created the table with int Id as PK, but had forgotten to set "Identity Specification" = True

answered Apr 03 '17 at 04:56

Adam Cox

3,341
1
36
46

You made my day ! – Hari Nov 17 '17 at 04:03

score 6 · Answer 4 · answered Aug 02 '18 at 14:39

6

Try to add this into your model class .cs file:

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

Or change your column CategoryId to identity:

    CategoryId int IDENTITY(1,1)

answered Aug 02 '18 at 14:39

Damir Varevac

341
2
10

score 1 · Answer 5 · answered Apr 17 '19 at 00:04

1

For me I had a different schema other than default one. The name of the Id key was some how missing the that schema part - fixed that in the database and it all went well. How it was ;

PK_TableName

How I changed it to

PK_mySchema.TableName

answered Apr 17 '19 at 00:04

marifrahman

681
2
13
31

score 0 · Answer 6 · edited Jun 25 '20 at 10:37

Met the same problem today.

Here is how I figured out the issue.

I had added the identity seed to the column initially but after that I removed it. So I did not realize that when modifying the column definition in the Code first, the Id

Property(x => x.Id)
    .HasColumnName("Id")
    .HasColumnType("int")
    .IsRequired();

So Entity framework figured out that it was an Identity column and I got the exception above.

To fix this, I added the following:

HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)

so the final piece was something like:

Property(x => x.Id)
    .HasColumnName("Id")
    .HasColumnType("int")
    .IsRequired()
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

score 0 · Answer 7 · answered Apr 03 '20 at 14:40

0

İf Model first try block

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

answered Apr 03 '20 at 14:40

tayfun Kılıç

2,042
1
14
11

score 0 · Answer 8 · answered Jul 08 '20 at 17:58

I know I'm a bit late but here's how I managed to fix it.

I included these attributes to my int PK in my models. (to give way for my seed in configuration.cs) [Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
After I've inserted my seed I use the SQL Server Object Explorer in Visual Studio and went to the table of this model by right clicking the table and press 'View Designer'
Below you will see the script generated for creating that table. Insert this command right after your primary key property.

IDENTITY(1,1)

Apply script changes by clicking 'Update' just above the table view (left corner)
After updating the database, go back to your model and change this attribute:

[DatabaseGenerated(DatabaseGeneratedOption.None)] to [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
Save, Add migration then Update Database should do it.

Everytime you insert, it will now increment depending on what you specified on the IDENTITY script.

Why is EF trying to insert NULL in id-column?

8 Answers8

Linked