Dynamically generated lookup key for IQueryable

问题:

I have the following simple cache class that I want to use to cache items frequently read from the database.

public class Cache<TKey, TElement>
    where TElement : class
{
    private Dictionary<TKey, TElement> cache = new Dictionary<TKey, TElement>();
    private Func<TElement, TKey> dbKey;

    public Cache(Func<TElement, TKey> dbKey)
    {
        this.dbKey = dbKey;
    }

    public TElement Get(TKey key, DataContext db)
    {
        if (cache.ContainsKey(key)) return cache[key];

        // This line throws exception.  See below
        var value = db.GetTable<TElement>().SingleOrDefault(x => dbKey(x).Equals(key));

        if (value != null) cache[key] = value;
        return value;
    }
}

The usage looks like this:

var db = new DataContext();
var userCache = new Cache<string, User>(u => u.Username);
userCache.Get("dave", db);

However, the indicated line throws a NotSupportedException with the message "Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL." I understand why the exception is being thrown, but I'm not sure how to resolve it.

The cache is intended to operate in front of a number of different db tables, with different columns being used for the lookup key. My thought was to pass in a lambda to specify the lookup column, as you can see in the code. This works for IEnumerable, but not IQueryable.

Is there another way to achieve this in LINQ to SQL?

回答1:

You have to use Expression Tree, not delegates and lambdas.

1. Change your constructor parameter type and field type to Expression<Func<TElement, TKey>>:

   private Expression<Func<TElement, TKey>> dbKey;
   
   public Cache(Expression<Func<TElement, TKey>> dbKey)
   {
       this.dbKey = dbKey;
   }
   

   Thanks to compiler which automatically generates Expression Tree from lambda expression at compile time, you can still call it like before:

   var userCache = new Cache<string, User>(u => u.Username);
   

2. Add private helper method to combine key selector expression with equality check:

   private Expression<Func<TElement, bool>> GetConditionExpression(TKey key)
   {
       var param = Expression.Parameter(typeof(TElement));
   
       return
           Expression.Lambda<Func<TElement, bool>>(
               Expression.Equal(
                   Expression.Invoke(
                       dbKey,
                       param
                   ),
                   Expression.Constant(key)
               ),
           param
           );
   }
   

3. Call that method to get proper Expression for SingleOrDefault:

   var value = db.GetTable<TElement>().SingleOrDefault(GetConditionExpression(key));
   

I can't test it right now with real DB, but should work. If not, at least should point you into right dirrection.