移除多条数据时如何修复DbUpdateConcurrencyException异常

问题详情 #

执行数据删除操作时遇到如下异常：

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 30 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.

相关代码如下：

using MySqlConnector;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata.Conventions;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using MySql.EntityFrameworkCore;

namespace Flanium_Agent
{
public class AgentContext : DbContext
{
    public DbSet<Agent> agent { get; set; }
    public DbSet<AgentRequest> agentRequest { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseMySQL("Server=localhost;User ID=root;Database=orchestration_db");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Agent>().HasKey(a => a.Station);
        modelBuilder.Entity<Agent>().ToTable("agents");
        modelBuilder.Entity<AgentRequest>().HasKey(a => a.Station);
        modelBuilder.Entity<AgentRequest>().ToTable("agent_requests");
    }
}

public class Agent
{
    public string Station { get; set; }
    public string Process { get; set; }
    public string Actions { get; set; }
    public string Started { get; set; }
    public string Finished { get; set; }
    public string Status { get; set; }
}

public class AgentRequest
{
    public string Station { get; set; }
    public string Request { get; set; }
}

public class Agency : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged = delegate { };
    private bool ProcessRunning = false;
    private List<string> RequestList = new List<string>();
    public string Station { get; set; }
    public string Process { get; set; }
    public string Actions { get; set; }
    public string Started { get; set; }
    public string Finished { get; set; }
    public string Status { get; set; }
    public string Request { get; set; }
    
    private Grid DisplayGrid { get; set; }
    private Window appWindow { get; set; }

    public Grid GetDisplayGrid()
    {
        return DisplayGrid;
    }

    public Agency InsertAgentToGrid(Window mainWindow)
    {
        DisplayGrid = new Grid();

        var headerRowDefinition = new RowDefinition();
        headerRowDefinition.Height = new GridLength(50);
        DisplayGrid.RowDefinitions.Add(headerRowDefinition);

        for (var index = 0; index < GetType().GetProperties().Length; index++)
        {
            var property = GetType().GetProperties()[index];
            var header = new TextBlock();
            var column = new ColumnDefinition();
            header.Text = property.Name;
            header.HorizontalAlignment = HorizontalAlignment.Center;
            header.VerticalAlignment = VerticalAlignment.Center;
            header.FontSize = 16;
            header.FontWeight = FontWeights.Medium;
            Grid.SetRow(header, 0);
            Grid.SetColumn(header, index);

            DisplayGrid.ColumnDefinitions.Add(column);
            DisplayGrid.Children.Add(header);
        }

        var contentRowDefinition = new RowDefinition();
        contentRowDefinition.Height = GridLength.Auto;
        DisplayGrid.RowDefinitions.Add(contentRowDefinition);

        for (var index = 0; index < GetType().GetProperties().Length; index++)
        {
            var propertyValue = GetType().GetProperties()[index];
            var content = new TextBlock();

            content.HorizontalAlignment = HorizontalAlignment.Center;
            content.VerticalAlignment = VerticalAlignment.Center;
            content.FontSize = 12;
            content.TextWrapping = TextWrapping.Wrap;
            Grid.SetRow(content, 1);
            Grid.SetColumn(content, index);
            DisplayGrid.Children.Add(content);

            var myBinding = new Binding(propertyValue.Name)
            {
                Source = this,
                Mode = BindingMode.TwoWay,
                UpdateSourceTrigger = UpdateSourceTrigger.PropertyChanged
            };
            content.SetBinding(TextBlock.TextProperty, myBinding);
        }

        DisplayGrid.HorizontalAlignment = HorizontalAlignment.Stretch;
        DisplayGrid.VerticalAlignment = VerticalAlignment.Top;
        DisplayGrid.Margin = new Thickness(0, 25, 0, 0);
        (mainWindow.Content as Grid).Children.Add(DisplayGrid);
        appWindow = mainWindow;


        return this;
    }


    private string[] GetPackages()
    {
        var packagesArray = new List<string>();

        var desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
        var pathString = Path.Combine(desktopPath, "Flanium Agent Data");
        var packageFolders = Directory.GetDirectories(pathString);
        foreach (var packFolder in packageFolders)
        {
            var versions = Directory.GetDirectories(packFolder);
            if (versions.Length != 0)
            {
                packagesArray.AddRange(versions.Select(version => version.Split('\\').Last())
                    .Select(versionName => packFolder.Split('\\').Last() + "\\" + versionName));
                packagesArray = packagesArray.Select(x => x = pathString + "\\" + x).ToList();
            }
        }

        return packagesArray.ToArray();
    }

    public Agency(string station, string process, string actions, string started, string finished, string status)
    {
        try
        {
            using (var context = new AgentContext())
            {
                context.agent.Add(new Agent
                    {Station = station, Process = process, Actions = actions, Started = started, Finished = finished, Status = status});
                // Saves changes
                context.SaveChanges();
            }
            
            Station = station;
            Process = process;
            Actions = actions;
            Started = started;
            Finished = finished;
            Status = status;
        }
        catch (Exception e)
        {
            
        }
    }

    public void RemoveAgent()
    {
        using (var context = new AgentContext())
        {
            context.agent.Where(x=> x.Station == Station).ToList().ForEach(x=> context.agent.Remove(x));
            // Saves changes
            context.SaveChanges();
        }
        
    }
}
}

问题原因 #

你把Agent实体的Station字段设为主键，但数据库中Station并不是唯一值——执行Where(x=>x.Station == Station)时查出了30条记录。EF Core默认认为主键是唯一标识，执行删除操作时会预期影响行数等于匹配的实体数，而当实际影响行数和预期不符时，就会触发这个并发异常。

解决方案 #

方案1：修正主键（推荐） #

如果Station本就不该作为唯一主键，给实体添加自增唯一主键：

1. 修改Agent实体，新增Id字段：

public class Agent
{
    public int Id { get; set; } // 新增自增主键
    public string Station { get; set; }
    public string Process { get; set; }
    public string Actions { get; set; }
    public string Started { get; set; }
    public string Finished { get; set; }
    public string Status { get; set; }
}

2. 在AgentContext的OnModelCreating中配置主键和自增：

modelBuilder.Entity<Agent>()
    .HasKey(a => a.Id)
    .Property(a => a.Id)
    .ValueGeneratedOnAdd(); // 设置自增
modelBuilder.Entity<Agent>().ToTable("agents");

3. 更新数据库结构：执行EF迁移命令（Add-Migration AddAgentId，再Update-Database）。
4. 修改删除逻辑，通过唯一Id删除单条记录：

// 修改Agency类的RemoveAgent方法，传入agent的Id
public void RemoveAgent(int agentId)
{
    using (var context = new AgentContext())
    {
        var agentToRemove = context.agent.Find(agentId);
        if (agentToRemove != null)
        {
            context.agent.Remove(agentToRemove);
            context.SaveChanges();
        }
    }
}

方案2：保留Station作为主键（需保证唯一性） #

如果业务上Station必须是主键，先确保数据库中Station字段有唯一约束，然后可以选择以下两种方式处理：

• 方式一：直接执行SQL删除，绕过EF并发检查

public void RemoveAgent()
{
    using (var context = new AgentContext())
    {
        context.Database.ExecuteSqlRaw("DELETE FROM agents WHERE Station = {0}", Station);
    }
}

• 方式二：捕获并发异常并处理

public void RemoveAgent()
{
    using (var context = new AgentContext())
    {
        var agentsToRemove = context.agent.Where(x => x.Station == Station).ToList();
        foreach (var agent in agentsToRemove)
        {
            context.agent.Remove(agent);
        }
        try
        {
            context.SaveChanges();
        }
        catch (DbUpdateConcurrencyException ex)
        {
            // 重新加载数据后重试
            foreach (var entry in ex.Entries)
            {
                entry.Reload();
            }
            context.SaveChanges();
        }
    }
}

内容的提问来源于stack exchange，提问作者Avrigeanu Laurian