2

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: enter image description here

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:

enter image description here 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; }
}

enter image description here

enter image description here

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.

6
  • see this not sure but it might help you and good luck Commented Apr 1, 2017 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. Commented Apr 1, 2017 at 13:06
  • identity for string is useless Commented Apr 1, 2017 at 13:07
  • If i set to column to allow nulls then it cant be a primary key and i want to be a primary key. Commented Apr 1, 2017 at 13:29
  • Then why you setting NULL to the identity column, what is purpose? Commented Apr 1, 2017 at 13:33

1 Answer 1

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

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

9 Comments

If I remove it I still get the same exception
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()).
In case when you remove it - it should work if database was updated accordingly.
@hvd, not sure but isn't migration script will do it automatically?
@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.
|

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.