LINQ to SQL Caching Gotcha

Posted in .NET, Coding, IT, LINQ by Will on June 24, 2008.

So, today I discovered an issue which related to me doing two calls something a little like this:

- Execute dc.sp_Proc1
- If some condition exists, execute dc.sp_Proc2, and then Execute dc.sp_Proc1 again with the same parameters.
- Insert some records into the database.

The problem is, the first time you execute the sproc, it caches the result. This would be okay for most instances, but in mine - I’m actually after the updated result.

A quick bit of googling revealed this post by Chris Rock. This approach of “turn off object tracking” works Ok if you don’t need to insert records on that Data Context.

My quick, dirty, and (possibly) really wrong approach was just to spin up a new Data Context, and re-execute that sproc.

I promise I’ll find a more sane way of fixing this :)

2 Comments

  1. Robin Clowers replied:

    I think a new data context makes sense in this situation. The data context can be used to implement the unit of work pattern, which would lead to a different context per transaction.

    Great blog by the way, you may have found a new subscriber!

    June 26th, 2008 at 1:22 pm. Permalink.

  2. Will replied:

    Hi Robin,

    My concerns were really that I’d already just spun up one context to service the request (This is all in a WCF service, by the way) - so spinning up yet another just to bypass a caching method wasn’t my ideal method.

    Mind you, I havn’t found a better way yet. Although I did have a moment where I was tempted to use the crude hack our frontend guys love to use when bypassing caches - adding a nocache param and passing a random value.

    Thanks for the comment, and I’ll keep an eye on your blog too :)

    June 27th, 2008 at 1:13 am. Permalink.