Error: The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges.
The below mentioned code runs successfully when used without stored procedures but fails on using stored procedures for saving and updating the user entity.
public string Index()
{
string s = "";
EFWithSP context = new EFWithSP();
try
{
context.Configuration.AutoDetectChangesEnabled = false;
User user01 = new User()
{
FirstName = "abhi" + DateTime.Now.ToString("yyyyMMddhhmmss"),
LastName = "mith" + DateTime.Now.ToString("yyyyMMddhhmmss"),
};
Console.WriteLine("adding user {0},{1}", user01.LastName, user01.FirstName);
context.Users.Add(user01);
context.ChangeTracker.DetectChanges();
context.SaveChanges();
s += DisplayEntityInfo(user01, "added user01");
Thread.Sleep(1000);
User user02 = new User()
{
FirstName = "abhi" + DateTime.Now.ToString("yyyyMMddhhmmss"),
LastName = "mith" + DateTime.Now.ToString("yyyyMMddhhmmss"),
};
Console.WriteLine("adding user {0},{1}", user02.LastName, user02.FirstName);
context.Users.Add(user02);
context.ChangeTracker.DetectChanges();
context.SaveChanges();
s += DisplayEntityInfo(user02, "added user02");
User aUser01 = context.Users.First(u => u.FirstName == user01.FirstName);
User bUser01 = context.Users.First(u => u.FirstName == user01.FirstName);
if (aUser01 != null)
{
aUser01.FirstName = "01" + aUser01.FirstName;
}
context.ChangeTracker.DetectChanges();
context.SaveChanges();
s += DisplayEntityInfo(aUser01, "updated aUser01");
if (bUser01 != null)
{
bUser01.FirstName = "02" + aUser01.FirstName;
}
context.ChangeTracker.DetectChanges();
context.SaveChanges();
s += DisplayEntityInfo(bUser01, "updated bUser01");
}
catch (Exception ex)
{
s += ex.Message;
if (null != ex.InnerException)
s += ex.InnerException.Message;
}
return s;
}
private string DisplayEntityInfo(User user, string note)
{
return string.Format("{0} ||| {1} ||| {2}<br>", user.GetHashCode(), user.UserID, note);
}
sql code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UsersAuditLog](
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[UserID] [int] NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UserUpdate]
@UserID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Timestamp timestamp
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
IF NOT EXISTS(SELECT 1 FROM dbo.Users where UserID = @UserID AND [Timestamp] = @Timestamp)
BEGIN
UPDATE
dbo.Users
SET
FirstName = @FirstName,
LastName = @LastName
WHERE
UserID = @UserID
END
ELSE
BEGIN
declare @errormsg varchar(max)
select @errormsg = 'Record for user ' + @FirstName + ' ' + @LastName + ' modified by other user. Please select the record again.'
RAISERROR (@errormsg, 16, 1)
END
END TRY
BEGIN CATCH
declare @ErMessage varchar(max)
declare @ErSeverity int
declare @ErState int
SELECT
@ErMessage = ERROR_MESSAGE(),
@ErSeverity = 17,
@ErState = ERROR_STATE()
RAISERROR (@ErMessage, @ErSeverity, @ErState)
END CATCH
SELECT * FROM dbo.Users where UserID = @UserID
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UserDelete]
@UserID int
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM dbo.Users where UserID = @UserID
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UserAdd]
@FirstName nvarchar(50),
@LastName nvarchar(50)
AS
BEGIN
declare @UserID int
SET NOCOUNT ON;
BEGIN TRY
IF NOT EXISTS(SELECT 1 FROM dbo.Users where FirstName = @FirstName)
BEGIN
INSERT INTO dbo.Users(FirstName, LastName)
VALUES (@FirstName, @LastName)
SELECT
@UserID = SCOPE_IDENTITY()
END
ELSE
BEGIN
declare @errormsg varchar(max)
select @errormsg = 'FirstName ' + @FirstName + ' already exists. Please select a different username.'
RAISERROR (@errormsg, 16, 1)
END
END TRY
BEGIN CATCH
declare @ErMessage varchar(max)
declare @ErSeverity int
declare @ErState int
SELECT
@ErMessage = ERROR_MESSAGE(),
@ErSeverity = ERROR_SEVERITY(),
@ErState = ERROR_STATE()
RAISERROR (@ErMessage, @ErSeverity, @ErState)
END CATCH
SELECT * FROM dbo.Users WHERE UserID = @UserID
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TUserDelete]
ON [dbo].[Users]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
UPDATE
dbo.Users
SET
FirstName = FirstName
WHERE
UserID = (select UserID from deleted)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TUserAddUpdate]
ON [dbo].[Users]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.UsersAuditLog(UserID, FirstName, LastName)
SELECT
UserID, FirstName, LastName
FROM
inserted
END
GO