Wednesday, June 25, 2008

Linq To SQL Caching

I ran into a weird behavior while trying out different usage patterns of Linq To SQL. I noticed that some queries were not hitting the database! Now I knew that Linq To SQL object tracking keeps cached copies of entities it retrieves, but my understanding was that it only used this for identity mapping and would never return stale results. After some Googling and then looking at the internals of the System.Data.Linq.Table class with Reflector, I came to the conclusion that it was indeed returning its cached results. This makes sense once you understand the way the data context works; I didn't realize the implications of object tracking. Once an object has been retrieved once by a data context, its values will not be updated by the database. This is key for the way optimistic concurrency support works in Linq to SQL, but if you are used to writing simple crud applications where you ignore concurrency it would be easy to overlook this.

On thing still puzzles me though, if I change my call from




I would always hit the database. It turns out that ToList calls GetEnumerator (which leads to a query being fired) whereas when I databind directly against the Table, it calls IListSource.GetList, which will return the cached table if it can. Why wouldn't you query the database to check for new objects that might have been added to your results, and why couldn't the same query use the cache when I call ToList on it?


Unknown said...

I've had a bunch of weirdness with LINQ caching - although most of mine have been around stored procs.

The .ToList() method, whilst crude, seems to be a good way to bypass a raft of issues.

The main drawback I found is that unless you're using something like Bindable LINQ you've just broken any eventing notifications right there should you subsequently update rows.

Obviously it's not that important for ASP.NET and other backend services, but is more important for WinForms/Silverlight/WPF.

Robin Clowers said...

Thanks for the comment Will! I agree that using ToList is the most intuitive way to solve this, but it definitely acheives the desired effect. Bindable LINQ looks really interesting, I have not tried using LINQ in a connected scenario, so I was unaware of those issues.