LINQ to SQL ERROR using Transaction：违反PRIMARY KEY约束'PK_EmployeeTerritories'。无法在对象'dbo.EmployeeTerritories'中插入重复键。该语句已终止 [英] LINQ to SQL ERROR using Transaction: Violation of PRIMARY KEY constraint 'PK_EmployeeTerritories'. Cannot insert duplicate key in object 'dbo.EmployeeTerritories'.The statement has been terminated
问题描述
我在事务中使用LINQ to SQL时遇到了一个奇怪的问题。
重现此行为的步骤：
1. 插入一些记录
2.修改现有的记录
3.在执行SubmitChanges之前（ ） System.Data.Linq.ConflictMode.ContinueOnConflict），在外部应用程序（例如sqlserver管理工作室）上修改上述记录
4。现在SubmitChanges（System.Data.Linq.ConflictMode.ContinueOnConflict）引发冲突错误
5。解决conflit
6。做另一个SubmitChanges（System.Data.Linq.ConflictMode.FailOnFirstConflict）
7。在这里你有错误：违反PRIMARY KEY约束'PK_EmployeeTerritories'。无法在对象'dbo.EmployeeTerritories'中插入重复键。该语句已被终止。
这是c＃ 代码重现此行为，此代码使用NORTHWND数据库：
static void Main（string [] args）
{
using（Northwnd db = new Northwnd（） ）
{
// ====================================== $================================================
//恢复上一个状态========================================== ====================
// ======================== ================================================== =============
EmployeeTerritory exitEt =（来自db.EmployeeTerritories中的et，其中et.EmployeeID == 5&& et.TerritoryID ==" 85014" select et） .FirstOrDefault（）;
if（exitEt！= null）
db.EmployeeTerritories.DeleteOnSubmit（exitEt）;
var existingRegToReset =（来自db.Regions中的er，其中er.RegionID == 2选择er）.First（）;
existingRegToReset.RegionDescription =" Western" ;;
db.SubmitChanges（System.Data.Linq.ConflictMode.FailOnFirstConflict）;
// ============================================ ===========================================
//恢复以前的状态================================================ ==============
// ============================== ================================================== =======
EmployeeTerritory newEt = new EmployeeTerritory（）;
newEt.EmployeeID = 5;
newEt.TerritoryID =" 85014" ;;
db.EmployeeTerritories.InsertOnSubmit（newEt）;
订购newO = new Order（）;
newO.CustomerID =" WHITC" ;;
newO.EmployeeID = 5;
newO.OrderDate = new DateTime（DateTime.Now.Year，DateTime.Now.Month，DateTime.Now.Day）;
newO.RequiredDate = new DateTime（DateTime.Now.Year，DateTime.Now.Month，DateTime.Now.Day）;
newO.ShippedDate = new DateTime（DateTime.Now.Year，DateTime.Now.Month，DateTime.Now.Day）;
newO.ShipVia = 1;
newO.Freight =（十进制）4.56;
newO.ShipName =" White Clover Markets" ;;
newO.ShipAddress =" 1029 - 12th Ave. S.英寸;
newO.ShipCity =" Seattle" ;;
newO.ShipRegion =" WA" ;;
newO.ShipPostalCode =" 98124" ;;
newO.ShipCountry =" USA" ;;
db.Orders.InsertOnSubmit（newO）;
var existingReg =（来自db.Regions中的er，其中er.RegionID == 2选择er）.First（）;
existingReg.RegionDescription =" Western1" ;;
Console.WriteLine（"使用Management Studio在数据库上提交以下更新：'更新dbo.Region设置RegionDescription ='Western2'，其中RegionID = 2'"）;
Console.ReadLine（）;
ChangeSet cs;
使用（TransactionScope ts = new TransactionScope（TransactionScopeOption.Required，TimeSpan.FromSeconds（0）））
{
try
{
try
{
cs = db.GetChangeSet（）;
db.SubmitChanges（System.Data.Linq.ConflictMode.ContinueOnConflict）;
}
catch（ChangeConflictException e）
{
Console.WriteLine（e.Message）;
//客户端
//未修改的成员的Automerge数据库值。
foreach（db.ChangeConflicts中的ObjectChangeConflict occ）
occ.Resolve（RefreshMode.KeepChanges）;
cs = db.GetChangeSet（）;
}
//第二次尝试提交成功。
db.SubmitChanges（ConflictMode.FailOnFirstConflict）;
}
catch（Exception ex）
{
throw;
}
}
cs = db.GetChangeSet（）;
}
}
我们需要进行交易才能完成当前数据库中的某些其他操作。
感谢您的帮助
Fabio
FabioBo
您好FabioBo，
感谢您发布此处。
根据您的问题与ADO.NET更相关，我将其移至
LINQ
to SQL 论坛以获得合适的支持。
CLR论坛讨论并询问有关.NET Framework基类（BCL）的问题，例如集合，I / O，Regigistry，Globalization，Reflection。还讨论所有
其他构建在.NET Framework上或扩展.NET Framework的Microsoft库，包括Managed Extensibility Framework（MEF），Charting Controls，CardSpace，Windows Identity Foundation（WIF），Point of Sale（POS），Transactions。
最好的问候，
温迪
Hi, i've had a strange problem using LINQ to SQL inside a transaction.
Step to reproduce this behaviour:
1. insert a couple of record
2. modify an existing record
3. before to executing SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict), modify the above record on an external application (ex. sqlserver management studio)
4. now SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict) raise conflict error
5. resolve conflit
6. do another SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict)
7. here you have the error: Violation of PRIMARY KEY constraint 'PK_EmployeeTerritories'. Cannot insert duplicate key in object 'dbo.EmployeeTerritories'.The statement has been terminated.
Here is c# code to reproduce this behaviour, this code is using NORTHWND database:
static void Main(string[] args)
{
using (Northwnd db = new Northwnd())
{
//=======================================================================================
// RESTORE PREVIOUS STATUS ==============================================================
//=======================================================================================
EmployeeTerritory exitEt = (from et in db.EmployeeTerritories where et.EmployeeID == 5 && et.TerritoryID == "85014" select et).FirstOrDefault();
if (exitEt != null)
db.EmployeeTerritories.DeleteOnSubmit(exitEt);
var existingRegToReset = (from er in db.Regions where er.RegionID == 2 select er).First();
existingRegToReset.RegionDescription = "Western";
db.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);
//=======================================================================================
// RESTORE PREVIOUS STATUS ==============================================================
//=======================================================================================
EmployeeTerritory newEt = new EmployeeTerritory();
newEt.EmployeeID = 5;
newEt.TerritoryID = "85014";
db.EmployeeTerritories.InsertOnSubmit(newEt);
Order newO = new Order();
newO.CustomerID = "WHITC";
newO.EmployeeID = 5;
newO.OrderDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
newO.RequiredDate= new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
newO.ShippedDate= new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
newO.ShipVia = 1;
newO.Freight = (Decimal)4.56;
newO.ShipName = "White Clover Markets";
newO.ShipAddress = "1029 - 12th Ave. S.";
newO.ShipCity = "Seattle";
newO.ShipRegion = "WA";
newO.ShipPostalCode = "98124";
newO.ShipCountry = "USA";
db.Orders.InsertOnSubmit(newO);
var existingReg = (from er in db.Regions where er.RegionID == 2 select er).First();
existingReg.RegionDescription = "Western1";
Console.WriteLine("Submit the following update on database using Management Studio:'Update dbo.Region Set RegionDescription='Western2' Where RegionID=2'");
Console.ReadLine();
ChangeSet cs;
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(0)))
{
try
{
try
{
cs = db.GetChangeSet();
db.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException e)
{
Console.WriteLine(e.Message);
// Automerge database values for members that client
// has not modified.
foreach (ObjectChangeConflict occ in db.ChangeConflicts)
occ.Resolve(RefreshMode.KeepChanges);
cs = db.GetChangeSet();
}
// Submit succeeds on second try.
db.SubmitChanges(ConflictMode.FailOnFirstConflict);
}
catch (Exception ex)
{
throw;
}
}
cs = db.GetChangeSet();
}
}
We need transaction in order to complete some other operations out of the current DB.
Thank you for your help
Fabio
FabioBo
Hi FabioBo,
Thank you for posting here.
According to your question is more related to ADO.NET, I will move it to LINQ to SQL forum for suitable support.
The CLR Forum discuss and ask questions about .NET Framework Base Classes (BCL) such as Collections, I/O, Regigistry, Globalization, Reflection. Also discuss all the other Microsoft libraries that are built on or extend the .NET Framework, including Managed Extensibility Framework (MEF), Charting Controls, CardSpace, Windows Identity Foundation (WIF), Point of Sale (POS), Transactions.
Best Regards,
Wendy
这篇关于LINQ to SQL ERROR using Transaction：违反PRIMARY KEY约束'PK_EmployeeTerritories'。无法在对象'dbo.EmployeeTerritories'中插入重复键。该语句已终止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
