How Can I Fix DbUpdateConcurrencyException In My New .Net Core Project?

Question

I am building a new .Net Core 2.2 website at work and have tried a few different approaches, but I am receiving errors when using the Edit functionality after setting up the CRUD model. Initially I started with a Database first approach and then received the DbUpdateConcurrencyException when trying to edit an item. I assumed there was something wrong with my DB or table so started a new project creating the database from the model and context in the fresh project.

Environment:

• MacOS Mojave
• Visual Studio for Mac Community 8.2.3 (build 16)
• SQL Server Database
• .Net Core 2.2
• C#

A few months ago I built another site with the same environment that didn't do this.

Steps To Create:

• dotnet new webapp -o TestSite
• cd TestSite/
• dotnet add package Microsoft.EntityFrameworkCore.Design --version 2.2.6
• dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 2.2.6
• dotnet add package Microsoft.EntityFrameworkCore.Tools --version 2.2.6
• dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design --version 2.2.3

Check the site runs okay

• dotnet run

Create model and context

Fileset.cs

using System;
using System.ComponentModel.DataAnnotations;

namespace TestSite.Models
{
    public class Fileset
    {
        public long Id { get; set; }
        public string Ticket { get; set; }
        public string Requester { get; set; }

        [Display(Name = "Research Centre")]
        public string ResearchCentre { get; set; }

        [Display(Name = "Project ID")]
        public string ProjectId { get; set; }

        [Display(Name = "Title")]
        public string ProjectTitle { get; set; }

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

        [Display(Name = "Internal ID")]
        public string InternalId { get; set; }

        public string Email { get; set; }

        [Display(Name = "Start Date")]
        public DateTime StartDate { get; set; }

        [Display(Name = "End Date")]
        public DateTime EndDate { get; set; }

        [Display(Name = "Quota (GB)")]
        public long Quota { get; set; }

        [Display(Name = "UNC Path")]
        public string StoragePath { get; set; }

        [Display(Name = "AD Group")]
        public string Adgroup { get; set; }

        [Timestamp]
        public byte[] RowVersion { get; set; }
    }
}

TestSiteContext.cs

using System;
using Microsoft.EntityFrameworkCore;

namespace TestSite.Data
{
    public class TestSiteContext : DbContext
    {
        public TestSiteContext (DbContextOptions<TestSiteContext> options) : base(options)
        {
        }

        public DbSet<Models.Fileset> Fileset { get; set; }
    }
}

Update Startup.cs to include Connection String reference for DB

services.AddDbContext<Data.TestSiteContext>(options => options.UseSqlServer(Configuration.GetConnectionString("TestDB")));

Add Connection String to appsettings.json

"ConnectionStrings": {
    "TestDB": "Server=server;Database=database;Integrated Security=SSPI;Connection Timeout=15"
}

Scaffold the model

• dotnet aspnet-codegenerator razorpage -m Fileset -dc TestSite.Data.TestSiteContext -udl -outDir Pages/Filesets --referenceScriptLibraries

Initial migration

• dotnet ef migrations add InitialCreate
• dotnet ef database update

Confirmed the database had the new table and correct schema and no errors were reported on creation in the dotnet cli output

• dotnet run

Navigated to https://localhost:5001/Filesets on kestrel server, created a new item and confirmed it appeared in the SQL database

• Tried the 'Edit' page for that item*

Error

DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(int commandIndex, int expectedRowsAffected, int rowsAffected)
Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithPropagationAsync(int commandIndex, RelationalDataReader reader, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple<IEnumerable<ModificationCommandBatch>, IRelationalConnection> parameters, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync<TState, TResult>(TState state, Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList<InternalEntityEntry> entriesToSave, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken)
TestSite.Pages.Filesets.EditModel.OnPostAsync() in Edit.cshtml.cs
-
            }
            _context.Attach(Fileset).State = EntityState.Modified;
            try
            {
                await _context.SaveChangesAsync(); // Error line
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!FilesetExists(Fileset.Id))
                {
                    return NotFound();
Microsoft.AspNetCore.Mvc.RazorPages.Internal.ExecutorFactory+GenericTaskHandlerMethod.Convert<T>(object taskAsObject)
Microsoft.AspNetCore.Mvc.RazorPages.Internal.ExecutorFactory+GenericTaskHandlerMethod.Execute(object receiver, object[] arguments)
Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker.InvokeHandlerMethodAsync()
Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker.InvokeNextPageFilterAsync()
Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker.Rethrow(PageHandlerExecutedContext context)
Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker.InvokeInnerFilterAsync()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Routing.EndpointRoutingMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

I am relatively new to this, but when following the tutorials or when building my first site on version 2.2, this did not happen. I have done no other steps than those stated above. I should not be receiving concurrency errors for something I alone am testing unless I purposefully did it i.e. Open a page to edit it, edit the item on a second page and then return to try and edit the first page.

Can anyone help? Have I done something stupid? The primary key and row version values are in my model and show correctly in my SQL DB design.

Please let me know if you need more information.

Update

Added the code for the Edit.cshtml.cs as requested.

public async Task<IActionResult> OnPostAsync()
{
    if (!ModelState.IsValid)
    {
        return Page();
    }

    _context.Attach(Fileset).State = EntityState.Modified;

    try
    {
        await _context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        if (!FilesetExists(Fileset.Id))
        {
            return NotFound();
        }
        else
        {
            throw;
        }
    }

    return RedirectToPage("./Index");
}

I have not amended it yet, so should be as default, created by the scaffolding process.

Update 2

After Neil's answer and comments below, I have updated my OnPostAsync method.

[BindProperty]
public Fileset Fileset { get; set; }
public SelectList FilesetSL { get; set; }

public async Task<IActionResult> OnPostAsync(int id)
{
    if (!ModelState.IsValid)
    {
        return Page();
    }

    _context.Attach(Fileset).State = EntityState.Modified;

    var filesetToUpdate = await _context.Fileset
        .FirstOrDefaultAsync(m => m.Id == id);

    if (filesetToUpdate == null)
    {
        return HandleDeletedFileset();
    }

    // Update the RowVersion to the value when this entity was
    // fetched. If the entity has been updated after it was
    // fetched, RowVersion won't match the DB RowVersion and
    // a DbUpdateConcurrencyException is thrown.
    // A second postback will make them match, unless a new 
    // concurrency issue happens.
    _context.Entry(filesetToUpdate)
        .Property("RowVersion").OriginalValue = Fileset.RowVersion;

    if (await TryUpdateModelAsync<Fileset>(
        filesetToUpdate, "Fileset", f => f.Ticket, f => f.Requester, f => f.ResearchCentre, f => f.ProjectId, f => f.ProjectTitle, f => f.Name, f => f.InternalId,
        f => f.Email, f => f.StartDate, f => f.EndDate, f => f.Quota, f => f.StoragePath, f => f.Adgroup))
    {
        try
        {
            await _context.SaveChangesAsync();
            return RedirectToPage("./Index");
        }
        catch (DbUpdateConcurrencyException ex)
        {
            var exceptionEntry = ex.Entries.Single();
            var clientValues = (Fileset)exceptionEntry.Entity;
            var databaseEntry = exceptionEntry.GetDatabaseValues();

            if (databaseEntry == null)
            {
                ModelState.AddModelError(string.Empty, "Unable to save. " + "The fileset was deleted by another user");
                return Page();
            }

            var dbValues = (Fileset)databaseEntry.ToObject();
            await SetDbErrorMessage(dbValues, clientValues, _context);

            // Save the current RowVersion so next postback
            // matches unless an new concurrency issue happens.
            Fileset.RowVersion = (byte[])dbValues.RowVersion;
            // Must clear the model error for the next postback.
            ModelState.Remove("Fileset.RowVersion");
        }
    }

    FilesetSL = new SelectList(_context.Fileset, "ID", "Project ID", filesetToUpdate.ProjectId);
    return Page();
}

private IActionResult HandleDeletedFileset()
{
    var fileset = new Fileset();
    // ModelState contains the posted data because of the deletion error and will overide the Department instance values when displaying Page().
    ModelState.AddModelError(string.Empty,
        "Unable to save. The Fileset was deleted by another user.");
    FilesetSL = new SelectList(_context.Fileset, "ID", "Project ID", fileset.ProjectId);
    return Page();
}

private async Task SetDbErrorMessage(Fileset dbValues, Fileset clientValues, TestSiteContext context)
{
    if (dbValues.Ticket != clientValues.Ticket)
    {
        ModelState.AddModelError("Fileset.Ticket",
            $"Current value: {dbValues.Ticket}");
    }
    if (dbValues.Requester != clientValues.Requester)
    {
        ModelState.AddModelError("Fileset.Requester",
            $"Current value: {dbValues.Requester}");
    }
    if (dbValues.ResearchCentre != clientValues.ResearchCentre)
    {
        ModelState.AddModelError("Fileset.ResearchCentre",
            $"Current value: {dbValues.ResearchCentre}");
    }
    if (dbValues.ProjectId != clientValues.ProjectId)
    {
        ModelState.AddModelError("Fileset.ProjectId",
            $"Current value: {dbValues.ProjectId}");
    }
    if (dbValues.ProjectTitle != clientValues.ProjectTitle)
    {
        ModelState.AddModelError("Fileset.ProjectTitle",
            $"Current value: {dbValues.ProjectTitle}");
    }
    if (dbValues.Name != clientValues.Name)
    {
        ModelState.AddModelError("Fileset.Name",
            $"Current value: {dbValues.Name}");
    }
    if (dbValues.InternalId != clientValues.InternalId)
    {
        ModelState.AddModelError("Fileset.InternalId",
            $"Current value: {dbValues.InternalId}");
    }
    if (dbValues.Email != clientValues.Email)
    {
        ModelState.AddModelError("Fileset.Email",
            $"Current value: {dbValues.Email}");
    }
    if (dbValues.StartDate != clientValues.StartDate)
    {
        ModelState.AddModelError("Fileset.StartDate",
            $"Current value: {dbValues.StartDate}");
    }
    if (dbValues.EndDate != clientValues.EndDate)
    {
        ModelState.AddModelError("Fileset.EndDate",
            $"Current value: {dbValues.EndDate}");
    }
    if (dbValues.Quota != clientValues.Quota)
    {
        ModelState.AddModelError("Fileset.Quota",
            $"Current value: {dbValues.Quota}");
    }
    if (dbValues.StoragePath != clientValues.StoragePath)
    {
        ModelState.AddModelError("Fileset.StoragePath",
            $"Current value: {dbValues.StoragePath}");
    }
    if (dbValues.Adgroup != clientValues.Adgroup)
    {
        Fileset fileset = await context.Fileset
            .FindAsync(dbValues.Adgroup);
        ModelState.AddModelError("Fileset.Adgroup",
            $"Current value: {fileset?.Adgroup}");
    }

    ModelState.AddModelError(string.Empty,
        "The record you attempted to edit "
        + "was modified by another user after you. The "
        + "edit operation was canceled and the current values in the database "
        + "have been displayed. If you still want to edit this record, click "
        + "the Save button again.");
}

Which results in:

The record you attempted to edit was modified by another user after you. The edit operation was canceled and the current values in the database have been displayed. If you still want to edit this record, click the Save button again.

I can create and delete records without any problems, but the Edit keeps on hitting concurrency problems. Even the second postback does not work.

Loriannlorianna

Which tutorials have you been following? There is generally no need to set the state of entities, it's all done automatically. – Adaline

Answer 1

The problem was that the RowVersion was not referenced in the Edit.cshtml file.

<input type="hidden" asp-for="Fileset.RowVersion" />

Handling Concurrency Conflicts

Loriannlorianna

Answer 2

If that's really what the scaffolding created, it's a really bad example. Try loading a record, modifying it and then saving it.

public async Task<IActionResult> OnPostAsync()
{
    if (!ModelState.IsValid)
    {
        return Page();
    }

    var record = _context.Fileset.Find(id); // <-- id should be part of the POST
    record.Ticket = "Some random text";

    try
    {
        await _context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        if (!FilesetExists(Fileset.Id))
        {
            return NotFound();
        }
        else
        {
            throw;
        }
    }

    return RedirectToPage("./Index");
}

Adaline

The text I have added updates the 'Ticket' field without error, but no other field is modified. I have done nothing other than the steps in my post, so it is what the scaffolding created and I can see it on other projects too. – Loriannlorianna

It's impossible for the scaffolding to know what you actually want to do, so the developers who wrote the scaffolding script have decided to do something that will always error, to force you into changing it. – Adaline

I will look at what I added to the other site and try that out, I think I modified it from here. By the way, I had followed the tutorial from here. – Loriannlorianna

I've updated my OnPostAsync() to be similar to another site that works, I'm still hitting the error, it's just being handled now. No one has access to the DB or the site, so it can't have been modified. Updated the code above. – Loriannlorianna