How Do You Implement Caching In Linq To Sql?

December 26, 2023 Post a Comment

We've just started using LINQ to SQL at work for our DAL & we haven't really come up with a standard for out caching model. Previously we had being using a base 'DAL' class th

Solution 1:

My LINQ query result cache is probably just what you're looking for.

var q = from c in context.Customers
        where c.City == "London"selectnew { c.Name, c.Phone };

var result = q.Take(10).FromCache();

Pete.

Solution 2:

A quick answer: Use the Repository pattern (see Domain Driven Design by Evans) to fetch your entities. Each repository will cache the things it will hold, ideally by letting each instance of the repository access a singleton cache (each thread/request will instantiate a new repository but there can be only one cache).

The above answer works on one machine only. To be able to use this on many machines, use memcached as your caching solution. Good luck!

Solution 3:

It's right under your nose:

List<TableItem> myResult = (from t in db.Table select t).ToList();

Now, just cache myResult as you would have cached your old DAL's returned data.

Solution 4:

I found this post, which offers an extension method as a means of caching the LINQ objects.

I've been banging my head against the wall for weaks now trying to figure out a good caching solution for Linq2SQL, an must admit that I'm really struggling to find a one-size fits all...

The repository pattern tends to limit the usefullness of Linq, since (without reimplementing IQueryable) caching must bge performed outside of Linq statement.

Moreover, deferred loading and object tracking are both big no-nos if you're going to cache your objects, which makes performing updates somewhat trickier.

Anyone who's managed to solve this problem in the wild within a highly concurrent web project, please chime in and save the world! :)

Solution 5:

I understand this is perhaps a bit late answer... Non the less, you can give a try to the LinqToCache project. It hooks a SqlDepdency on an arbitrary LINQ query, if possible, and provides active cache invalidation via the server side Query Notifications. The queries must be valid queries for notifications, see Creating a Query for Notification. Most Linq-to-sql queries conform to these restrictions, as long as the tables are specified using two-part names (dbo.Table, not only Table).

Lab Sql