none
Insert or modify. RRS feed

  • Question

  • I am trying the following:

                using (OrderClassesDataContext orderContext = new OrderClassesDataContext())
                {
                    bool exists = orderContext.VendorOrderForecasts.Any(o => vendor == o.Vendor);
                    VendorOrderForecast row = null;
                    if (!exists)
                    {
                        //insert
                        row = new VendorOrderForecast();
                        row.Vendor = vendor;
                        row.Model = model;
                        row.Created = created;
                        row.ModelDetail = XElement.Parse(modeldetail);
                        orderContext.VendorOrderForecasts.InsertOnSubmit(row);
                    }
                    else
                    {
                        //attach then modify
                        orderContext.VendorOrderForecasts.Attach(row);
                        row.Model = model;
                        row.Created = created;
                        row.ModelDetail = XElement.Parse(modeldetail);
                    }
                    orderContext.SubmitChanges();
                }
    

    And iseems to work just fine in the 'insert' case (when there are no existing rows). But if I just want to modify an existing row the 'Attach' doesn't work.  Would someone be so kind as to show me what I am missing?

    Thank you.

    Kevin
    Monday, September 21, 2009 2:47 PM

Answers

  • Hi Kevin,

     

    Damien is correct.   Before attaching an object, the primary key of the object should be generated first.  And if the concurrency check is set, you can follow the Updating Data section of this MSDN documentation: Data Retrieval and CUD Operations in N-Tier Applications (LINQ to SQL).  

     

    For the UpdateCheck property, in O/R designer you can check it by seeing the properties window of each columns in the data class.  Also, for detailed information about LINQ to SQL optimistic concurrency, please see http://msdn.microsoft.com/en-us/library/bb399373.aspx. 

     

    If the VendorOrderForecase object does not involves the concurrency check (UpdateCheck of each column is None), your codes can be modified as the following:
    ========================================================================
                               using (OrderClassesDataContext orderContext = new OrderClassesDataContext())

    ·                                                                                                                                                              {

    ·                                                                                                                                                                  bool exists = orderContext.VendorOrderForecasts.Any(o => vendor == o.Vendor);

    ·                                                                                                                                                                  VendorOrderForecast row = new VendorOrderForecast();

    ·                                                                                                                                                                  if (!exists)

    ·                                                                                                                                                                  {

    ·                                                                                                                                                                      //insert

    ·                                                                                                                                                                      row.Vendor = vendor;

    ·                                                                                                                                                                      row.Model = model;

    ·                                                                                                                                                                      row.Created = created;

    ·                                                                                                                                                                      row.ModelDetail = XElement.Parse(modeldetail);

    ·                                                                                                                                                                      orderContext.VendorOrderForecasts.InsertOnSubmit(row);

    ·                                                                                                                                                                  }

    ·                                                                                                                                                                  else

    ·                                                                                                                                                                  {

    ·                                                                                                                                                                      //attach then modify

    ·                                                                                                                                                                      row.PKColumn = …;

    ·                                                                                                                                                                      orderContext.VendorOrderForecasts.Attach(row, true);

    ·                                                                                                                                                                      row.Model = model;

    ·                                                                                                                                                                      row.Created = created;

    ·                                                                                                                                                                      row.ModelDetail = XElement.Parse(modeldetail);

    ·                                                                                                                                                                  }

    ·                                                                                                                                                                  orderContext.SubmitChanges();

    ·                                  }
    ==================================================================================================================================


    Please note:
    We use the Attach methods with entities that have been created in one DataContext, serialized to a client, and then deserialized back with the intention to perform an update or delete operation.  Is your scenario similar with this?   Otherwise, I don’t think using Attach is a good option. 

     

    You may directly retrieve the objects and modify them without using Attach. 

     

    If you have only one object to modify, we can use the SingleOrDefault method.
    ========================================================================
                               using (OrderClassesDataContext orderContext = new OrderClassesDataContext())

    ·                                                                                                                                                              {

    ·                                                                                                                                                                  VendorOrderForecast row = orderContext.VendorOrderForecasts.

    ·                                                                                                                                                                                                SingleOrDefault(o => o.Vendor = vendor);

    ·                                                                                                                                                                  if (row == null)

    ·                                                                                                                                                                  {

    ·                                                                                                                                                                      //insert

    ·                                                                                                                                                                      row = new VendorOrderForecast();

    ·                                                                                                                                                                      row.Vendor = vendor;

    ·                                                                                                                                                                      row.Model = model;

    ·                                                                                                                                                                      row.Created = created;

    ·                                                                                                                                                                      row.ModelDetail = XElement.Parse(modeldetail);

    ·                                                                                                                                                                      orderContext.VendorOrderForecasts.InsertOnSubmit(row);

    ·                                                                                                                                                                  }

    ·                                                                                                                                                                  else

    ·                                                                                                                                                                  {

    ·                                                                                                                                                                      row.Model = model;

    ·                                                                                                                                                                      row.Created = created;

    ·                                                                                                                                                                      row.ModelDetail = XElement.Parse(modeldetail);

    ·                                                                                                                                                                  }

    ·                                                                                                                                                                  orderContext.SubmitChanges();

    ·                                  }       

    ========================================================================


    Or you want to modify multiple objects,

    ========================================================================
                           using (OrderClassesDataContext orderContext = new OrderClassesDataContext())

                            {

                                var rows = orderContext.VendorOrderForecasts.

                                                              Where(o => o.Vendor = vendor);

                                if (rows.Count() == 0)

                                {

                                    //insert

                                    VendorOrderForecast row = new VendorOrderForecast();

                                    row.Vendor = vendor;

                                    row.Model = model;

                                    row.Created = created;

                                    row.ModelDetail = XElement.Parse(modeldetail);

                                    orderContext.VendorOrderForecasts.InsertOnSubmit(row);

                                }

                                else

                                {

                                    foreach (var row in rows)

                                    {

                                        row.Model = model;

                                        row.Created = created;

                                        row.ModelDetail = XElement.Parse(modeldetail);

                                    }

                                }

                                orderContext.SubmitChanges();

                           }

    ========================================================================

    If you have any questions, please feel free to let me know.

     

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KevinBurton Tuesday, September 22, 2009 12:47 PM
    Tuesday, September 22, 2009 2:34 AM
    Moderator
  • Hi Kevin,

     

    I can reproduced the exception “Row not found or changed” if the entity UpdateCheck property is set.  When updating an entity, LINQ to SQL generates such a SQL command with the UpdateCheck columns:

    =====================================================================
    UPDATE [dbo].[Person]

    SET [PersonName] = @p2

    WHERE ([PersonID] = @p0) AND ([PersonName] = @p1)
    =====================================================================

     

    The PersonID and PersonName column are set to UpdateCheck.Always.  So LINQ to SQL will update the data record where PersonID and PersonName equal to the original values.  In your codes, the original record is not retrieved from the database, so LINQ to SQL do not know the original column values to build the correct SQL command.  The incorrect SQL command which raise the “Row not found or changed” exception can be this one:

    =====================================================================
    UPDATE [dbo].[Person]

    SET [PersonName] = @p1

    WHERE ([PersonID] = @p0) AND ([PersonName] IS NULL)
    =====================================================================

    Here are some additional references:

    http://dotnetslackers.com/Community/blogs/bmains/archive/2009/07/27/row-not-found-or-changed-linq-to-sql-error.aspx


    Thus, if all the columns are set to UpdateCheck.Never, your codes will work fine. 

     

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, September 23, 2009 8:26 AM
    Moderator

All replies

  • You need to set the primary key and if your model uses concurrency check (updatecheck set to anything other than none or a rowversion field) then you'll need to set that too before the attach.

    [)amien
    Monday, September 21, 2009 3:17 PM
    Moderator
  • I am unfamiliar with concurrency check. Is UpdateCheck a property on the SQL context?

    Thank you.

    Kevin
    Monday, September 21, 2009 3:27 PM
  • Hi Kevin,

     

    Damien is correct.   Before attaching an object, the primary key of the object should be generated first.  And if the concurrency check is set, you can follow the Updating Data section of this MSDN documentation: Data Retrieval and CUD Operations in N-Tier Applications (LINQ to SQL).  

     

    For the UpdateCheck property, in O/R designer you can check it by seeing the properties window of each columns in the data class.  Also, for detailed information about LINQ to SQL optimistic concurrency, please see http://msdn.microsoft.com/en-us/library/bb399373.aspx. 

     

    If the VendorOrderForecase object does not involves the concurrency check (UpdateCheck of each column is None), your codes can be modified as the following:
    ========================================================================
                               using (OrderClassesDataContext orderContext = new OrderClassesDataContext())

    ·                                                                                                                                                              {

    ·                                                                                                                                                                  bool exists = orderContext.VendorOrderForecasts.Any(o => vendor == o.Vendor);

    ·                                                                                                                                                                  VendorOrderForecast row = new VendorOrderForecast();

    ·                                                                                                                                                                  if (!exists)

    ·                                                                                                                                                                  {

    ·                                                                                                                                                                      //insert

    ·                                                                                                                                                                      row.Vendor = vendor;

    ·                                                                                                                                                                      row.Model = model;

    ·                                                                                                                                                                      row.Created = created;

    ·                                                                                                                                                                      row.ModelDetail = XElement.Parse(modeldetail);

    ·                                                                                                                                                                      orderContext.VendorOrderForecasts.InsertOnSubmit(row);

    ·                                                                                                                                                                  }

    ·                                                                                                                                                                  else

    ·                                                                                                                                                                  {

    ·                                                                                                                                                                      //attach then modify

    ·                                                                                                                                                                      row.PKColumn = …;

    ·                                                                                                                                                                      orderContext.VendorOrderForecasts.Attach(row, true);

    ·                                                                                                                                                                      row.Model = model;

    ·                                                                                                                                                                      row.Created = created;

    ·                                                                                                                                                                      row.ModelDetail = XElement.Parse(modeldetail);

    ·                                                                                                                                                                  }

    ·                                                                                                                                                                  orderContext.SubmitChanges();

    ·                                  }
    ==================================================================================================================================


    Please note:
    We use the Attach methods with entities that have been created in one DataContext, serialized to a client, and then deserialized back with the intention to perform an update or delete operation.  Is your scenario similar with this?   Otherwise, I don’t think using Attach is a good option. 

     

    You may directly retrieve the objects and modify them without using Attach. 

     

    If you have only one object to modify, we can use the SingleOrDefault method.
    ========================================================================
                               using (OrderClassesDataContext orderContext = new OrderClassesDataContext())

    ·                                                                                                                                                              {

    ·                                                                                                                                                                  VendorOrderForecast row = orderContext.VendorOrderForecasts.

    ·                                                                                                                                                                                                SingleOrDefault(o => o.Vendor = vendor);

    ·                                                                                                                                                                  if (row == null)

    ·                                                                                                                                                                  {

    ·                                                                                                                                                                      //insert

    ·                                                                                                                                                                      row = new VendorOrderForecast();

    ·                                                                                                                                                                      row.Vendor = vendor;

    ·                                                                                                                                                                      row.Model = model;

    ·                                                                                                                                                                      row.Created = created;

    ·                                                                                                                                                                      row.ModelDetail = XElement.Parse(modeldetail);

    ·                                                                                                                                                                      orderContext.VendorOrderForecasts.InsertOnSubmit(row);

    ·                                                                                                                                                                  }

    ·                                                                                                                                                                  else

    ·                                                                                                                                                                  {

    ·                                                                                                                                                                      row.Model = model;

    ·                                                                                                                                                                      row.Created = created;

    ·                                                                                                                                                                      row.ModelDetail = XElement.Parse(modeldetail);

    ·                                                                                                                                                                  }

    ·                                                                                                                                                                  orderContext.SubmitChanges();

    ·                                  }       

    ========================================================================


    Or you want to modify multiple objects,

    ========================================================================
                           using (OrderClassesDataContext orderContext = new OrderClassesDataContext())

                            {

                                var rows = orderContext.VendorOrderForecasts.

                                                              Where(o => o.Vendor = vendor);

                                if (rows.Count() == 0)

                                {

                                    //insert

                                    VendorOrderForecast row = new VendorOrderForecast();

                                    row.Vendor = vendor;

                                    row.Model = model;

                                    row.Created = created;

                                    row.ModelDetail = XElement.Parse(modeldetail);

                                    orderContext.VendorOrderForecasts.InsertOnSubmit(row);

                                }

                                else

                                {

                                    foreach (var row in rows)

                                    {

                                        row.Model = model;

                                        row.Created = created;

                                        row.ModelDetail = XElement.Parse(modeldetail);

                                    }

                                }

                                orderContext.SubmitChanges();

                           }

    ========================================================================

    If you have any questions, please feel free to let me know.

     

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KevinBurton Tuesday, September 22, 2009 12:47 PM
    Tuesday, September 22, 2009 2:34 AM
    Moderator
  • Thank you I think I will go with the route that doesn't use Attach since this is causing me so much trouble.  I will have to admit that I don't really understand the need for Attach. For future reference here is what I am trying now:

    I assigned the primary key and now I am getting a LINQ exception. Here is the code:

                using (OrderClassesDataContext orderContext = new OrderClassesDataContext())
                {
                    bool exists = orderContext.OrderForecasts.Any(o => id == o.Identifier &&
                                                                       category == o.Category &&
                                                                       subcategory == o.SubCategory);
    
                    OrderForecast row = new OrderForecast();
                    row.Identifier = id;
                    row.Category = category;
                    row.SubCategory = subcategory;
                    if (!exists)
                    {
                        //insert
                        row.IdentifierType = identifiertype;
                        row.Created = created;
                        row.Model = model;
                        row.ModelDetail = XElement.Parse(modeldetail);
                        orderContext.OrderForecasts.InsertOnSubmit(row);
                    }
                    else
                    {
                        //attach then modify
                        orderContext.OrderForecasts.Attach(row);
                        row.IdentifierType = identifiertype;
                        row.Created = created;
                        row.Model = model;
                        row.ModelDetail = XElement.Parse(modeldetail);
                    }
                    orderContext.SubmitChanges();
                }
    


    Here is the excepotion:

    System.Data.Linq.ChangeConflictException {"Row not found or changed."}
    System.Data.Linq.ChangeConflictException was unhandled by user code
      Message="Row not found or changed."
      Source="System.Data.Linq"
      StackTrace:
           at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
           at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
    . . . .
      InnerException: 
    

    If you understand why I am getting this exception then it will certainly increase my understanding. Otherwise I will go thie 'non-Attach' route.

    Thank you.

    Kevin
    Tuesday, September 22, 2009 1:00 PM
  • Hi Kevin,

     

    I can reproduced the exception “Row not found or changed” if the entity UpdateCheck property is set.  When updating an entity, LINQ to SQL generates such a SQL command with the UpdateCheck columns:

    =====================================================================
    UPDATE [dbo].[Person]

    SET [PersonName] = @p2

    WHERE ([PersonID] = @p0) AND ([PersonName] = @p1)
    =====================================================================

     

    The PersonID and PersonName column are set to UpdateCheck.Always.  So LINQ to SQL will update the data record where PersonID and PersonName equal to the original values.  In your codes, the original record is not retrieved from the database, so LINQ to SQL do not know the original column values to build the correct SQL command.  The incorrect SQL command which raise the “Row not found or changed” exception can be this one:

    =====================================================================
    UPDATE [dbo].[Person]

    SET [PersonName] = @p1

    WHERE ([PersonID] = @p0) AND ([PersonName] IS NULL)
    =====================================================================

    Here are some additional references:

    http://dotnetslackers.com/Community/blogs/bmains/archive/2009/07/27/row-not-found-or-changed-linq-to-sql-error.aspx


    Thus, if all the columns are set to UpdateCheck.Never, your codes will work fine. 

     

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, September 23, 2009 8:26 AM
    Moderator