实体框架6: DbUpdateConcurrencyException

Question

我有创建/更新实体和“插入、更新触发器”的功能。这个错误发生在我正在创建新实体时，但是通过更新它就可以了，直接在数据库中添加也可以很好地处理这个触发器。

我在用：

• .NET框架4.5
• 实体框架6.1.3
• MS SQL Server Express 2016

存储update、insert或delete语句影响到意外的行数(0)。实体可能已被修改或删除，因为实体是加载的。有关理解和处理乐观并发异常的信息，请参见http://go.microsoft.com/fwlink/?LinkId=472540。

我已经尝试过的是：

1. catch (DbUpdateConcurrencyException ex) { var ctx = ((IObjectContextAdapter)db).ObjectContext；ctx.Refresh(RefreshMode.ClientWins，db.Plannings)；db.SaveChanges()；返回新SaveResult(true)；}
2. 有或不设NOCOUNT；

功能：

public SaveResult Save(PlanningSaveModel model)
    {
        using (var db = new RTPContext())
        {
            try
            {
                var entity = GetOrCreateEntity(db.Plannings, x => x.Id == model.Id).Item2;
                int totalDays = (model.EndDate.Value - model.StartDate.Value).Days + 1;
                if (totalDays <= 0)
                {
                    throw new Exception("Дата начала не может быть больше даты окончания");
                }
                entity.DocumentNumber = model.DocumentNumber;
                entity.DocumentEndDate = model.DocumentEndDate.Value;
                entity.UnitId = model.UnitId;
                entity.UnitMarkId = model.UnitMarkId;
                entity.Value = model.Value;
                entity.MeasureUnitId = model.MeasureUnitId;
                entity.StartDate = model.StartDate.Value;
                entity.EndDate = model.EndDate.Value;
                entity.CalendarWorkId = model.CalendarWorkId;
                //entity.PerDayValue = (int)Math.Round(Decimal.Divide(model.Value, totalDays));

                db.SaveChanges();
                return new SaveResult(true);
            }
            catch (Exception ex)
            {
                var errorMessage = ex.GetBaseException().Message;
                return new SaveResult(false, errorMessage);
            }
        }
    }

型号：

public abstract class PlanningModel
{
    [Key]
    public long? Id { get; set; }
    [Required]
    [Display(Name = "Document_Number", ResourceType = typeof(SharedStrings))]
    public string DocumentNumber { get; set; }
    [Required]
    [Display(Name = "Document_End_Date", ResourceType = typeof(SharedStrings))]
    public DateTime? DocumentEndDate { get; set; }
    [Required]
    [Display(Name = "Planning_Value", ResourceType = typeof(SharedStrings))]
    public int Value { get; set; }
    [Required]
    [Display(Name = "Date_Start", ResourceType = typeof(SharedStrings))]
    public DateTime? StartDate { get; set; }
    [Required]
    [Display(Name = "Date_End", ResourceType = typeof(SharedStrings))]
    public DateTime? EndDate { get; set; }
}

public class PlanningSaveModel : PlanningModel
{
    [Required]
    [Display(Name = "Unit_Name", ResourceType = typeof(SharedStrings))]
    public long UnitId { get; set; }
    [Required]
    [Display(Name = "Unit_Mark_Name", ResourceType = typeof(SharedStrings))]
    public long UnitMarkId { get; set; }
    [Required]
    [Display(Name = "Measure_Unit_Name", ResourceType = typeof(SharedStrings))]
    public long MeasureUnitId { get; set; }
    [Required]
    [Display(Name = "Calendar_Work_Name", ResourceType = typeof(SharedStrings))]
    public long CalendarWorkId { get; set; }
}

触发器：

ALTER TRIGGER [dbo].[CalculatePerDayValue]
ON  [dbo].[Planning]
INSTEAD OF INSERT, UPDATE
AS 
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF EXISTS(SELECT * FROM deleted) BEGIN
    UPDATE dbo.Planning
    SET DocumentNumber = i.DocumentNumber,
        DocumentEndDate = i.DocumentEndDate,
        UnitId = i.UnitId,
        UnitMarkId = i.UnitMarkId,
        MeasureUnitId = i.MeasureUnitId,
        [Value] = i.[Value],
        StartDate = i.StartDate,
        EndDate = i.EndDate,
        CalendarWorkId = i.CalendarWorkId,
        PerDayValue = dbo.WorkDays(i.StartDate, i.EndDate, i.CalendarWorkId, i.Value),
        ModifierId = i.ModifierId,
        ModifyDate = i.ModifyDate,
        IsDeleted = i.IsDeleted,
        HVersion = i.HVersion
    FROM inserted i
    WHERE dbo.Planning.Id = i.Id
END
ELSE BEGIN
    INSERT INTO dbo.Planning
    SELECT [DocumentNumber]
       ,[DocumentEndDate]
       ,[UnitId]
       ,[UnitMarkId]
       ,[MeasureUnitId]
       ,[Value]
       ,[StartDate]
       ,[EndDate]
       ,[CalendarWorkId]
       ,dbo.WorkDays(StartDate, EndDate, CalendarWorkId, Value)
       ,[ModifierId]
       ,[ModifyDate]
       ,[IsDeleted]
       ,[HVersion] FROM inserted
END
END

触发器中使用的标量函数：

ALTER FUNCTION [dbo].[WorkDays]
(
    @Start datetime,
    @End datetime,
    @CalendarId bigint,
    @Value int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @WorkDays int

-- Add the T-SQL statements to compute the return value here
SELECT @WorkDays = CASE @CalendarId WHEN 1 
        THEN
        (DATEDIFF(dd, @Start, @End) + 1) - (DATEDIFF(wk, @Start, @End) * 2)
            - (case when DATEPART(dw, @Start) IN (6, 7) then 1 else 0 end)
            - (case when DATEPART(dw, @End) IN (6, 7) then 1 else 0 end)
            - (select count(*) from dbo.CalendarHoliday where [Date] between @Start and @End and DATEPART(dw, [Date]) NOT IN (6, 7))
        ELSE
        (DATEDIFF(dd, @Start, @End) + 1)
            - (select count(*) from dbo.CalendarHoliday where [Date] between @Start and @End)
        END

-- Return the result of the function
RETURN @Value/@WorkDays

END

Answer 1

我在这里找到了一个答案：Entity Framework, view and instead of insert trigger. Can't insert a row into the view

刚将SELECT Id FROM dbo.Planning where @@ROWCOUNT > 0 AND Id = SCOPE_IDENTITY()添加到触发器中的insert语句中

所以触发器看起来是这样的：

ALTER TRIGGER [dbo].[CalculatePerDayValue]
ON  [dbo].[Planning]
INSTEAD OF INSERT, UPDATE
AS 
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF EXISTS(SELECT * FROM deleted) BEGIN
    UPDATE dbo.Planning
    SET DocumentNumber = i.DocumentNumber,
        DocumentEndDate = i.DocumentEndDate,
        UnitId = i.UnitId,
        UnitMarkId = i.UnitMarkId,
        MeasureUnitId = i.MeasureUnitId,
        [Value] = i.[Value],
        StartDate = i.StartDate,
        EndDate = i.EndDate,
        CalendarWorkId = i.CalendarWorkId,
        PerDayValue = dbo.WorkDays(i.StartDate, i.EndDate, i.CalendarWorkId, i.Value),
        ModifierId = i.ModifierId,
        ModifyDate = i.ModifyDate,
        IsDeleted = i.IsDeleted,
        HVersion = i.HVersion
    FROM inserted i
    WHERE dbo.Planning.Id = i.Id
END
ELSE BEGIN
    INSERT INTO dbo.Planning
    SELECT [DocumentNumber]
       ,[DocumentEndDate]
       ,[UnitId]
       ,[UnitMarkId]
       ,[MeasureUnitId]
       ,[Value]
       ,[StartDate]
       ,[EndDate]
       ,[CalendarWorkId]
       ,dbo.WorkDays(StartDate, EndDate, CalendarWorkId, Value)
       ,[ModifierId]
       ,[ModifyDate]
       ,[IsDeleted]
       ,[HVersion] FROM inserted

    -- FOR ENTITY FRAMEWORK CONCURRENCY EXCEPTION HANDLING
    SELECT Id FROM dbo.Planning where @@ROWCOUNT > 0 AND Id = SCOPE_IDENTITY()
END
END

Answer 2

我犯了这个错误，因为我试图插入到数据库中的表没有主键。为表添加了主键，为我修复了此异常。我认为您也有同样的问题，因为您的计划表没有主键，这就是为什么您的数据模型有以下几行：

 [Key]
 public long? Id { get; set; }