How to stop linq to sql from caching my data? RRS feed

  • Question

  • I am totally lost with this behavior, please help...

    I am using visual studio 2010 and while testing in the IDE, I noticed the data is being cached even after i ended the run, rebuild and run again.  I double checked the database and the data is updated, however the old data is still showing every time i run the app.  I tried setting objectTrakingEnabled = false but still i am seeing the old data.  I am using a stored procedure and returning a List of objects via a web service.   this is the code:

            public List<Employee> GetEmployees()
                DB.EmployeeDataContext employeeData = new DB.EmployeeDataContext();
                employeeData.ObjectTrackingEnabled = false;
                var employees = employeeData.getEmployee().ToList();
                return employees;

    Any help is greatly appreciated.

    Friday, August 20, 2010 2:10 PM


  • I feel like a total retard...

    Apologies to all of you the data i was pulling IS updating.  I updated the wrong field, well they have the same name but in different tables.

    Anyways, again sorry, and i must say, i trully appreciate all the help!!!



    Friday, August 20, 2010 6:16 PM

All replies

  • ObjectTracking is the concept that if the object is from the database and changes it will be updated on submit changes

    DataContext dc = new DataContext();
    Item item = dc.GetTable<Item>().FirstOrDefault();
    if(item != null
     item.Value = "New Value";
     dc.SubmitChanges(); //Updates Item

    What you want is the latest version.  The only way to do that is to create a new DataContext.  DataContexts are light and disposable, so it isn't a big deal.  You can create a Singleton that notifies each thread on Submitted.  You probably would want to look into Optimistic Concurrency.

    Having the Data Update auto magically update would be a management nightmare.  IE: Your iterating over a list and the list count changes.  Another issue is the speed in which the properties of the data is executed.  If everytime you accessed a property it had to go back to the database they shouldn't be properties but methods.  So, you might also want to look into member design also. The values of the properties are interally cached after their first get. Any changes to those properties will be stored, and subsequent queries will return the cached values.

    If you go into the DBML and select your table's column and goto properties. There is a property called Auto-Sync. You can set that to Always. I have not used it therefore don't know what to expect.

    Friday, August 20, 2010 3:28 PM
  • Create a new datacontext or invalidate the current one to force it to update the cached info.

    This is perhaps the simplest way to achieve what you want

    Also you can update using the datacontext because of the object tracking (if you don't disable it), however this will work only when you need it because of the optimistic concurrency, that what the caching is all about


    PS: also please don't double post

    Friday, August 20, 2010 3:35 PM
  • Sorry for the double posting.  Dont know what happened, I thought I only posted once, but it showed up twice, thats weird!

    I am a newbie so please bear with me.  I still cant wrap my head around this behavior.  Let me try to clarify, the data is not being updated within the code.  Here are the steps:

    1. I run my application (pressing F5) in visual studio.

    2. i retrieve a list of employee records by calling the web service and see wrong data.

    3. i stop the application in visual studio

    4. in management studio, i updated one employee in the database manually, lets say i changed birthdate for employeeID 5 from 1/8/2000 to 7/25/2000

    5. i run the application again, pulling up exactly the same records using the same web service, birthdate for employeeID 5 still shows 1/8/2000.

    I should see 7/25/2000, no?

    Friday, August 20, 2010 3:48 PM
  • Did you actually commited the data in the database? Did the program that runs the code you show was restarted?

    If so then yes

    Friday, August 20, 2010 4:04 PM
  • Let me understand the architecture. You have VS2010, a web service and a database, right? Are you resetting the web service in between? It would be that which is caching the data.

    I would have thought that using a new context each time, would resolve your problem. A couple of extra thoughts; though I don't know if they really will help.

    1) You don't dispose your context - you could use "using (DB.EmployeeDataContext employeeData = new DB.EmployeeDataContext();) { ... }"

    2) Maybe you need to also use the Refresh method with RefreshMode.OverwriteCurrentValues



    Friday, August 20, 2010 4:16 PM
  • Yes the data is committed, and yes the program restarted.  But still seeing the old data.  I am pulling my hair out I dont understand what is going on.

    \The web service is an existing web service all i am doing is adding a new webMethod to use the linqToSQL datacontext to pull data.  Other web methods in the same web service do not have this behavior, but they are not using linqToSQL.

    i understand the disposing part, but wouldnt restarting the program re-instantiate a new datacontext?  I tried looking for setting that may contribute to this behavior but couldnt find any.  Did i miss something?

    Thanks guys for trying to help, really appreciate it. 

    Friday, August 20, 2010 4:41 PM
  • Have you restarted your web service?  Are you sure your connecting to the right webserver?  Can you hit a debug point in your webserver that creates a new datacontext?
    Friday, August 20, 2010 4:54 PM
  • From the behavior you are describing the webservice seems guilty

    Try restarting the webservice to see if you get the new data

    Check how the webservice is pulling the data, read this, it might help you (specially the end part)


    Friday, August 20, 2010 4:58 PM
  • I feel like a total retard...

    Apologies to all of you the data i was pulling IS updating.  I updated the wrong field, well they have the same name but in different tables.

    Anyways, again sorry, and i must say, i trully appreciate all the help!!!



    Friday, August 20, 2010 6:16 PM