<< 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.

# re: LINQ TO SQL Caching Gotcha

Left by Random Pseudo-Purist at 9/15/2009 12:51 AM
Gravatar
Just want to point out to the author and several commenters that you aren't really writing unit tests. once you start hitting the database or another layer, you are really writing integration tests. if you're writing integration tests, you have to ask yourself if what you are doing is a realistic scenario that is likely to happen under normal usage. i know this post is a bit old, but there seems to be a new contribution or two

# re: LINQ TO SQL Caching Gotcha

Left by software developers at 10/26/2009 7:22 AM
Gravatar
Nice post,

Some nice information and advice regarding sql caching,

Thanks for writing about it,

Keep up the good work,

# re: LINQ TO SQL Caching Gotcha

Left by Prabodh at 12/3/2009 11:53 PM
Gravatar
Thank you very much for this post.
I was facing same problem in my project and your post helped me to get out of this.

I hope you'll keep on posting good stuff.

# re: LINQ TO SQL Caching Gotcha

Left by Prabodh at 12/3/2009 11:53 PM
Gravatar
Thank you very much for this post.
I was facing same problem in my project and your post helped me to get out of this.

I hope you'll keep on posting good stuff.

# re: LINQ TO SQL Caching Gotcha

Left by Prabodh at 12/3/2009 11:53 PM
Gravatar
Thank you very much for this post.
I was facing same problem in my project and your post helped me to get out of this.

I hope you'll keep on posting good stuff.

# re: LINQ TO SQL Caching Gotcha

Left by winning in slot machines at 12/11/2009 6:16 AM
Gravatar
Technically by disallowing Windows users access you can block anyone’s connection in effect disabling it.While being the most secure, its not the fastest…for sites that have hundreds/thousands of connections made per minute there is a noticeable difference between Windows and SQL Server Authentication.

# re: LINQ TO SQL Caching Gotcha

Left by php chat software at 12/21/2009 10:07 AM
Gravatar
Well, this is my first visit to your blog! But I admire time and effort you put into it, especially into interesting articles you share here!

# re: LINQ TO SQL Caching Gotcha

Left by Web Hosting at 12/22/2009 1:50 PM
Gravatar
Other than that there should never be a reason to give an application insert, update or delete access to a table.

# re: LINQ TO SQL Caching Gotcha

Left by inbound call center at 12/29/2009 1:31 PM
Gravatar
Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It's always nice when you can not only be informed, but also entertained!

# re: LINQ TO SQL Caching Gotcha

Left by Web design company at 12/30/2009 3:51 PM
Gravatar
Thanks for great tips!

# re: LINQ TO SQL Caching Gotcha

Left by CSharp at 1/1/2010 6:39 AM
Gravatar
http://code.msdn.microsoft.com/linqtosqlcache

# re: LINQ TO SQL Caching Gotcha

Left by telephone answering service at 1/7/2010 12:21 PM
Gravatar
Excellent post. This was actually what I was looking for, and I am glad that I finally came here! This for sharing and keep up the good work... thanks for this nice article Good post.....Valuable information for all. I will recommend my friends to read this for sure…

# re: LINQ TO SQL Caching Gotcha

Left by chipper at 1/7/2010 12:23 PM
Gravatar
I was very pleased to find this site.I wanted to thank you for this great read!! I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you post.

# re: LINQ TO SQL Caching Gotcha

Left by cheap binders at 1/7/2010 12:24 PM
Gravatar
This is good site to spent time on .I just stumbled upon your informative blog and wanted to say that I have really enjoyed reading your very well written blog posts. I will be your frequent visitor, that’s for sure.

# health coverage

Left by health coverage at 1/7/2010 1:18 PM
Gravatar
Your blog is so good and filled with interesting things that its a daily task for me to take a peek at for new posts! Keep it up!

# e: LINQ TO SQL Caching Gotcha

Left by nicolas at 1/24/2010 4:14 PM
Gravatar
Hey thx for this great post, I have to visit your blog more often.

# re: LINQ TO SQL Caching Gotcha

Left by Online Sweepstakes at 2/17/2010 2:58 AM
Gravatar
Thanks for this posting...I was also facing same problem

# re: LINQ TO SQL Caching Gotcha

Left by asigurari auto at 2/21/2010 1:24 AM
Gravatar
You still done correctly. Even if it came out first after all you did.

# re: LINQ TO SQL Caching Gotcha

Left by Seo Services at 2/26/2010 4:44 AM
Gravatar
great share thanks for this

# re: LINQ TO SQL Caching Gotcha

Left by Canvas Prints at 3/2/2010 1:42 PM
Gravatar
Thanks for this, cool post

# re: LINQ TO SQL Caching Gotcha

Left by Canvas Prints at 3/2/2010 1:42 PM
Gravatar
Thanks for this, cool post

# re: LINQ TO SQL Caching Gotcha

Left by Canvas Prints at 3/2/2010 1:42 PM
Gravatar
Thanks for this, cool post

# re: LINQ TO SQL Caching Gotcha

Left by FERNANDEZAmy34 at 3/7/2010 11:46 PM
Gravatar
It's cool that people can get the credit loans moreover, that opens up completely new opportunities.

# re: LINQ TO SQL Caching Gotcha

Left by web development at 3/8/2010 1:08 PM
Gravatar
This is great work and we’ve been using it very sucessfully – until today :) We’ve got a query which is evaluated with the same expression (and hence MD5) even though the parameters are different. The expression is pretty huge but one thing I noticed is that it’s showing a @p0 parameter in the expression, rather than the actual value being used.

Any ideas why this might be?

Your comment:



 (will not be displayed)


 
 
 
Please add 3 and 4 and type the answer here:
 

Live Comment Preview:

 
«March»
SunMonTueWedThuFriSat
28123456
78910111213
14151617181920
21222324252627
28293031123
45678910


My name is Chris Rock. I'm a .NET / SQL Server developer for PragmaticWorks, Inc based out of Jacksonville, FL. I'm not a comedian. Although I do get laughed at (not with) and laugh at myself frequently.

About
Syndication:

RSS ATOM