Caching LINQ expressions by equality

Question

Consider this scenario.

You have a repository that allows certain calls to be made on it. These calls use LINQ and could be relatively expensive in terms of the amount of data returned.

Given that in my case, it's not overly bad if the data is old - one could implement a cache, so that the large and expensive query wasn't executed every call. Hey, we could even implement some caching policies to determine when to execute that query again based on time or useage.

The thing I'm trying to wrap my head around, is how to key that in a cache. One way would be to simply say:

"querytype1" = Particular LINQ expression
"querytype2" = Particular LINQ expression

And then key the cache by a simple string. But, given we're doing LINQ, could we potentially key the cache by the LINQ expression itself? I understand the performance indications but is there anyway to compare whether two LINQ expressions are the same?

How would you compare the LINQ expression? Does the object implement IComparable? — Kyle C, Apr 18 '13 at 20:13
@KyleC That's my question. Is it possible to find out if two LINQ expressions are semantically the same (irrespective of the result set) — Moo-Juice, Apr 18 '13 at 20:16
You may want to look at the Lazy class and pattern: http://msdn.microsoft.com/en-us/library/dd642331.aspx — Dan Esparza, Apr 18 '13 at 20:33

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

Strategy: Compare SQL output

One strategy might be to retrieve the rendered SQL text + parameters and determine if they are the same as the rendered SQL + parameters from another IQueryable.

See Retrieve LINQ to sql statement (IQueryable) WITH parameters for more information.

edited May 23 '17 at 11:50

Community

1
1

answered Apr 18 '13 at 20:17

Dan Esparza

28,047
29
99
127

If you look at many of some of my answers to other questions, it will come as a surprise when I ask about the performance implications. Alas, the data sets we're talking about could be incredibly big, and the `SELECT` statement itself will be huge (alas, this pulls things in from multiple third-party solutions, so I can't really make the query smaller). This would potentially put the key in to the "1kb+" range of comparison. Thought, in truth, this would be less expensive (by an order of magnitude) than requerying. I guess I was wondering if we could *hash* a LINQ expression. – Moo-Juice Apr 18 '13 at 20:24
You could hash the key and look for collisions. – Joey Gennari Apr 18 '13 at 20:33

score 1 · Accepted Answer · edited May 23 '17 at 12:21

This is my current solution to this.

Given that, in a repository, we're going to have calls resembling:

public IEnumerable<MYPOCO> GetData(string someParameter, int anotherParameter);

We can therefore say that these parameters are criteria. So, I introduced a Criteria class, which basically contains Dictionary<string, object> instance, and has some type-safe setters and getters, simplified:

public class Criteria
{
    private Dictionary<string, object> _criteria = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);

    public Criteria Set<T>(string key, T value)
    {
        _criteria[key] = value;
        return this;
    } // eo Set

    public T Get<T>(string key)
    {
        return _criteria.ContainsKey(key) ? _criteria[key] : default(T);
    } // eo Get

    public Dictionary<string, object> Items { get { return _criteria; } }
}    // eo class Criteria

Then, I wrote an extension method for Dictionary<TK, TV>, based on this Stackoverflow answer. And finally, a IEqualityComparer<Criteria> class that works with types of Criteria.

This means my cache is now keyed by the criteria, which is set taking the parameters that were passed in to the repository:

public class MyPocoRepository<TMYPOCO>
{
    private Cache<Criteria, IEnumerable<TMYPOCO>> _cache = new Cache<Criteria, IEnumerable<TMYPOCO>>(CriteriaComparer); // is passed to the dictionary constructor which is actually the cache.
    public IEnumerable<TMYPOCO> GetData(string someParameter, int anotherParameter)
    {
        Criteria criteria = new Criteria();
        criteria.Set("someParameter", someParameter)
                .Set("anotherParameter", anotherParameter);
        // we can check the cache now based on this...
    } // eo GetData
} // eo MyPocoRepository<TMYPOCO>

Note that this also allows me to extend this idea when we want caching-strategies where the parameters are exactly the same, but perhaps a different user account is accessing it (we can add a field, say - the user-type, to the criteria, even if the LINQ expression isn't going to use it).

you need to override `Equals` and `GetHashCode` if you want to store instances as keys in a `Dictionary` — Charles Lambert, May 07 '13 at 19:35
@CharlesLambert, that'll teach me not to have the code in front of me when I wrote the post. Please see edit, it's `IEqualityComparer<>` — Moo-Juice, May 07 '13 at 19:36

Caching LINQ expressions by equality

2 Answers2