LINQ to SQL cache issues

c#, caching, linq-to-sql

Solution

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);
}

Problem

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; } ```

Original source