0

I am inserting new entity into the DB through Entity Framework, setting all properties, but the code still throws me the SqlException with text:

Cannot insert the value NULL into column C_CUSTKEY, table y; column does not allow nulls.

But I manually set all properties, they are not null. What can cause this problem?

Code:

            maxCustomerId = db.Customers.Max(o => o.C_CUSTKEY) + 1;
            var customer = new Customer
            {
                C_CUSTKEY = maxCustomerId,
                C_NATIONKEY = 1,
                C_NAME = "Peter",
                C_ADDRESS = "Praha",
                C_COMMENT = "Best customer",
                C_ACCTBAL = 0,
                C_PHONE = "12345"
            };
            db.Customers.Add(customer);
            db.SaveChanges();

This is my Customer entity:

public class Customer
{
    [Key]
    public int C_CUSTKEY { get; set; }

    public string C_NAME { get; set; }

    public string C_ADDRESS { get; set; }

    public int C_NATIONKEY { get; set; }

    [ForeignKey("C_NATIONKEY")]
    public virtual Nation Nation { get; set; }

    public string C_PHONE { get; set; }

    public decimal C_ACCTBAL { get; set; }

    public string C_COMMENT { get; set; }

}

Nation entity:

public class Nation
{
    [Key]
    public int N_NATIONKEY { get; set; }

    public string N_NAME { get; set; }

    public int N_REGIONKEY { get; set; }

    [ForeignKey("N_REGIONKEY")]
    public virtual Region Region { get; set; }

    public string N_COMMENT { get; set; }

}

Region entity:

public class Region
{
    [Key]
    public int R_REGIONKEY { get; set; }

    public string R_NAME { get; set; }

    public string R_COMMENT { get; set; }

}
3
  • Please post your code in your question so that we can see what problem is.. Commented Apr 14, 2017 at 7:46
  • 1
    You do know that doing this SELECT MAX(...) + 1 to get your new unique primary key is horribly bad?? In a busy system with a few concurrent users, rather sooner than later, you'll have duplicate values - which is NOT a good thing for a primary key ! Commented Apr 14, 2017 at 7:53
  • This is only the project where I test caching, I don't use this DB in any concurrent way. I am the only one that can access this DB. Commented Apr 14, 2017 at 7:54

2 Answers 2

2

Please decorate your primary key property of Customer entity as follows:

 [DataMember, Required, Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
 public int C_CUSTKEY { get; set; }

The key here is putting the attributes:

  1. Key
  2. DatabaseGenerated

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

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

9 Comments

I don't use my DB, I can't change its model or anything... Customers table always contains some data so that is not a problem. And I debuged it and maxCustomerId is set to specific number.
Ok then plz open your edmx model, delete your table from there and add it again. Its surely gonna work
But I am using Code First, I don't have edmx file :(
lol, you should specify it. Please post your Customers class in your question
Also ppost your Nation object
|
0

Have you updated your EDMX model?

If you haven't, Entity Framework can't know if you have changed your database column attributes. If you open your model and right click and choose "Update Model From Database" and then press "Finish", it should do the work.

Also, you can check if you have some Primary Key that is not specified as a incremental identity column on the database, and you are not setting any value on your code.

4 Comments

I have not updated my model. And in my DB the primary key is not specified as an incremental identity, so I increment it manually (see code)
In that case, I would say C_CUSTKEY is a nullable field and table doesn't have any rows where C_CUSTKEY is not null. That would make; db.Customers.Max(o => o.C_CUSTKEY) returns null. When you do +1, it returns null again, so you might be setting a null value there. Would that be it?
When I debug this, maxCustomerId is set to a specific number
Can you attach your table create SQL, i feel like a scooby doo, trying to solve some mystery

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.