How to clear the DataContext cache on Linq to Sql

Question

I'm using Linq to Sql to query some database, i only use Linq to read data from the DB, and i make changes to it by other means. (This cannot be changed, this is a restriction from the App that we are extending, all updates must go trough its sdk).

This is fine, but I'm hitting some cache problems, basically, i query a row using Linq, then i delete it trough external means, and then i create a new row externally if i query that row again using linq i got the old (cached) data.

I cannot turn off Object Tracking because that seems to prevent the data context from auto loading associated propertys (Foreign Keys).

Is there any way to clear the DataContex cache?

I found a method sufring the net but it doesn't seem safe: http://blog.robustsoftware.co.uk/2008/11/clearing-cache-of-linq-to-sql.html

What do you think? what are my options?.

Answer 1

If you want to refresh a specific object, then the Refresh() method may be your best bet.

Like this:

Context.Refresh(RefreshMode.OverwriteCurrentValues, objectToRefresh);

You can also pass an array of objects or an IEnumerable as the 2nd argument if you need to refresh more than one object at a time.

Update

I see what you're talking about in comments, in reflector you see this happening inside .Refresh():

object objectByKey = context.Services.GetObjectByKey(trackedObject.Type, keyValues);
if (objectByKey == null)
{
    throw Error.RefreshOfDeletedObject();
}

The method you linked seems to be your best option, the DataContext class doesn't provide any other way to clear a deleted row. The disposal checks and such are inside the ClearCache() method...it's really just checking for disposal and calling ResetServices() on the CommonDataServices underneath..the only ill-effect would be clearing any pending inserts, updates or deletes that you have queued.

There is one more option, can you fire up another DataContext for whatever operation you're doing? It wouldn't have any cache to it...but that does involve some computational cost, so if the pending insert, update and deletes aren't an issue, I'd stick with the ClearCache() approach.