none
How to avoid DuplicateKeyException? RRS feed

  • Question

  • I have two tables Article and Availability:

    Article                 Availability
    ----------------    -----------------
    ID                     ArticleID        
    Name                Qty
    TimeStamp        TimeStamp
        
          
    I want to update existing Availability without first fetching it from the database.
    So I create new Availability entity, set Qty and TimeStamp properties and set the Article relation.
    I prefer to use Article property instead of ArticleID.

    Here is my code:

    1            using (DB.DefaultDataContext db = new DB.DefaultDataContext()) 
    2            { 
    3                DB.Article art = (from a in db.Articles 
    4                                  where a.ID == 28 
    5                                  select a).SingleOrDefault(); 
    6 
    7                DB.Availability avail = new Minibar.DB.Availability(); 
    8                avail.Qty = 2; 
    9                avail.TimeStamp = new byte[8]; 
    10 
    11                 //on this line it fetches Availability with ArticleID = 28 in another entity 
    12                avail.Article = art; 
    13 
    14                //trows DuplicateKeyException - Cannot add an entity with a key that is already in use 
    15                db.Availabilities.Attach(avail, true); 
    16            } 


    But when I set that relation between Article and Availability, Linq2Sql fetches the Availability from database.
    And when I attach the Availability I get DuplicateKeyException.
    If I use ArticleID instead of Article property it works fine but I need to work with entities only.

    How to avoid this error?
    Tuesday, February 24, 2009 11:25 AM

Answers

  • Hi tchimev, no problem.

    You are right that the ID = 0 in the case of new entities, but if you are trying to update an existing Availability record and set its Article property to an Article that already exists in the database, then that Article will have an ID, correct? You will need to set the ID for LINQ to SQL to recognize which Article to associate with that Availability. Even if you set the entity, LINQ to SQL will use the ID of that entity to determine how to make the relationship between the Article and Availability.

    I hope this makes sense. Please let me know if you have any further questions.
    • Marked as answer by tchimev Wednesday, March 4, 2009 2:21 PM
    Wednesday, March 4, 2009 1:59 PM
    Answerer

All replies

  • First of all, why do you not want to fetch the record from the db and just update it?  It makes more sense.  Anyways, you could try using Merge() instead of Attach().
    Daniel - http://webs.neumont.edu/dstafford
    Tuesday, February 24, 2009 3:53 PM
  • But when I use Merge() I have to fetch the original object from the database.

    EDIT:
    I do not want to know what is in the database, I just want to change it.
    Wednesday, February 25, 2009 7:09 AM
  • True, as a last resort, you could always just run a direct command against the db.
    Daniel - http://webs.neumont.edu/dstafford
    Wednesday, February 25, 2009 8:22 PM
  • LINQ to SQL does support this scenario in the case where you don't want to make another round trip to the DB to update an object. The steps are as follows.

    1. Create an instance of the entity you want to update. (e.g. var avail = new Minibar.Db.Availability();)
    2. Set the properties that LINQ to SQL would use to identify this entity in the database. This includes primary key columns and TimeStamp columns. Note that the values have to be what they are in the database; otherwise the update will fail with a ChangeConflictException that says "Row not found or changed." This makes sense because LINQ to SQL can't find the row you want to update.
    3. Attach the entity to the appropriate ITable. (e.g. db.Availabilities.Attach(avail)).
    4. Now set all the properties that you want to be updated. (e.g. avail.Article = article).
    5. Call DataContext.SubmitChanges.

    Putting everything together...

    using (DefaultDataContext db = new DefaultDataContext())  
    {  
        // Step 1: Create the entity instance  
        Availability avail = new Availability();  
     
        // Step 2: Set the identification columns  
        avail.ID = 5;  
        avail.TimeStamp = new byte[] { 0, 0, 0, 0, 0, 0, 0xD, 0x71 };  
     
        // Step 3: Attach to the ITable  
        db.Availabilities.Attach(avail); // Same as .Attach(avail, false);
     
        // Step 4: Update properties for the UPDATE statement  
        avail.Qty = 60;  
        avail.Article = article; // Assume article is of type Article  
     
        // Step 5: Update  
        db.SubmitChanges();  

    The final thing to keep in mind is Attach(avail, true) would actually tell LINQ to SQL that every property (besides the ID and TimeStamp) has been updated. In this case you could actually set the Qty and Article properties before attaching and achieve the same result. However, if the Availability entity has additional properties that haven't changed, then those would still be updated when using Attach(avail, true).

    Hope this helps.
    Wednesday, February 25, 2009 10:54 PM
    Answerer
  • Thank you @David DeWinter for your sample.

    In step 2 of your code did you mean set the avail.ArticleID,
    like avail.ArticleID = art.ID

    I do not have ID column in my Availability entity.

    When I comment out avail.ID = 5, I get an InvalidOpertaionException:

    'An attempt was made to remove a relationship between a Article and a Availability. However, one of the relationship's foreign keys (Availability.ArticleID) cannot be set to null.'

    When I use IDs to set the relation between Article and Availability there is no fetch from the database, but if I use objects for that relation it has to fetch. Could you explain that?

    Thank you once again.

    EDIT:
    In step 2 if I replace 'avail.ID = 5' with 'avail.ArticleID = art.ID', I'm able to submit without errors and fetches, which is good
    But I still do not like to use IDs.
    Thursday, February 26, 2009 7:24 AM
  • For that part of step 2, I meant the property (or properties) that represent your Availability entity's primary key, so there I assumed you had an AvailabilityId property.

    Note that the actual Article reference (not ArticleId) is being set in step 4, after the Attach.

    When I use IDs to set the relation between Article and Availability there is no fetch from the database, but if I use objects for that relation it has to fetch. Could you explain that?

    I would have to see your code to make sure, but based on your first code example, there is a fetch from the database because of the LINQ query that selects the article with ID 28 from the database. Does that answer your question, or did you mean something else?

    * Before I was assuming that Availability's primary key was not Article ID. If, in fact, the Availability's primary key is the Article ID, then you will have to do something a little different for your attach:

    using (DefaultDataContext db = new DefaultDataContext())  
    {  
        Availability avail = new Availability();  
     
        avail.Article = new Article() { articleId = 1 };  
        avail.Timestamp = new byte[] { 0, 0, 0, 0, 0, 0, 0x7, 0xD1 };  
     
        db.Availabilities.Attach(avail);  
     
        avail.Qty = 40;  
     
        db.SubmitChanges();  


    As you can see above, I have to set the Availability.Article property to be a new Article instance whose primary key properties have been set. The DuplicateKeyException would occur if you tried to reference an Article that was already attached to the DataContext.

    May I ask why you do not want to set the ID properties directly?

    Thursday, February 26, 2009 2:04 PM
    Answerer
  • Hi @David DeWinter, sorry for my late replay.

    I do not want to use IDs directly because IDs are not always unique.
    If I create two new entities they are with ID = 0 before I save them to DB.

    I still can not find a way without using IDs.
    But you point me to a way to use both IDs and entities, so may be I will use that.

    Wednesday, March 4, 2009 9:35 AM
  • Hi tchimev, no problem.

    You are right that the ID = 0 in the case of new entities, but if you are trying to update an existing Availability record and set its Article property to an Article that already exists in the database, then that Article will have an ID, correct? You will need to set the ID for LINQ to SQL to recognize which Article to associate with that Availability. Even if you set the entity, LINQ to SQL will use the ID of that entity to determine how to make the relationship between the Article and Availability.

    I hope this makes sense. Please let me know if you have any further questions.
    • Marked as answer by tchimev Wednesday, March 4, 2009 2:21 PM
    Wednesday, March 4, 2009 1:59 PM
    Answerer
  • Yes, I think that make sense.

    Thank you for your help.
    Wednesday, March 4, 2009 2:22 PM