动态生成的查找密钥[英] Dynamically generated lookup key for IQueryable

本文是小编为大家收集整理的关于动态生成的查找密钥的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有以下简单的缓存类,我想用来从数据库中读取缓存项.

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;
    }
}

用法看起来像这样:

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

然而,指示的线通过消息" method'system.Object dynamicinvoke(system.Object [])抛出了一个notsupportedException'没有支持转换为SQL."我了解为什么要抛出例外情况,但我不确定如何解决.

缓存旨在在许多不同的DB表前操作,其中不同的列用于查找键.我的想法是通过lambda传递以指定查找列,如您在代码中所见.这适用于iEnumerable,但不可用.

是否有另一种方法可以在linq到SQL?

实现此目标

推荐答案

您必须使用表达树,而不是委托和lambdas.

  1. 将您的构造函数参数类型和字段类型更改为Expression<Func<TElement, TKey>>:

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

    感谢编译器在编译时自动从lambda表达式生成表达树,您仍然可以像以前一样称呼它:

    var userCache = new Cache<string, User>(u => u.Username);
    
  2. 添加私人辅助方法将关键选择器表达式与平等检查相结合:

    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. 调用该方法以获取SingleOrDefault的正确表达方式:

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

我现在无法使用Real DB进行测试,但应该起作用.如果没有,至少应该指向您正确的dirrection .

本文地址:https://www.itbaoku.cn/post/1556810.html

问题描述

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?

推荐答案

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.