# Thursday, February 05, 2009

Lets say I have a nice simple database (guess which one…)

CropperCapture[5]

Now I want to insert a new Order into the Orders table

 

using (NorthwindDataContext context = new NorthwindDataContext())

{

    Order order = new Order();

    context.Orders.InsertOnSubmit(order);

    context.SubmitChanges();

}

No problem.  Totally works like you’d think.  What happens if I make more changes after that and call SubmitChanges() again?

using (NorthwindDataContext context = new NorthwindDataContext())

{

    Order order = new Order();

    context.Orders.InsertOnSubmit(order);

    context.SubmitChanges();

 

    order.Freight = 98.77M;

    order.ShipName = "Fred Jones";

 

    context.SubmitChanges();

}

Still cool.  The problem comes in if I add an insert trigger.  Let’s say I add a trigger to set the OrderDate column of the order to make sure it’s always the right date…

ALTER TRIGGER Trigger1

ON dbo.Orders

FOR INSERT

AS

UPDATE Orders

SET OrderDate = GETDATE()

FROM Orders INNER JOIN inserted ON Orders.OrderID = inserted.OrderID

Whether or not that’s a good idea is a whole ‘nother topic.  Seemed like the thing to do at the time…  However, now there’s a problem.  The first SubmitChanges() works fine, but the second one fails with an update conflict.  WTF?  After I stopped to think about it (and talked to Stuart) I realized that that makes perfect sense.  The copy of Order that I have in memory doesn’t know anything about the result of the insert trigger, and so the record is out of date, hence the conflict.  In this particular case it wasn’t a problem because it wasn’t too much work to just take out the trigger and set the date in the code.  I had been hoping that I wouldn’t have to redeploy the web app, hence the trigger.  Best laid plans…

Anyway, it turns out that something similar came up on another project, so it is something to be aware of.

Thursday, February 05, 2009 2:49:23 PM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [2]  | 
Friday, February 06, 2009 6:02:33 AM (Pacific Standard Time, UTC-08:00)
You can turn off concurrency checking for that column.
El Guapo
Friday, February 06, 2009 8:59:31 AM (Pacific Standard Time, UTC-08:00)
Calling Refresh on the DataContext should work to get the copy in memory in sync with what is in the database, but should be used sparingly on a high volume app because it is very slow.
Leo
Comments are closed.