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.

Answer 1

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; }