none
Stored Procedure based method run for a 2nd time does not return data changes that happened since 1st time RRS feed

  • 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?

    Monday, August 30, 2010 11:48 PM

Answers

  • I don't know if this is relevant for the results of stored procedures, but have you tried using the DataContext.Refresh method? I've had to use this to refresh an object who's database counterpart has been updated by a stored procedure - e.g I have the customer record in the DataContext object, I call a stored procedure which updates the database, then have to call .Refresh() to get the latest value from the database back into the DataContext object.

    An alternative is to use a second DataContext for the second call.

    • Marked as answer by liurong luo Monday, September 6, 2010 6:45 AM
    Tuesday, August 31, 2010 8:06 AM
  • We have discused this on severals occasions, in order to keep db calls to a minimun, the data is cached so it is up to you to tell it to retrieve the new data.  Think of concurrency and you will know why.

    John advice works perfectly and there is little to no overhead in this.  You can actually check if the data changed if you want.

    Also, enable object tracking.

    Regards

    • Marked as answer by liurong luo Monday, September 6, 2010 6:46 AM
    Tuesday, August 31, 2010 1:03 PM

All replies

  • I don't know if this is relevant for the results of stored procedures, but have you tried using the DataContext.Refresh method? I've had to use this to refresh an object who's database counterpart has been updated by a stored procedure - e.g I have the customer record in the DataContext object, I call a stored procedure which updates the database, then have to call .Refresh() to get the latest value from the database back into the DataContext object.

    An alternative is to use a second DataContext for the second call.

    • Marked as answer by liurong luo Monday, September 6, 2010 6:45 AM
    Tuesday, August 31, 2010 8:06 AM
  • We have discused this on severals occasions, in order to keep db calls to a minimun, the data is cached so it is up to you to tell it to retrieve the new data.  Think of concurrency and you will know why.

    John advice works perfectly and there is little to no overhead in this.  You can actually check if the data changed if you want.

    Also, enable object tracking.

    Regards

    • Marked as answer by liurong luo Monday, September 6, 2010 6:46 AM
    Tuesday, August 31, 2010 1:03 PM