Cannot insert NULL into column while value is not null

Question

So im having this weird problem with trying to add an instance to the entities. Its a asp.net application using EF 6.0.

The exception:

SqlException: Cannot insert the value NULL into column 'OrderId', table 'BETA.MDF.dbo.Orders'; column does not allow nulls. INSERT fails. The statement has been terminated.

My code:

User user = (User)Session["CurrentUser"];
BetaEntities entities = new BetaEntities();

Beta.Order order = new Beta.Order();
order.OrderId = Guid.NewGuid().ToString();
order.OrderDate = DateTime.Now;
order.OrderedBy = user.UserId;
order.HandledBy = entities.Users.Where(x => x.Rank > 0).Select(i => i.UserId).FirstOrDefault();

entities.Orders.Add(order);

entities.SaveChanges();

My table:

Also this is my order class, i already tried [Databasegenerated]

public partial class Order
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public string OrderId { get; set; }
    public System.DateTime OrderDate { get; set; }
    public int OrderedBy { get; set; }
    public int HandledBy { get; set; }
}

Example entry:

Please tell me if you need more details.

EDIT:

I tried setting the database datatype of OrderID to UNIQUEIDENTIEFIER and updated the entity model so the database should generate the guid(and i removed itself but i still get the same exception.

This is my new class:

public partial class Order
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public System.Guid OrderId { get; set; }
    public System.DateTime OrderDate { get; set; }
    public int OrderedBy { get; set; }
    public int HandledBy { get; set; }
}

EDIT:

If i make OrderID NULLABALE and remove the primary key i get the following exception:

System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: 'Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.'

But this can be resolved by adding a primary key :( so removing the PK and making it NULLABLE isnt working.

Also i need to assign to GUID myself for further purposes so letting the database assign the guid itself(with [DatabaseGenerated(DatabaseGeneratedOption.Identity)]) is also not an option.

see [this](http://stackoverflow.com/questions/8855100/why-is-ef-trying-to-insert-null-in-id-column) not sure but it might help you and good luck — Jaqen H'ghar, Apr 01 '17 at 13:04
You get pretty clear exception message: _Cannot insert the value NULL into column 'OrderId', because column does not allow nulls_. Just set column to allow `NULL` values. — Fabio, Apr 01 '17 at 13:06
If i set to column to allow nulls then it cant be a primary key and i want to be a primary key. — Jurriaan Buitenweg, Apr 01 '17 at 13:29
Then why you setting `NULL` to the identity column, what is purpose? — Fabio, Apr 01 '17 at 13:33
@Fabio Im not sure i understand you, my problem is that it says OrderId has a value of NULL but it doesnt. I have never set orderId to NULL. — Jurriaan Buitenweg, Apr 01 '17 at 13:37

Fabio · Accepted Answer · 2017-04-01T17:46:23.007

2

Your problem is that you set DatabaseGenerated attribute to the OrderId column.

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public string OrderId { get; set; }

With this attribute EF will ignore value you set to OrderId and "try" generate unique value, but generating unique value will not work for string and OrderId will be NULL when INSERT query is generated.

If you are using EF "code first" then change type of OrderId to Guid and remove next line from code

order.OrderId = Guid.NewGuid().ToString();

And leave DatabaseGenerated attribute as it is.
When using DatabaseGenerated attribute for column - means that value of this column will be generated by database.

After changing your structure of Order class, you need update database accordingly. If you using "code-first" approach then you need generate migration script and run it against your database. In "database-first" approach you need update database manually

Even you said removing DatabaseGenerated attribute doesn't help, it will be most simpler solution as suggested by Ivan Stoev in the comments.
Because you generating unique string by yourself using Guid.NewGuid().ToString().

edited Apr 01 '17 at 17:46

answered Apr 01 '17 at 13:47

Fabio

31,528
4
33
72

If I remove it I still get the same exception – Jurriaan Buitenweg Apr 01 '17 at 13:49
There's two things missing from this answer: change the type of `OrderId` to `Guid`, yes. But also adjust the column type in the database to match, and give it a default constraint of `newid()` (or `newsequentialid()`). – Apr 01 '17 at 13:54
In case when you remove it - it should work if database was updated accordingly. – Fabio Apr 01 '17 at 13:55
@hvd, not sure but isn't migration script will do it automatically? – Fabio Apr 01 '17 at 13:55
@Fabio If migrations are enabled, maybe, I don't know. But the OP didn't specify migrations, so I'm not assuming they're enabled. – Apr 01 '17 at 13:57
I tried this answer but i still get the same exception :( – Jurriaan Buitenweg Apr 01 '17 at 15:47
Since OP is specifying the `OrderId` value for new entities, I'm wondering why don't you recommend removing the `DatabaseGenerated` attribute, which is more natural for what they are doing rather than changing their table filed type, removing code etc. – Ivan Stoev Apr 01 '17 at 17:35
@IvanStoev :) - I did in the comment of this answer, but OP said it doesn't work – Fabio Apr 01 '17 at 17:38
I also added the result of removing the PK and making it nullable. Also i need to assign the OrderID myself for further purposes in the code so DATABASEGENERATED("identity") wont be an option im afraid. – Jurriaan Buitenweg Apr 01 '17 at 19:17

Cannot insert NULL into column while value is not null

1 Answers1