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?