Cannot add an entity with a key that is already in use.

Question

 

We are passing a list of LINQ objects to a method as shown below.

I'm getting "Cannot add an entity with a key that is already in use." on the first Loan object in my List (array).

The loanId is NOT in my current SQL database.  The Loan Object is a complex object that includes about 5 other objects (for example one Loan has many Borrowers, and Borrower is another object and another table in the database). 

 

First, I want to find out which key for sure that LINQ is complaining about.

 

I was tempted to use SQL Profiler to debug, but then found the DataContext.Log property.
Based on what I have read, the code below SHOULD write the SQL out to the log file (but it does not).

 

The catch for System.Data.Linq.DuplicateKeyException is executing.

 

 

Code Snippet

public bool SaveLoans(List<Loan> loanList)

{

string loanId = "";

using (System.IO.TextWriter mySqlLog = System.IO.File.AppendText(@"c:\WCFLogs\log.sql"))

{

  mySqlLog.WriteLine("Start Debug at " + System.DateTime.Now);

  try

  {

   TMAC.BizTalk.ESB.Utility.Log.WriteEntry(this.GetType().ToString(), _guid, "0010", "Started Method SaveLoan", string.Empty, string.Empty);

   ESBEDSDataObjectDataContext dc = new ESBEDSDataObjectDataContext();

   dc.Log = mySqlLog;

   int loopCounter = 0;

   foreach (Loan loan in loanList)

   {

    loopCounter++;

    loanId = loan.CenlarLoanId;

    TMAC.BizTalk.ESB.Utility.Log.WriteEntry(this.GetType().ToString(), _guid, "0020", "Before foreach loop loanid=" + loanId, string.Empty, string.Empty);

    loanId = loan.CenlarLoanId;

    mySqlLog.Write("LoanId=" + loanId + " LoopCounter=" + loopCounter + "\r\n\r\n");

    dc.Loans.InsertOnSubmit(loan);

    TMAC.BizTalk.ESB.Utility.Log.WriteEntry(this.GetType().ToString(), _guid, "0030", "After foreach loop loanid=" + loanId, string.Empty, string.Empty);

    mySqlLog.Write("Before dc.SubmitChanges \r\n\r\n");

    dc.SubmitChanges();

    mySqlLog.Write("After dc.SubmitChanges \r\n\r\n");

}

}

catch (System.Data.SqlClient.SqlException ex)

{

string shortMsg = " for Loan=" + loanId;

TMAC.BizTalk.ESB.Utility.Log.WriteEntry(this.GetType().ToString(), _guid, "0040",

"SQLEX:" + shortMsg + " Error: " + ex.Message, string.Empty, string.Empty);

mySqlLog.Write("SQLEx" + ex.Message + "\r\n\r\n");

throw new Exception(ex.Message + shortMsg, ex);

}

catch (System.Data.Linq.DuplicateKeyException ex)

{

string shortMsg = " for Loan=" + loanId;

TMAC.BizTalk.ESB.Utility.Log.WriteEntry(this.GetType().ToString(), _guid, "0041",

"LinqDupKeyEX: " + shortMsg + " Error: " + ex.Message, string.Empty, string.Empty);

mySqlLog.Write("LinqDupKeyEx: " + ex.Message + "\r\n\r\n");

throw new Exception(ex.Message + shortMsg, ex);

}

catch (Exception ex)

{

string shortMsg = " for Loan=" + loanId;

TMAC.BizTalk.ESB.Utility.Log.WriteEntry(this.GetType().ToString(), _guid, "0042",

"EX:" + shortMsg + " Error: " + ex.Message, string.Empty, string.Empty);

mySqlLog.Write("Ex" + ex.Message + "\r\n\r\n");

throw new Exception(ex.Message + shortMsg, ex);

}

finally

{

mySqlLog.WriteLine("close()");

mySqlLog.Close(); // flush any unwritten data to file

}

}

return true;

}

 

 

 

The log file contains no SQL statements:

Code Snippet

Start Debug at 4/9/2008 2:29:12 PM
LoanId=0012340001 LoopCounter=1

Before dc.SubmitChanges

LinqDupKeyEx: Cannot add an entity with a key that is already in use.

close()

 

 

So why don't have SQL in my log?  How do I debug?

 

 

Thanks in advance!

Neal Walters

http://BizTalk-Training.com

 

 

 

 

 

 

 

Answer 1

You might try taking advantage of a class such as the one here: http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?ID=11

 

I've seen it linked from many locations, and it looks like it should work great. As for your initial problem, I have a guess at what might be throwing the exception, though I can't say for sure with only the piece of code you have shown. You say that each Loan has many Borrowers. Do you save these Borrowers separate from saving the Loans?

 

You might already know this, but just in case you didn't: Linq to Sql should know about any complex objects you might have, so if you tell it to insert a Loan, it will also insert (or update) any Borrowers that are tied to it. This should also work the other way around. I only say this as your issue could be due to you inserting the Borrowers separately from the Loan, rather than letting Linq to Sql take care of it.

 

In case this isn't the issue you are facing, go ahead and play with the debugger output, and let us know what else you find out.

 

[Edit]

I just realized that my description may not be entirely accurate. If you tied your objects together similar to the following, then it should work as I described:

borrower.Loan = loan;

 

However, if you only tied them together by Id, then you would likely still have to do separate inserts. For example, this:

borrower.LoanId = loan.Id;

Answer 2

LINQ to SQL complains with this error message when it tries to add the loan object to its internal identity cache which is keyed by the primary key of the object. For entities with PK's that are not generated by the database, insertion into the identity cache is done before any SQL is generated or sent to the database.  This is why you see the exception without seeing any SQL in the log. In order to get this exception there must already be an object keyed by that loan id in the internal cache. This usually happens when you've created more than one instance of an object and given it the same ID.

 

 

 

 

Answer 3

Hi there.

 

Your example it’s quite intricate but as I understand it, the best suggestion I give you is to remove the referential integrity checking from the Designer generates classes. Before SQL is sent to database the referential integrity constrains defined in the entities are checked. If they fail, no SQL is sent to the database. If you remove them, the fail will exist on the database but at least you can see the SQL sent and find what the problem is.

Hope it helps.

Answer 4

most likely you are trying to insert a duplicate key in DB, to be able to know which entity is causing the failure , strip all nested entities by assigning null values and first try to insert loans, if it passed, fill a loan with a nested object and try again, and so on until you find the one who is causing the problem.

Answer 5

Hi Mr.Neal!
                 I also faced the Same Problem while Updating the Database. I had Bind the "CompanyTable" in my  Dropdown list using   the 'List<CompanyTable>' Method. It said that the Same Error as u Faced. But i analysis and Find out it. Now i'm binding the CompanyCode and CompanyName in Another Metod to Bind in DropDownList. Now it is Working. So i think do't use List<Loan> Method.

Regards,
Er Suresh Ganesan.

Answer 6

dc = new ESBEDSDataObjectDataContext();
dc.Loans.InsertOnSubmit(loan);

Create an instance as i created above, it will work.