LINQ to SQL bug when updating foreign key fields
-
Came across an annoying bug in LINQ to SQL today where you update foreign keys. Interested to hear if anyone else has seen this behaviour. Summary: if you've loaded related objects using DataLoadOptions but then change the foreign key directly, the change is ignored. I have a process that uploads orders to a supplier service via the web. The process used work fine before I moved to LINQ to SQL.. now for some reason it wasn't updating the order state after sending the order. I checked and rechecked my code - I had a loop that ran
order.SetStatusAsSent()
Stepping thru the code I could see this had the simple code
Me.OrderStateID = 2 ' 2 represents the order state 'sent'
Me.OtherInfo = "Order sent"Problem was that it wasn't saving the OrderStateID change. I had a .SubmitChanges statement right after the loop.. but examining the SQL I see it only updated the
OtherInfofield. I eventually figured out (after some headscratching) that because I had loaded the related object to the OrderStateID - which is a foreign key field - using DataLoadOptions - it was ignoring the change. In other cases where the foreign key field was not loaded the change worked. I changed the code to set the status thus:Dim newState = db.OrderStates.Single(Function(s) s.OrderStateID=2)
Me.OrderState = newState
Me.OtherInfo = "OrderSent"Now the code works as expected. A nasty one!
'Howard
-
Came across an annoying bug in LINQ to SQL today where you update foreign keys. Interested to hear if anyone else has seen this behaviour. Summary: if you've loaded related objects using DataLoadOptions but then change the foreign key directly, the change is ignored. I have a process that uploads orders to a supplier service via the web. The process used work fine before I moved to LINQ to SQL.. now for some reason it wasn't updating the order state after sending the order. I checked and rechecked my code - I had a loop that ran
order.SetStatusAsSent()
Stepping thru the code I could see this had the simple code
Me.OrderStateID = 2 ' 2 represents the order state 'sent'
Me.OtherInfo = "Order sent"Problem was that it wasn't saving the OrderStateID change. I had a .SubmitChanges statement right after the loop.. but examining the SQL I see it only updated the
OtherInfofield. I eventually figured out (after some headscratching) that because I had loaded the related object to the OrderStateID - which is a foreign key field - using DataLoadOptions - it was ignoring the change. In other cases where the foreign key field was not loaded the change worked. I changed the code to set the status thus:Dim newState = db.OrderStates.Single(Function(s) s.OrderStateID=2)
Me.OrderState = newState
Me.OtherInfo = "OrderSent"Now the code works as expected. A nasty one!
'Howard
Wierd.. I was trying to check this using a test project using Northwind and a simple LINQ to SQL Datacontext. I find that the code generated for this datacontext has a check to see if the related object is loaded for an association, e.g. :
<Column(Storage:="_CustomerID", DbType:="NChar(5)")> _
Public Property CustomerID() As String
Get
Return Me._CustomerID
End Get
Set
If (String.Equals(Me._CustomerID, value) = false) Then
If Me._Customer.HasLoadedOrAssignedValue Then _
Throw New System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException
End If
Me.OnCustomerIDChanging(value)
Me.SendPropertyChanging
Me._CustomerID = value
Me.SendPropertyChanged("CustomerID")
Me.OnCustomerIDChanged
End If
End Set
End PropertyYet my datacontext does not have this code for the foreign keys.. and I cannot see any reason why it should be different. Any ideas anyone??
'Howard