且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

重写LINQ Expression查询以启用缓存SQL执行计划

更新时间:2023-01-28 16:54:07

经过反复试验,我们发现您仍然可以通过稍微改变传入的方式来强制Entity Framework将convertedId识别为参数:

After a lot of trial and error, we found you can still force Entity Framework to recognise convertedId as a parameter by slightly changing how we pass it in:

....

var convObj = new
{
    id = convertedId
};
var rightExp = Expression.Convert(Expression.Property(Expression.Constant(convObj), "id"), convertedId.GetType());

var whereExpression = Expression.Lambda<Func<T, bool>>
    (
    Expression.Equal(
        Expression.Property(
            itemParameter,
            prop.Name
            ),
        rightExp
        ),
    new[] { itemParameter }
    );

return Get<T>().Where(whereExpression);

这会导致生成的SQL对任何给定的id使用相同的参数(和代码):

Which causes the generated SQL to use the same parameter (and code) for any given id:

WHERE [Extent1].[Id] = @p__linq__0 

我们正在处理的问题查询花费了很长时间才能生成执行计划,因此我们发现访问新ID的执行时间显着减少(从3到4秒减少到300毫秒)

The query in question that we were dealing with takes a long time to generate the execution plan, so we saw a significant decrease in execution time for accessing new IDs (from 3~4 seconds down to ~300 milliseconds)