cdakz
asked on
LINQ Query Returns Cached Data
Working on a vb.net web form that uses LINQ-to-SQL. I have a function similar to the following:
The first time it runs, it gets the correct Results data (e.g. for Subject.Id = 1). However, when running it later, for a different subject (Subject.Id = 2), it returns the Results data for Subject.Id = 1 instead of 2.
I've debugged the function to confirm that the Id truly has changed to 2, so I'm pretty sure that the 2nd query is using a cache and not actually re-querying the database.
How do I force it to actually re-query the database?
Function GetSubjectResults(ByVal Id As Integer) As String
Dim strReturn As String = Nothing
Dim dc As New myDataContext
Dim q = From iterVar In dc.Subjects _
Where iterVar.ID = Id
Select iterVar.Results
If q.Count > 0 Then
If q.First Is Nothing = False Then
strReturn = q.First
End If
End If
Return strReturn
End Function
The first time it runs, it gets the correct Results data (e.g. for Subject.Id = 1). However, when running it later, for a different subject (Subject.Id = 2), it returns the Results data for Subject.Id = 1 instead of 2.
I've debugged the function to confirm that the Id truly has changed to 2, so I'm pretty sure that the 2nd query is using a cache and not actually re-querying the database.
How do I force it to actually re-query the database?
Hi cdakz;
Because the DataContext is created and is disposed of when it leaves the function because it goes out of scope I doubt highly that it is a caching issue caused by linq, a new DataContext is created each time the function is called.
Can you set a breakpoint at the first if statement and view the variable q in the watch window and expand the results to see what value it holds?
Because the DataContext is created and is disposed of when it leaves the function because it goes out of scope I doubt highly that it is a caching issue caused by linq, a new DataContext is created each time the function is called.
Can you set a breakpoint at the first if statement and view the variable q in the watch window and expand the results to see what value it holds?
Also, have you checked your database to make sure it's the results for ID=2 should not be the same as the results for ID=1? Maybe the code is working properly, and you have a data problem. It's not an issue of caching the datacontext; in that case, the query would still return different results for ID=1 and ID=2.
ASKER
Yes I've set a breakpoint and examined that the ID truly changed and also am viewing the database directly with SQL server management studio before and after each line of code. The code is definitely not issuing a new data query.
ASKER
Oh yeah, I also did look at q in the watch Window and it is showing the result from the previous query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just a thought: What if you pull the New from the Dim statement for dc and make it a separate statement:
You could wrap the query in a Using container for the datacontext instance, to make sure it's destroyed at the end of the loop:
Dim dc as myDataContext
dc=new myDataContext()
orYou could wrap the query in a Using container for the datacontext instance, to make sure it's destroyed at the end of the loop:
dim dc as myDataContext
Using dc as new myDataContext()
...
End Using
ASKER
eflamm, regarding suggestion #1: I'm already using the New keyword, it's just that it's on the same line as the assignment.
Regarding suggestion #2, that causes the error: "Variable 'dc' hides a variable in an enclosing block." during compilation.
Regarding suggestion #2, that causes the error: "Variable 'dc' hides a variable in an enclosing block." during compilation.
ASKER
FernandoSoto, That did the trick. Not sure why it had to be explicitly disposed of, but thanks (gets me past this roadblock while meeting my deadline).
Hi cdakz;
What was most likely causing the issues was that because the DataContext was not explicitly disposed of when leaving the function call it needed to wait for garbage collection to find out if any object had a reference to the DataContext object and if there were none then it could dispose of the object. But if there were a call to the object in this state then it is possible to get the value that it holds, the old value as you were seeing. It is always a good idea to use a Using statement because of this.
Not a problem and glad to help.
What was most likely causing the issues was that because the DataContext was not explicitly disposed of when leaving the function call it needed to wait for garbage collection to find out if any object had a reference to the DataContext object and if there were none then it could dispose of the object. But if there were a call to the object in this state then it is possible to get the value that it holds, the old value as you were seeing. It is always a good idea to use a Using statement because of this.
Not a problem and glad to help.
hth
Mike