I have function to create/update entity and "instead insert, update trigger". This error occurs when I'm creating new entity, but with updating it's OK and adding directly in database also works perfectly with this trigger.
I'm using:
- .NET Framework 4.5
- Entity Framework 6.1.3
- MS SQL Server Express 2016
Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.
What I already tried:
catch (DbUpdateConcurrencyException ex) { var ctx = ((IObjectContextAdapter)db).ObjectContext; ctx.Refresh(RefreshMode.ClientWins, db.Plannings); db.SaveChanges(); return new SaveResult(true); }With and without SET NOCOUNT ON;
Function:
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);
}
}
}
Model:
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; }
}
Trigger:
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
Scalar function used in trigger:
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
