DbUpdateConcurrencyException on Add

Question

I am having an issue adding a new entity via the DbContext.Add(...) method. On DbContext.SaveChanges(), I get a DbUpdateConcurrencyException. I'm trying to do an insert into the database. I'm the only developer on the application, running localhost, and database table is pristine. There are no users. It's just me.

This didn't help, although it's similar:
Entity Framework 6 DbUpdateConcurrencyException When Adding New Rows To Database

I'm using one DbContext per request. Like this:
One DbContext per request in ASP.NET MVC (without IOC container)

I'm logging out the sql statement generated by the entity framework like this:
https://stackoverflow.com/a/20751723/2088914

Here's the model (code first, with migrations). When I post, I only provide the associate, role, assignor, and deleted flag. The row id is automatically set to 0 since I don't provide it from the client, and date is a default value as well, which I then populate at the server - all expected stuff:

public class Permission {

    [Column("row_id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int RowId { get; set; }

    [Key]
    [StringLength(10)]
    [Column("associate_nt_id", Order = 0)]
    [Required]
    public string AssociateNtId { get; set; }

    [Key]
    [Column("role_name", Order = 1)]
    [StringLength(50)]
    [Required]
    public string RoleName { get; set; }

    [Key]
    [Column("eff_ts", Order = 2)]
    [Required]
    public DateTime EffectiveTimestamp { get; set; }

    [StringLength(10)]
    [Column("assignor_nt_id")]
    [Required]
    public string RoleAssignorNtId { get; set; }

    [Column("deleted")]
    [Required]
    public bool Deleted { get; set; }

}

I have a web api controller with a post method, which is successfully accessed by an angular spa, and model gets populated:

[HttpPost]
[Route("")]
[Require(Roles.admin)]
public Permission Post(Permission permission) {
    permission.EffectiveTimestamp = DateTime.Now;
    MAPDB.Current.Permissions.Add(permission);
    MAPDB.Current.Database.Log = s => {
        Debug.WriteLine(s); 
    };
    MAPDB.Current.SaveChanges();
    return permission;
}

I run in debug in visual studio 2015. Here's the error message:

Here's the sql that the entity framework is generating:

INSERT [dbo].[_permissions]([associate_nt_id], [role_name], [eff_ts], [assignor_nt_id], [deleted]) 
VALUES (@0, @1, @2, @3, @4) 
SELECT [row_id] 
FROM [dbo].[_permissions] 
WHERE @@ROWCOUNT > 0 AND [associate_nt_id] = @0 AND [role_name] = @1 AND [eff_ts] = @2 
-- @0: 'associate' (Type = String, Size = 10)
-- @1: 'user' (Type = String, Size = 50) 
-- @2: '9/20/2017 12:49:05 PM' (Type = DateTime2)
-- @3: 'assignor' (Type = String, Size = 10)
-- @4: 'True' (Type = Boolean) 
-- Executing at 9/20/2017 12:49:05 PM -05:00
-- Completed in 24 ms with result: SqlDataReade
-- Closed connection at 9/20/2017 12:49:05 PM -05:00

It seems to have executed in logger, and the statement is exactly what I would expect, except no database insert occurs. If I take the statement out of the logger, fill in the parameters, and execute it via a sql client, it works and returns the row id of what was created. I don't understand what the problem is - there's no concurrency situation happening, no column missing/added when it shouldn't be, no invalid parameters, no problems mapping the columns to object properties, no key violations, no issues with identity column.

How can I get the entity framework to just insert this new object (without swallowing the exception)?

Thank you for your help.

You could also mark the `RowId` as the primary key but make the primary clustered index on the 3 fields you are currently using as key. You would also have to manually change your generated migration script. — Igor, Sep 20 '17 at 20:00
@Igor This was a helpful suggestion. I got it to work for now as below, but I'm wondering if I do what your saying, I would probably have to do both: change the column type and switch around the index and key. Thank you. — o.carltonne, Sep 20 '17 at 22:02

score 5 · Accepted Answer · answered Sep 20 '17 at 19:46

Most likely the issue is caused by the DateTime part of the PK (EffectiveTimestamp). The record is inserted, but then the SELECT is not finding it, since by default the EF maps DateTime properties to datetime columns, while as you can see from the log, the parameter type is datetime2, which has higher precision, so it's get truncated when stored to the table column, hence is not matched by the [eff_ts] = @2 part of the select filter.

To see if that's the case, you can try trimming the time value:

// ...
var time = DateTime.Now;
permission.EffectiveTimestamp = new DateTime(time.Year, time.Month, time.Day, time.Hour, time.Minute, time.Second);
// ...

If that works (as I expect), you might consider changing the database column type:

[Key]
[Column("eff_ts", Order = 2, TypeName="datetime2")]
[Required]
public DateTime EffectiveTimestamp { get; set; }

Yes, this worked. Specifically, I did what you said for `TypeName="datetime2"`, and also removed the `DateTime.Now` truncation once that was done. Thank you so much. — o.carltonne, Sep 20 '17 at 22:06

score 0 · Answer 2 · answered Mar 14 '22 at 16:48

I had this issue as well. Apparently Daylight Savings Time screwed up all the records that EF was caching in memory. I figured this out by turning on Information logging for Microsoft.EntityFrameworkCore.Database.Command, and where it should have been an INSERT statement, it was a big crazy MERGE statement... Resetting the service fixed it. Thanks, Daylight Savings Time...

DbUpdateConcurrencyException on Add

2 Answers2