Auto assign column value when row is created

Question

I am using EF6 and SQL Server 2014.

I have something like the following simplified model:

public class Order
{
    public int Id { get; set; }
    public DateTime Created { get; set; }
    public IEnumerable<OrderItem> OrderItems { get; set; }
}
public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; }
    public string ItemName { get; set; }
    public int OrderItemNumber { get; set; }
}

The Id properties are standard auto-increment primary keys.

The OrderItem.OrderItemNumber property uniquely identifies an OrderItem within the context of a given Order.

When a new OrderItem row is inserted it needs to be assigned the next OrderItemNumber for the parent Order, something like SELECT COALESCE(MAX(OrderItemNumber),0) FROM dbo.OrderItems WHERE OrderId = 2

Is there a means using EF6/SQL Server to auto-assign the OrderItemNumber at the point a row is inserted?

I've edited question to show why this is not a dupe of http://stackoverflow.com/q/5631489/95423 — TonE, Apr 07 '17 at 17:33
See [this](http://stackoverflow.com/questions/24162895/entity-framework-6-and-sql-server-sequences) — Steve Greene, Apr 07 '17 at 17:41

score 0 · Answer 1 · answered May 04 '17 at 13:03

Although this proved an interesting learning experience on EF support for triggers (see https://github.com/NickStrupat/EntityFramework.Triggers) I eventually decided to re-model.

In this case my question was a result of incorrect modelling. The OrderItemNumber property was redundant so I removed it; the sequence the OrderItems were created can be inferred from the Id value, and there was no real requirement to allow re-sequencing.

Auto assign column value when row is created

1 Answers1