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.

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
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
Then why you setting NULL to the identity column, what is purpose? — Fabio

Fabio Fabio · Accepted Answer · 2017-04-01T13:47:20

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().

Cannot insert NULL into column while value is not null

1 Answers