LINQ to SQL - method run for 2nd time does not return data changes that happened since 1st time

Question

I have created a method in my data context that is mapped to a SQL stored procedure. The method is used in an ASP.NET application, and it is called twice in the lifecycle of a page. It returns the same single object in both cases (i.e. same primary key).

After the 1st call some data changes are made, so on the 2nd call the stored procedure returns the same record but with different property values. If I use the debugger and SQL Profiler I can verify absolutely that the record being returned has the same PK but different property values between the 1st and 2nd calls.

However, on the 2nd call the object returned by the method is identical to the object returned in the 1st call. It is as if LINQ has run the stored procedure but then totally ignored the results, deciding instead that the data couldn't have changed since the first time it was run, so it may as well return a copy of the original object that it happened to hang on to!

I have experimented with setting the datacontext's ObjectTrackingEnabled to false immediately before calling my method, but this stops me being able to reference related objects.

Here's the code I use to call the method:

Dim stl = _DataContext.GetMyStatus(SelectedUserID)
Dim st As MyStatus= stl.FirstOrDefault

I really need to be able to call this method more than once in the lifecycle of the page, and for it to accurately reflect the current state of the database, so how do I do it?

Answer 1

DataContext produces a single instance per primary key value. It populates this single instance the first time it sees the record, and then returns that instance for any future requests with that key.

If you want to update an existing instance's value from the database, use the Refresh method.

I really need to be able to call this method more than once in the lifecycle of the page, and for it to accurately reflect the current state of the database.

Don't share datacontexts between different page requests.