LINQ to SQL cache issues

Question

Long story short: I am using Linq to sql, but am having issues with its caching

My application is metadata driven, so I do not want caching on (changes in the db should be reflected in the web site on a page refresh). Is there a way to turn caching off? or a way to reset the cache (for example currently when I change data in the database, I have to physically change the code and recompile before I see the results).

And finally a c# question (hopefully a basic mistake on my part). In the below code if I run method1 then method2 then doc2 == doc1 (and I want it to get the original value from the db)

This seems very strange to me as the RecordDictionary class is pivoted data (so does not directly relate to the model), and in my code the assignments are in different controllers; but somehow linq to sql is caching changes applied to doc1 and applying them to doc2 (if I exit out of my app, and recompile then doc2 equals what I expect it to be (until I change doc1)

Contrived example

public RecordDictionary method1()
{
    RecordDictionary doc1 = genericRepository.GetRecordById(
        action.AppliesToModelEntityId, 27);

    //do some stuff to doc1 here
    return doc1;
}

public RecordDictionary method2()
{    
    RecordDictionary doc2 = genericRepository.GetRecordById(
        action.AppliesToModelEntityId, 27);
    return doc2;
}

public RecordDictionary GetRecordById(int ContainerModelId, int id)
{
    var query = (from dv in _db.DataValues
                 where dv.DataInstance.IsCurrent == true &&
                     dv.DataInstance.DataContainer.DataContainerId == id
                 select new { 
                     dv.DataInstance.DataContainer.ParentDataContainerId, 
                     dv });

    RecordDictionary result = CreateRecordColumns(
        ContainerModelId, query.FirstOrDefault().ParentDataContainerId);
    result.Id = id;

    foreach (var item in query)
    {
        if (result.ContainsKey(item.dv.ModelEntity.ModelEntityId))
            result[item.dv.ModelEntity.ModelEntityId] = item.dv;                               
    }

    return result;
} 

Answer 1

Is ObjectTrackingEnabled set to true on your data context class? If so you may want to try setting it to false. Also, you may find reading this blog entry useful.

Answer 2

Create a new DataContext per "unit of work": i.e. a HTTP request, or even a method [1] - the intent is that SubmitChanges is called once at the end of the method (or as part of the implementation).

// Where this is all one (financial) transaction.
void Transfer(int fromAccountId, int toAccountId, decimal amount) {
    var db = new Customers();
    var fromAccount = db.Accounts.Single(row => row.Id == fromAccountId);
    var toAccount = db.Accounts.Single(row => row.Id == toAccountId);
    fromAccount.Balance -= amount;
    toAccount.Balance += amount;
    db.SubmitChanges();
}

You can also call DataContext.Refresh() on rows you expect to have changed behind the DataContexts back, but it is difficult to use efficiently. It's intended for things like stored procedures:

var client = db.Clients.Single(row => row.Id == clientId);
if (!client.CheckingAccount) {
    db.CreateCheckingAccount(client.Id); // Stored procedure.
    db.Refresh(RefreshMode.OverwriteCurrentValues, client);
}