<< Returning a single row from a stored procedure using LINQ TO SQL | Home | Extension Methods in VB.NET 2008 >>

LINQ TO SQL Caching Gotcha

Ran across a small issue with LINQ TO SQL I'd like to share. As you may or may not know LINQ TO SQL's default behavior when retrieving a result set from the database for an entity is to keep that object in cache. If you run another query against linq attempting to retrieve the same row, LINQ will return the cached object. Depending on how you want your program to behave this can be a good thing or a bad thing. For me, this was a bad thing.

My problem came while I was writing unit tests to check the value of an updated row in one of my tables. After I updated the row, I queried the table (using linq) expecting to receive the updated values. I did not get the updated values and my tests failed. My test looked something like this in pseudo-code:

1) Create a new People record by using email address test@test.com

2) Get a People object by using the email address test@test.com

3) Validate the people object is not null (Assert.IsNotNull)

4) Update the Password column value to "1234" using a stored procedure

5) Get a new People object using the email address test@test.com

6) Check the first people object password property to validate that it is different than the second people object password property. <-- This failed

The issue was LINQ was caching the first object after the first call to get the people object (Step 2 above) and then giving me the same object when I wanted to retrieve the new people object (Step 5 above).

The first thing I did was check the "Auto-Sync" property for the columns on the People entity in my LINQ TO SQL data model. It was set to "Always" which led me to believe this would always be refreshed. After confirming this was the case that got me to thinking about why this wasn't working. The conclusion I came up with is I was not updating the table using the DataContext.SubmitChanges method rather I was using a stored procedure.

I found a post online stating to use DataContext.Refresh. I tried this but quickly found out that by using the Refresh method LINQ was actually querying the database twice. That is obviously something I wanted to avoid.

After doing a little more research I found out that the issue was "object tracking". Apparently object tracking is the caching mechanism in LINQ TO SQL. Long story short, whenever you create the DataContext, set the ObjectTrackingEnabled to false and this caching issue goes away. The following is my function to get the datacontext for my linqtosql layer.

Private mDb As MyDataContext  
Public Function GetMyDataContext() As MyDataContext

If mDb Is Nothing Then
     mDb = New MyDataContext
     mDb.ObjectTrackingEnabled = False
End If

Return mDb

End Function

 

Make sure you only set the ObjectTrackingEnabled property before querying any entities. Otherwise an error will be thrown.


Technorati Tags: ,,.NET 3.5

posted @ Monday, January 14, 2008 5:46 PM

Print

Comments on this entry:

# re: LINQ TO SQL Caching Gotcha

Left by Hilton Giesenow at 2/24/2008 1:39 AM
Gravatar
Interesting. This makes perfect sense when you think about it, I suppose. Another good reason not to use sprocs with LINQ, in my opinion ;->. Also, to clarify, you obviously only do need to this is you are using sprocs. Do you perhaps return the updated entity in the sproc, e.g.:

sproc pseudo-code:
update table where id = @id
select * from table where id = @id

that might also have worked?

On a related note, you can use transactions on the datacontext to rollback at the end of your unit tests in order to isolate them better.

# re: LINQ TO SQL Caching Gotcha

Left by Chris at 2/24/2008 9:48 AM
Gravatar
There are many reasons to use proc and really only one reason NOT to use them; Dynamic SQL queries.

Other than that there should never be a reason to give an application insert, update or delete access to a table.

All of those calls should be done through procs for the masses.

I understand why they did it but I wanted anyone else that ran across this problem to know how to fix it.

Thanks for the comments! Keep em coming!

# re: LINQ TO SQL Caching Gotcha

Left by yeraycaballero at 4/16/2008 10:01 AM
Gravatar
Hi Chris, I liked your post, but I have a different opinion.

Linq do it well. Why do you want to have got two instances of same person.

- In your data model you have only one table row for this person.
- In your object model you should have only one object for this person to.

A person is an entity that has got indentity. If you think in real world there aren't two equals persons.

I would like know what do you think?.

# re: LINQ TO SQL Caching Gotcha

Left by Chris Rock at 4/16/2008 11:05 AM
Gravatar
The point is that the default behavior of LINQ is to keep the object in cache even though the data from the database is different. Here is an example

Get person object with ID #1 out of the person table. The person object has two properties. "Name" and "EmailAddress". Name = "chris rock", EmailAddress = "chris@rocksthoughts.com"

Update the email address of person object ID #1 with a stored procedure named usp_person_updateemailaddress to "rock@rocksthoughts.com".

Get person object with ID #1 out of the person table again. We should now get a person with Name = "chris rock" and EmailAddress = " rock @rocksthoughts.com" but we do not. LINQ TO SQL has cached the previous object and we get emailaddress of "chris@rocksthoughts.com".

This will obviously lead to problems especially during unit testing.

# re: LINQ TO SQL Caching Gotcha

Left by yeraycaballero at 4/16/2008 1:57 PM
Gravatar
Now I understand what do you want to do. But if you show me some code it was better. I don't know .NET Linq too much but I have working with ORM's a lot.

Maybe I'm wrong because I don't know Linq too much but I think you are changing your table "with a store procedure" and your are not
calling sumbitChanges from DataContext after it.

"DataContext tracks changes that you made to all retrieved entities and maintains an identity cache"

If you don't call submitchanges method of DataContext, DataContext doesn't know your person instance hasn't be changed and for this reason it returns you the cached person instance

You should do something like this:

using(TransactionScope ts = new TransactionScope())
{
db.ExecuteCommand("exec DoIt");
db.SubmitChanges();
ts.Complete();
}

# re: LINQ TO SQL Caching Gotcha

Left by Chris Rock at 4/16/2008 10:18 PM
Gravatar
I do not run a stored procedure through db.ExecuteCommand. I will call the hard coded version (e.g. usp_person_updateemailaddress) that linq to sql generates.

When I was testing this I did call submitchanges to no avail.

# re: LINQ TO SQL Caching Gotcha

Left by praveen at 5/14/2008 6:37 AM
Gravatar
This is really a good post and helped me a lot. thanks to Chris.
good post and continue the same work.

thanks

# re: LINQ TO SQL Caching Gotcha

Left by ben at 6/26/2008 7:55 AM
Gravatar
There is only one reason ever to use stored procs , and that is if you have a great DBA team that manages them

If you NEED security use different accounts / Security pricipals but with most larger apps the security should be in the app layer not the DB .

If you want the deployment headached of deploying another tier use store procs in most cases you gain nothing.

# re: LINQ TO SQL Caching Gotcha

Left by Chris Rock at 6/28/2008 3:17 PM
Gravatar
In my experience most developers that don't use procs are either lazy, afraid or uniformed. There are times when you can use dynamic sql. For instance when creating a dynamic search. Even then, the user executing the search should only have read only access to those tables needed for the search.

If you worked in any type of secure environment you would NEVER just have security at the business layer. If you were a bank you would rely on the business layer to get it right or would you lock down the database and rely on access through procs?

I take the approach of security first, flexibility second. In the end my software will be more secure because of it. There aren't too many situations where you would never *NEED* security.

FYI, my current environment does not have a DBA. I just know better...

I leave you with this article:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/06/28/if-only-you-d-used-stored-procedures-you-wouldn-t-have-got-hacked.aspx


Thank you for the comments!

# re: LINQ TO SQL Caching Gotcha

Left by Gavin at 8/19/2008 3:04 PM
Gravatar
Chris I assume that you understand that with AD you can create multiple forests, connect multiple domains together and enforce 1/2-N way trusts. Also in the end most of the roles are hinged around the application domain, ..., it's function and securing those functions ... You cannot guarentee that using sprocs this gives a level of security that allows efficient and user effective means of validation either, which probably are going to be coded in the app anyway (DDD). I think we should all understand how security works onsite as much as possible and code accordingly not just for incase we need it... What do you reckon?

# re: LINQ TO SQL Caching Gotcha

Left by Gavin at 8/19/2008 3:55 PM
Gravatar
Chris I assume that you understand that with AD you can create multiple forests, connect multiple domains together and enforce 1/2-N way trusts. Also in the end most of the roles are hinged around the application domain, ..., it's function and securing those functions ... You cannot guarentee that using sprocs this gives a level of security that allows efficient and user effective means of validation either, which probably are going to be coded in the app anyway (DDD). I think we should all understand how security works onsite as much as possible and code accordingly not just for incase we need it... What do you reckon?

# re: LINQ TO SQL Caching Gotcha

Left by Andy at 9/1/2008 1:45 PM
Gravatar
Isn't there a dependency option to manoeuvre between cached objects and database changes ?

(Just like in SqlDependency)

# re: LINQ TO SQL Caching Gotcha

Left by Aaron at 10/7/2008 9:05 AM
Gravatar
Careful with this option. If you turn it off, you can no longer traverse the relationships. You have to set load options before doing any queries, otherwise the data context will not query any child or parent tables. This also removes the ability to do a SubmitChanges on the DataContext.

# crosleyben

Left by negative early at 4/14/2009 8:24 PM
Gravatar
open contribute environment frozen app simulation

# re: LINQ TO SQL Caching Gotcha

Left by Roman at 4/29/2009 9:15 AM
Gravatar
Chris, let's me continue this old disscussion.
ObjectTrackingEnabled works good while you don't want to use submitchange. But if I have big multiuser application, that read and write data, I must use two way:
1. Use dataContext with ObjectTrackingEnabled =false as singleton for read data, and another datacontext for write
2. Create local datacontext's

is this right? Any other more clear way?

# re: LINQ TO SQL Caching Gotcha

Left by David at 6/4/2009 2:44 PM
Gravatar
Why are you calling the sproc directly? If you plan to use sprocs with Linq to SQL you need to have a sproc to update all updateable columns on the table, not just a single column.

usp_person_updateemailaddress should be usp_person_update

You need to configure your Person Entity class so that it uses the mapped sproc function when the entity is updated (This can be done from the designer)

You will then be able to do...

using(PersonDataContext context = new PersonDataContext())
{
Person person = context.usp_person_selectbyemail("test@test.com");
person.Email = "newemail@test.com";
context.SubmitChanges();
}


When SubmitChanges is called it will invoke usp_person_update.

Your comment:



 (will not be displayed)


 
 
 
Please add 7 and 3 and type the answer here:
 

Live Comment Preview:

 
«July»
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678


My name is Chris Rock. I'm a .NET / SQL Server consultant and trainer in the Orlando, FL area for EndToEndTraining.com. I'm not a comedian. Although I do get laughed at (not with) and laugh at myself frequently.

About
Syndication:

RSS ATOM