Came across an annoying bug in LINQ to SQL today. 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.
I checked and rechecked my code – I had a loop that ran
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 OtherInfo field.
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.
I changed the code to load the related object 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!