ASP.NET MVC OptimisticConcurrencyException

Question

I am trying to write to two tables in my database in a function that takes in lists as a parameter from the previous page. I call the db to retrieve the purchase_order_no because the column is an IDENTITY primary key that is generated on entry.

Models:

purchase_order

    [Key]
    [Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int client_no { get; set; }

    [Key]
    [Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int purchase_order_no { get; set; }

    [StringLength(60)]
    public string name { get; set; }

    [Key]
    [Column(Order = 2)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int order_group_no { get; set; }

    [StringLength(24)]
    public string purchase_order_reference { get; set; }

    [Key]
    [Column(Order = 3)]
    public DateTime order_timestamp { get; set; }

order_detail

    [Key]
    [Column(Order = 0)]
    public long order_detail_no { get; set; }

    [Key]
    [Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int purchase_order_no { get; set; }

    [Key]
    [Column(Order = 2)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int inventory_no { get; set; }

    [Key]
    [Column(Order = 3)]
    public decimal quantity { get; set; }

    public int? vendor_no { get; set; }

I receive this error when trying to insert my new 'purchase_order' model into the db:

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.

    [HttpPost]
    public ActionResult orderForm (List<int> quantity, List<string> itemName, List<int> inventory_no, List<int> client_no, List<int> vendorCode, int orderGroupNo)
    {
        using (var db = new db_model())
        {
            var ctx = ((IObjectContextAdapter)db).ObjectContext;
            purchaseOrderVM poVM = new purchaseOrderVM();
            List<order_detail> tempList = new List<order_detail>();
            purchase_order po = new purchase_order();
            po.client_no = client_no[0];
            var purchaseOrder = db.purchase_order.Where(x => x.client_no == po.client_no).Max(x => x.purchase_order_no);
            po.order_group_no = orderGroupNo;
            po.order_timestamp = DateTime.Now;
            db.purchase_order.Add(po);
            try
            {
                db.SaveChanges(); <!-- This is where I get the error -->
            }
            catch (OptimisticConcurrencyException e)
            {
                ctx.Refresh(RefreshMode.ClientWins, db.purchase_order);
                throw e;

            }
            for (int i = 0; i < itemName.Count(); i++)
            {
                order_detail od = new order_detail();
                od.purchase_order_no = db.purchase_order.Where(x => x.client_no == po.client_no).Max(x => x.purchase_order_no);
                od.inventory_no = inventory_no[i];
                od.quantity = quantity[i];
                od.vendor_no = vendorCode[i];
                db.order_detail.Add(od);
                try
                    {
                        db.SaveChanges();
                    }
                catch (OptimisticConcurrencyException e)
                    {
                        ctx.Refresh(RefreshMode.ClientWins, db.order_detail);
                        throw e;
                    }
                tempList.Add(od);
            }
            poVM.purchase_order = po;
            poVM.orderList = tempList;
            return View(poVM);

        }
    }

Answer 1

I think the problem is in your model class. In purchase_order, why do you have so many columns flagged as [Key]? As purchase_order_no is declared as an identity column, it should be the primary key by itself. I don't even know why EF doesn't complain at startup, because your configuration makes no sense.

Remove the [Key] attribute on all other columns, and it should work. If you need to ensure uniqueness, you can create a unique index on the other columns.

Answer 2

I will not question your model as to why you have so many columns flagged as [Key] in the first place. That's not helping you with your problem. Moreover, you might want all those columns in your primary key for performance reasons: when using code first with EF, the primary key is created as clustered by default.

The problem is probably not the number of columns with attribute [Key]. I had the same problem after incorporating just one extra column of type DateTime in the primary key, and you also have a DateTime column in your primary key combination for entity purchase_order.

It looks like EF is not handling DateTime columns very well, when marked as part of the primary key. I found an interesting webpage that confirms this: https://social.msdn.microsoft.com/Forums/en-US/6f545446-aa26-4a21-83e9-60f7fa5ed3b0/optimisticconcurrencyexception-when-trying-to-insert-a-new-record-in-table-using-entity-framework?forum=adodotnetentityframework

I can think of two solutions for your problem:

1. If you want to stick with your primary key definition: truncate your [order_timestamp] to a value your database does accept. In case of SQL Server you'll be doing fine when truncating to 10 msec, C#:

   order_timestamp = DateTime.Now.Truncate(TimeSpan.FromMilliseconds(10));
   

2. If you don't want to truncate your timestamp but accept to change your PK combination to an index: consider adding an autonumber column for the primary key:

   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   [Key]
   public int Id { get; set; }
   

   and changing the other key attributes to:

   [Index("IX_purchase_order", Order = 1)],
   [Index("IX_purchase_order", Order = 2)], etc.
   

   For performance, you might want the index to be created as clustered:

   [Index("IX_purchase_order", IsClustered = true, Order = 1)], etc.
   

   But when using Migrations, bear in mind that unfortunately EF will still try to create your PK as clustered as well. This would give errors when issuing command update-database, you first will have to change your migration code a little. Although in the code you will find something like:

   .Index(t => new{client_no, ..., order_timestamp}, clustered: true, name: "IX_purchase_order")
   

   you explicitly must declare the primary key non-clustered, by changing the migration code for the PK part to:

   .PrimaryKey(t => t.Id, clustered: false) 
   

I hope this helps.