DbUpdateConcurrencyException when Editing an Entity

Question

I have the following classes in an EF6 application.

public class Extension
{
    [Key]
    [Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [ForeignKey("Record")]
    public string RecordId { get; set; }
    public Record Record { get; set; }

    [Key]
    [Column(Order = 2)]
    [DataType(DataType.Date)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "Extension Date")]
    public DateTime ExtensionDate { get; set; }

    [Required]
    [Range(1, 100)]
    [Display(Name = "Days Extended")]
    public int DaysExtended { get; set; }

    [Required]
    [Display(Name = "Details")]
    public string Details { get; set; }

    [Required]
    [ForeignKey("ExtendedByUser")]
    public string ExtendedByUserId { get; set; }
    public ApplicationUser ExtendedByUser { get; set; }
}

public class Record
{
    // Id
    [Key]
    public string Id { get; set; }

    // Status
    [Required]
    public RecordStatus Status { get; set; }

    // Date Created
    [Required]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "Date Created")]
    public DateTime DateCreated { get; set; }

    // Created By
    [ForeignKey("CreatedByUser")]
    public string CreatedByUserId { get; set; }
    public ApplicationUser CreatedByUser { get; set; }

    // Date of last edit
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    public DateTime? LastEditedDate { get; set; }

    // User who performed last edit
    [ForeignKey("LastEditedByUser")]
    public string LastEditedByUserId { get; set; }
    public ApplicationUser LastEditedByUser { get; set; }

    // Submission date
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "Date Submitted")]
    public DateTime? SubmissionDate { get; set; }

    // User who submitted the record
    [ForeignKey("SubmittedByUser")]
    public string SubmittedByUserId { get; set; }
    public ApplicationUser SubmittedByUser { get; set; }
}

The ApplicationUser class is just the default class for users created by the Visual Studio ASP.NET MVC template.

I'm able to create Extensions and save them to the database just fine, but when I try to edit them I get a DbUpdateConcurrencyException. For example, the following code throws an exception without fail.

class Program
{
    static void Main(string[] args)
    {
        using (ApplicationDbContext db = new ApplicationDbContext())
        {
            Extension extension = new Extension
            {
                Details = "Details for a new extension.",
                DaysExtended = 25,
                ExtendedByUserId = db.Users.First().Id,
                RecordId = db.Records.First().Id,
                ExtensionDate = DateTime.Now
            };

            // create
            db.Extensions.Add(extension);
            db.SaveChanges(); // <-- Works

            // now edit
            extension.Details = "some new details";
            extension.DaysExtended = 40;

            db.SaveChanges(); // <-- Fails every time
        }
    }
}

Could someone please shed some light on why this may be happening? I know that the exception means that the entry has been modified since it was loaded into the context, but there is no reason that the record should have changed between the creation of the entry and the modification of the entry.

Please let me know if more information is required.

Update

Well, I was able to fix it...

The C# DateTime object and the "datetime" data type in SQL Server have compatibility issues. Somehow the DateTime stored in my Extension object is considered unequal to the "datetime" value stored in the database. This causes Entity Framework to throw the concurrency exception whenever I try to update an Extension record because it believes that the values have been changed since the entry was loaded.

In my case, there were two ways to fix this problem (I went with the latter, because it was more aligned with my original intentions).

1. Set the data type of the column used to represent the ExtensionDate property to "datetime2".
2. Set the data type of the column used to represent the ExtensionDate property to "date" and use only the Date when working with DateTime.

I'll post this as an answer, but I'd like to know the exact cause of the incompatibility before posting. Does anyone know why these two types are incompatible?

Answer 1

This is due to the value of DateTime.Now that is being stored in memory compared to the value that is being stored in SQL Server. datetime has a time range that can store between 00:00:00 through 23:59:59.997. datetime2 has a larger storage range between 00:00:00 through 23:59:59.9999999.