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?

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

Answer 2

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