none
Caching and LINQ to SQL-trying to minimize trips to the database. RRS feed

  • Question

  • Apologies if this is well-trod ground, but I've seen some informaiton on earlier betas out there, and I want to make sure that I'm getting the up to date info for the RTM vs2008 and .net 3.5. 

    I'm trying to figure out whether it's possible to have a LINQ to SQL mapping cache data, but be notified when that data changes so the cache can be invalidated.  I'm looking at a clustered data access layer scenario, and I'd like to try to minimize the number of trips to and from the database.  I understand that SQL 2005 supports change notifications-is there native support for these notifications in LINQ to SQL?  Is there anything else that LINQ changes with respect to caching SQL server data, or are things pretty much the same as they were with .net 2.0?  I've never needed to set up much with SQL and 2.0, and since LINQ is already making life a whole lot easier from the data access persective, if caching is also improved I'd really like to take advantage of it. 

    Thanks!

    Chris
    Wednesday, March 19, 2008 4:07 PM

Answers

  • Philipp's description of how LINQ to SQL discards entities that are already loaded is accurate. LINQ to SQL requires that you explicitly invoke DataContext.Refresh() in order to update entities in the cache from the database.

    Entity Framework has a somewhat different model in which you can change the MergeOptions of your queries in order to obtain different behaviors.

    Regarding mechanisms for cache invalidation, LINQ to SQL doesn't add anything that I am aware of. However, I can imagine it shouldn't be too hard to take the resulting T-SQL query to set standard change notifications. Then every time the cache needs to be invalidated you could spin a new DataContext. I don’t think Refresh would perform well, since it would probably execute a separate query for each entity.

    All that said, I haven’t tried this myself and so there could be a scenario blocker I am not aware of.

    Hope this helps,
    Diego

    Wednesday, May 21, 2008 9:36 AM

All replies

  • Hi Chris,

     

    I never heard that Linq to SQL implemented something that uses the notification services.

    From my understanding Linq doesn't care about changes in host data until you try to SubmitChanges(). As far as I remember, I read in a blog entry (sorry, I forget where) that even if you read the data again from the database Linq discarded every database row which is already in its cache. They explained that this is done to preserve changes already made to those objects in cache.

    Maybe Linq to Entities uses another approach.

     

    regards

    Philipp

    Wednesday, March 19, 2008 5:41 PM
  • Philipp's description of how LINQ to SQL discards entities that are already loaded is accurate. LINQ to SQL requires that you explicitly invoke DataContext.Refresh() in order to update entities in the cache from the database.

    Entity Framework has a somewhat different model in which you can change the MergeOptions of your queries in order to obtain different behaviors.

    Regarding mechanisms for cache invalidation, LINQ to SQL doesn't add anything that I am aware of. However, I can imagine it shouldn't be too hard to take the resulting T-SQL query to set standard change notifications. Then every time the cache needs to be invalidated you could spin a new DataContext. I don’t think Refresh would perform well, since it would probably execute a separate query for each entity.

    All that said, I haven’t tried this myself and so there could be a scenario blocker I am not aware of.

    Hope this helps,
    Diego

    Wednesday, May 21, 2008 9:36 AM