none
The database produced a key that is already in use. RRS feed

  • Question

  • Hello everyone, I got a problem with LINQ to SQL. Im  trying to add some related data like:

     

    //creating some simple piece of data

    for (int i = 0; i < 3; i++)

    {

    //adding content object

    BookChapterContent rootContent = new BookChapterContent();

    rootContent.HTMLContent = "root " + i + " content";

    db.BookChapterContents.InsertOnSubmit(rootContent);

    BookChapter root = new BookChapter();

    root.ChapterContentID = rootContent.ChapterContentID; //ID of added content above

    root.OwnerID = 1;

    root.ParentChapterID = null;

    root.Title = "root " + i;

    db.BookChapters.InsertOnSubmit(root);

     

    [...]

    }

     

    db.SubmitChanges();

     

    and when SubmitChanges() executes i'm getting an error:

     

    {"The database generated a key that is already in use."} System.Exception {System.Data.Linq.DuplicateKeyException}

     

    StackTrace "   at System.Data.Linq.ChangeProcessor.PostProcessUpdates(List`1 insertedItems, List`1 deletedItems)\r\n   at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)\r\n   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)\r\n   at System.Data.Linq.DataContext.SubmitChanges()\r\n   at PrattWhitney.Web.DefaultPage.Page_Load(Object sender, EventArgs e) in D:\\workshop\\PrattWhitney\\!svn\\current code\\PrattWhitney\\Default.aspx.cs:line 41\r\n   at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)\r\n   at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)\r\n   at System.Web.UI.Control.OnLoad(EventArgs e)\r\n   at System.Web.UI.Control.LoadRecursive()\r\n   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)" string

    (I have set autoincrement to all keys in DB)

     

    Do anyone have any solution? I will be grateful for any response. Thanks.

    Tuesday, October 7, 2008 10:19 PM

All replies

  • Actually I found a bit strange workaround here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1975634&SiteID=1 , but now, after executing this code:

     

    using (PWDataContext db = new PWDataContext())

    {

    //creating some simple piece of data

    for (int i = 0; i < 3; i++)

    {

    //adding content object

    BookChapterContent rootContent = new BookChapterContent();

    rootContent.HTMLContent = "root " + i + " content";

    db.BookChapterContents.InsertOnSubmit(rootContent);

    BookChapter root = new BookChapter();

    root.ChapterContentID = rootContent.ChapterContentID; //ID of added content above

    root.OwnerID = 1;

    root.ParentChapterID = null;

    root.Title = "root " + i;

    db.BookChapters.InsertOnSubmit(root);

    }

    db.SubmitChanges();

    }

     

    the objects in BookChapterContents are inserted correctly, but the BookChapters table are empty, and no exception occurs[!]

    can somebody tell me what's going on here?

    Tuesday, October 7, 2008 11:20 PM
  • The problem is highlighted below:

     

    using (PWDataContext db = new PWDataContext())

    {

    //creating some simple piece of data

    for (int i = 0; i < 3; i++)

    {

    //adding content object

    BookChapterContent rootContent = new BookChapterContent();

    rootContent.HTMLContent = "root " + i + " content";

    db.BookChapterContents.InsertOnSubmit(rootContent);

    BookChapter root = new BookChapter();

    root.ChapterContentID = rootContent.ChapterContentID; //ID of added content above

    root.OwnerID = 1;

    root.ParentChapterID = null;

    root.Title = "root " + i;

    db.BookChapters.InsertOnSubmit(root);

    }

    db.SubmitChanges();

    }

     

    You seem to be attempting to relate two object's together using ID's that have not been generated yet.  The InsertOnSubmit function does not actually send an INSERT command to the database.  It just queues one up to be sent when you call SubmitChanges. 

     

    In order for you to properly submit a bunch of object's at the same time that are related, you must relate them using relationship properties, not ID's.  The relationship properties are pointers/collections between objects (not the ID's in the database) and allows L2S to order the inserts correctly and fill-in the ID's after they have been generated.

     

     

    Tuesday, October 7, 2008 11:55 PM
    Moderator
  • thanks for quick response. I changed all foreign ID's to properties:

     

    using (PWDataContext db = new PWDataContext())

    {

    User owner = db.Users.First();

    //creating some simple piece of data

    for (int i = 0; i < 3; i++)

    {

    //adding content object

    BookChapterContent rootContent = new BookChapterContent();

    rootContent.HTMLContent = "root " + i + " content";

    db.BookChapterContents.InsertOnSubmit(rootContent);

     

    BookChapter root = new BookChapter();

    //root.ChapterContentID = rootContent.ChapterContentID; //ID of added content above

    root.BookChapterContent = rootContent;

    root.User = owner;

    //root.ParentChapterID = null;

    root.ParentChapter = null;

    root.Title = "root " + i;

    db.BookChapters.InsertOnSubmit(root);

    }

     

    db.SubmitChanges();

     

     

     

    but still the BookChapters table is empty after SubmitChanges, and no exception occurs...

     

    Wednesday, October 8, 2008 8:57 AM
  •  

    guys? It still doesnt work... Anybody can help me and tell why to one table data is inserted and to the other NOT ???

    Wednesday, October 8, 2008 3:11 PM