locked
Error with delete then insert RRS feed

  • Question

  • Hi

     

    When i delete from a table with a composite key and then re-insert a row with the same composite key, i get a duplicate key exception. If i insert, close the program and then insert, it works just fine. I can see that the value has been deleted from the database when i expect it to be, but there must be some remnant in the LINQ-objects.

     

    Any workaround? Or am i missing something?

     

    /Michael

    Thursday, May 10, 2007 7:04 AM

Answers

  • Okay, you've convinced us.  We're changing the behavior to allow you to insert new entities with the same key as ones you are deleting or have deleted already.  No need to toss out the DataContext and start over.
    Friday, May 18, 2007 5:42 PM

All replies

  • When does the exception happen? When you insert entity to the entity set or when you submit changes?

    Both ways it sounds like LINQ to SQL didn't dispose the key from its internal list.

    Thursday, May 10, 2007 8:06 AM
  • It is when i call SubmitChanges(). And yes, i agree it sounds like LINQ to SQL doesn't dispose of the key in a proper way.
    Thursday, May 10, 2007 8:14 AM
  • I would expect the exception to happen when you insert the entity into collection if the problem is within LINQ to SQL, not when submiting changes. Can you check the sql statement generated?

    Also, are you sure that entity was really deleted from database? What is exception call stack?

    Thursday, May 10, 2007 8:23 AM
  • I'm at work right now, i'll look at the code when i get back.

     

    But it is fairly simple, it's just a simple table representing a many-to-many relation with no other columns than the 2 keys. What i do is, run it in debug, run the delete statement, check that it is really gone with query analyzer and then run the insert statement.

    Thursday, May 10, 2007 9:50 AM
  • This is actually the designed behavior.  Deleting an object does not remove knowledge of it from the DataContext.  If you try to re-use the same key in the same context you are going to get an exception.
    Thursday, May 10, 2007 3:13 PM
  • Why this behaviour (why SubmitChanges doesn't remove removed keys?)? And why does it happen when SubmitChanges is invoked - IOW why doesn't happen on Add method?
    Thursday, May 10, 2007 5:48 PM
  • If this is by design, then how are we supposed to use it? I'm using it in a windows app and in that i have a form that attaches artists to a band, you can add and remove them from the band (read: database). Should we create/destroy the database object for each form and submit our data on exit from the program/form? What about persisting through a crash? Web applications?

     

    EDIT:

    I don't get it about the datacontext, what exact datacontext are we talking about? How do i refresh it?

    Thursday, May 10, 2007 6:18 PM
  • The DataContext manages the object's identity through the data context's lifetime. When you retrieve an object from the data context, the context caches the returned value and returns it. In subsequent requests, the context checks the identity of the object coming back from the database and compares it against the cached version. If the cache already contains an object with that identity, it returns the cached version. This way, changes that the user has made but not submitted back to the database will be reflected as the user continues to request the item in question.

    When the changes are submitted to the database, the change log is flushed, but the object cache is not. Items still flagged for deletion will be retained in the cache as well. When you add another item using the same identity key, you will cause the conflict which will be resolved on the next submit changes.

    Because of the caching and the memory footprint that it takes, the DataContext is NOT intended to be long lived. It should not extend past the life of an ASP form for instance. In that case, you would leverage the .Attach method to re-attach a submitted record to an existing DataContext's entity set. In a winform application, you may want to dispose the current datacontext when you finish submit changes and then start again with a new one to keep your footprint smaller.

     

    Jim Wooley

    http://linqinaction.net

    http://devauthority.com/blogs/jwooley

    Thursday, May 10, 2007 9:39 PM
  • Wooley, I guess you ment that the DataContent is not supposed to be long lived?! .. Just curious whether I missed something. However, short lived DataContexts go well together with quick instantiation times (which I seriously hope will be manifested in the next CTP / beta as well as the other performance improvements mentioned by the team).
    Thursday, May 10, 2007 10:00 PM
  • Anders. Correct, I fixed my post. Thanks for catching it.

     

    Jim

    Friday, May 11, 2007 2:00 AM
  • Just for being the idiot wanting things spelt out 7 times:

     

    I should make a:

     

    MyDataBase database = new MyDatabase();

     

    in each of the functions that delete from the database? It just seems like a massive overhead... And similarly for each asp.net page?

     

    Just to have it carved in stone, then what is the very best way of designing a function that is to be called at random from a windows appliaction that sends deletes and inserts to a database?

     

    void DeleteItem()

    {

    //initialize - how much?

    //what should we keep as global/static for the app, just the connectionstring?

     

    //deletion, nevermind this

     

    SubmitChanges();

     

    //anything else?

    }

    Friday, May 11, 2007 6:00 AM
  • I disagree. Why would I want to dispose DataContext and reload everything everytime? Why would I waste time? And at the end, what's the difference? I have a DataContext with some entities, I dispose it after save and I reload the same entities again. So I have a version of "to be disposed" entities and a new version in memory. How makes that more effective? Actually it doubles the memory required (in worst case, for short time) and it requires roundtrip(s) to database - which can cost me very much.
    Friday, May 11, 2007 8:19 AM
  • I'll just bump this. Can it be that noone is able to supply me with a template for such an exceedingly simple function??

     

    //preBig Smileelete a row from the database

    //post: be completely in sync with the database after

    void DeleteItem()

    {

    //initialize - how much?

    //what should we keep as global/static for the app, just the connectionstring?

     

    //deletion, nevermind this

     

    SubmitChanges();

     

    //anything else?

    }

    Tuesday, May 15, 2007 1:16 PM
  • Okay, you've convinced us.  We're changing the behavior to allow you to insert new entities with the same key as ones you are deleting or have deleted already.  No need to toss out the DataContext and start over.
    Friday, May 18, 2007 5:42 PM
  • Ok, now I'm REALLY frightened...

     

    I have to decide which persistance technology we will use in our further company's product.

    I'm benchmarking different technologies, I chose DLINQ, LINQ to EF, db4o, NHibernate...

     

    But It seems that EF project is not achieved enough, even to be used in a "non commercial" benchmark, as implementation and specifications go on changing. So how could I compared a behaviour yet ?

     

    Please, could you tell me if EF is achieved enough to forecast its use in a professional application ?

    Development would start in about 3 months.

     

    Thank you

    Stephanie

    Thursday, July 26, 2007 9:21 AM
  • Matt, could you please elaborate on the change (and state whether the change will make it to Beta 2)?

     

    I guess the primary question revolves around whether the DataContext disposes deleted entries or not. Regardless, please provide your feedback for reference.

     

    Thanks in advance (and thanks for taking the feedback to change the API).

    Thursday, July 26, 2007 1:44 PM
  • Strange.

    As of today (20 July 2009) I still can not find a way of inserting deleted records with compposite key.

    am I missing something?
    Monday, July 20, 2009 6:02 PM