?
Solved

LINQ Query Returns Cached Data

Posted on 2013-11-01
10
Medium Priority
?
703 Views
Last Modified: 2016-02-10
Working on a vb.net web form that uses LINQ-to-SQL. I have a function similar to the following:

   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

Open in new window


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?
0
Comment
Question by:cdakz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 39618273
I don't work with Linq (thanks be!) but you'll find an explanation of the caching on this MS page,  In Step 2 it refers to refreshing your data context, which is probably what you need to do in order to get linq to stop using old data.

hth

Mike
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39618299
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?
0
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 39618357
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.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:cdakz
ID: 39618815
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.
0
 

Author Comment

by:cdakz
ID: 39618825
Oh yeah,  I also did look at q in the watch Window and it is showing the result from the previous query.
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 39618845
Hi cdakz;

Try modifying your code as follows. This should make sure that the myDataContext is disposed of.

Function GetSubjectResults(ByVal Id As Integer) As String

    Dim strReturn As String = Nothing
    
    Using 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
    End Using

    Return strReturn
    
  End Function

Open in new window

0
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 39618851
Just a thought: What if you pull the New from the Dim statement for dc and make it a separate statement:
Dim dc as myDataContext
dc=new myDataContext()

Open in new window

or
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
Using  dc as new myDataContext()
   ...
End Using

Open in new window

0
 

Author Comment

by:cdakz
ID: 39619110
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.
0
 

Author Closing Comment

by:cdakz
ID: 39619114
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).
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39619144
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

766 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question