none
Update as opposed to insert. RRS feed

  • Question

  • I have a simple LINQ query that inserts a row into a table in my database:

                    orderContext.OrderForecasts.InsertOnSubmit(row);
                    orderContext.SubmitChanges();
    

    But if the row already exists this fails with a SQL exception indicating that I have violated the PK constraints, which is true.

    I have two questions. One how do I detect that the row already exists (Efficiently. I don't want to end up with one query to tell me if the row already exists and another to update the row)? And two how do I update rather than insert?

    Friday, June 5, 2009 6:33 PM

Answers

  • Hi,

    Damien is right, you either need two calls or a single call to an sp. Here's another quick method to insert/update incoming data:
    int id = row.ForeCastID;
    
    bool exists = orderContext.OrderForecasts.Any(o => o.ForecastID == id);
    if (!exists)
    {
      //insert
      orderContext.OrderForecasts.InsertOnSubmit(row);
    }
    else
    {
      //attach as modified
      orderContext.OrderForecasts.Attach(row, true);
    }
    orderContext.SubmitChanges();
    
    

    Hope that helps,

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Saturday, June 6, 2009 11:55 AM
  • Hi Kevin,

    I am afraid things aren't going to be simple for this scenario. The Attach() method has three overloads:

    • Attach( dataObject, true ) needs a timestamp column so you wont to be able to use this.
    • Attach ( dataObject ) needs original (unmodified) dataobject followed by the desired modifications, something like:

    //attach the original object
    dbContext.dataTable.Attach ( orgObj );
    
    //make the modifications
    orgObj.Field1 = 'updated value1'
    orgObj.Field2 = 'updated value2'
    orgObj.Field3 = 'updated value3'
    
    //submit changes
    dbContext.SubmitChanges();
    

    • Attach ( modifiedDataObject, originalDataObject ) is a nice overload but I am not sure if you have the original(unchanged) object or not.


    If you only have the modified object, here's a workaround I could think of (Beware, it will violate conflict resolution in optimistic concurrency):
    • In the LINQ designer, set UpdateCheck =Never for all your fields except the primary one.
    • Use the third overload with only primaryKey filled for the originalDataObject. Something like:

    //incoming modified data object
    DataObject modified;
    
    //create a new object (with only primary key fields filled) to be used as original object
    DataObject original = new DataObject();
    original.ID = modified.ID;
    
    //now attach both the objects and commit
    dbContext.DataTable.Attach( modified, original );
    dbContext.Commit();
    
    Let me know if this works for you.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Tuesday, June 16, 2009 8:59 AM

All replies

  • You have to retrieve the row already there and make modifications to it.

    e.g.

    int orderForecastID = 1;
    OrderForecasts current = orderContext.OrderForecasts.SingleOrDefault(o => o.ForecastID = orderForecastID);
    if (current == null)
    {
    current = CreateForecast(orderForecastID);
    orderContext.OrderForecasts.InsertOnSubmit(orderForecastID);
    }
    else
    {
    current.CheckedDate = DateTime.Now; // or whatever other modification
    }
    orderContext.SubmitChanges();

    The only other option would be to write a stored procedure that takes the values and either updates/inserts and wrap it in a method using LINQ to SQL.

    [)amien
    Saturday, June 6, 2009 2:58 AM
    Moderator
  • Hi,

    Damien is right, you either need two calls or a single call to an sp. Here's another quick method to insert/update incoming data:
    int id = row.ForeCastID;
    
    bool exists = orderContext.OrderForecasts.Any(o => o.ForecastID == id);
    if (!exists)
    {
      //insert
      orderContext.OrderForecasts.InsertOnSubmit(row);
    }
    else
    {
      //attach as modified
      orderContext.OrderForecasts.Attach(row, true);
    }
    orderContext.SubmitChanges();
    
    

    Hope that helps,

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Saturday, June 6, 2009 11:55 AM
  • I tried this code and I get the following InvalidOperation exception:

    An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.

    The debugger is pointing to:

                    {
                        //attach as modified
                        orderContext.OrderForecasts.Attach(row, true);
                    }
    

     

    Any ides as to what this means?

    Kevin

    Saturday, June 13, 2009 12:56 PM
  • I think LINQ could not generate an appropriate SQL query for the update operation. Do you have a timestamp column in your table?

    Update: Have a look at this MSDN document, the Attach(object, true) needs atleast one column in your LINQ generated classes with RowVersion property as true (this one is typically a TimeStamp column).

    Also, have a look at this and this .

    Hope that helps.




    Syed Mehroz Alam
    My Blog | My Articles
    • Edited by Syed Mehroz Alam Monday, June 15, 2009 9:54 AM adde more detail and a few external links
    Monday, June 15, 2009 9:46 AM
  • This generates another question (I hate when that happens).

    Can I get away with not specifying the boolean (true/false) or doing Attach(xxx, false)? I guess I don't understand the consequences of Attach(,true). Since I don't care (I only care that I don't insert a duplicate primary key and get the resulting exception) is there a way to modify an existing row irrespective of the timestamp/version?

    Thank you.

    Kevin

    Monday, June 15, 2009 4:37 PM
  • Hi Kevin,

    I am afraid things aren't going to be simple for this scenario. The Attach() method has three overloads:

    • Attach( dataObject, true ) needs a timestamp column so you wont to be able to use this.
    • Attach ( dataObject ) needs original (unmodified) dataobject followed by the desired modifications, something like:

    //attach the original object
    dbContext.dataTable.Attach ( orgObj );
    
    //make the modifications
    orgObj.Field1 = 'updated value1'
    orgObj.Field2 = 'updated value2'
    orgObj.Field3 = 'updated value3'
    
    //submit changes
    dbContext.SubmitChanges();
    

    • Attach ( modifiedDataObject, originalDataObject ) is a nice overload but I am not sure if you have the original(unchanged) object or not.


    If you only have the modified object, here's a workaround I could think of (Beware, it will violate conflict resolution in optimistic concurrency):
    • In the LINQ designer, set UpdateCheck =Never for all your fields except the primary one.
    • Use the third overload with only primaryKey filled for the originalDataObject. Something like:

    //incoming modified data object
    DataObject modified;
    
    //create a new object (with only primary key fields filled) to be used as original object
    DataObject original = new DataObject();
    original.ID = modified.ID;
    
    //now attach both the objects and commit
    dbContext.DataTable.Attach( modified, original );
    dbContext.Commit();
    
    Let me know if this works for you.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Tuesday, June 16, 2009 8:59 AM
  • Thank you for the detail. I understand it better now. For my purposes I think I will go with the Attach(object) solution. But it is nice to put the 'modified only' solution into my bag of tricks.

    THanks again.

    Kevin
    Tuesday, June 16, 2009 2:17 PM