Problem

You’re updating a database record using LINQ to SQL, and upon calling ‘SubmitChanges’ you find that you’re getting the below error:

Operation is not valid due to the current state of the object

Solution

This relates to updating a foreign key when you’ve already read from the foreign key ‘object’ itself. To better illustrate this, imagine the below database structure

When you add this into your LINQ to SQL clss, LINQ will create you an association between the 2 and will create navigation properties. This means that if you have an Order object in your code, you will have an OrderStatus property available to let you read the fields from the corresponding table. Likewise, an object of type OrderStatus would have an Orders property which would be a list of Orders at that status.

Your code to update the status of an order may look like this

private void UpdateOrderStatus(int orderID, int statusID)
{
    using (var dc = new DataContext())
    {
         var order = dc.Orders.Single(a => a.ID == orderID);
         var existingStatus = order.OrderStatus;
         Console.WriteLine($"Updating the status from {existingStatus.Description}"); 
         order.StatusID = statusID;
         dc.SubmitChanges();
    }
}

This particular code would generate the aforementioned error message. If line 6 (and line 7 which uses the variable) didn’t exist, the code would work.

This is because that the OrderStatus row is only retrieved when you access the property (i.e. it’s lazy). Once the object has loaded, changing the foreign keyed field (StatusID) is not allowed. This is actually quite sensible, as there would be a mismatch between the StatusID and the row loaded in OrderStatus

What LINQ allows you to do is update any foreign keyed fields using its navigation property. So we simply need to retrieve the correct OrderStatus row and update the navigation property as below

private void UpdateOrderStatus(int orderID, int statusID)
{
    using (var dc = new DataContext())
    {
         var order = dc.Orders.Single(a => a.ID == orderID);
         var existingStatus = order.OrderStatus;
         var newStatus = dc.OrderStatus.Single(a => a.ID == statusID);
         Console.WriteLine($"Updating the status from {existingStatus.Description}"); 
         order.OrderStatus = newStatus;
         dc.SubmitChanges();
    }
}

TL;DR

Rather than update the foreign key column itself (i.e. StatusID), update the navigation property (i.e. OrderStatus)