Entity Framework中的“SELECT IN”未使用参数

Question

为什么在使用“SELECT IN”时，Entity Framework会将字面值放入生成的SQL中，而不是使用参数？

using (var context = new TestContext())
{
    var values = new int[] { 1, 2, 3 };
    var query = context.Things.Where(x => values.Contains(x.Id));

    Console.WriteLine(query.ToString());
}

这将生成以下SQL语句：

SELECT
    [Extent1].[Id] AS [Id]
    FROM [dbo].[PaymentConfigurations] AS [Extent1]
    WHERE [Extent1].[Id] IN (1, 2, 3)

我在SQL Server中看到了很多缓存的查询计划。是否有办法让EF放置参数而不是硬编码的值，或者激活参数嗅探是唯一的选项？

这也发生在EF Core中。

- Vlad

---

在引擎层面上，IN子句无法进行参数化。 - Alex K.

你为什么想要对生成的 SQL 进行参数化呢？当你按照 ORM 的意图使用时，通常的原因似乎并不适用。 - andrensairr

为了避免在in子句中的每个值组合上都有一个缓存的查询计划，可以考虑使用@ andrensairr。由于参数始终少于10个，因此我预计会缓存约10个查询计划，即每个参数数量一个……而不是实际监视器中看到的1500多个。 - Vlad

在引擎层面上，IN子句无法被参数化。@AlexK.，这是指哪个引擎？SQL Server绝对允许在IN子句中使用参数。 - mjwills

考虑使用“为即席工作优化”服务器配置选项 https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option?view=sql-server-2017 - Denis Rubashkin

LINQ-to-SQL做到了这一点。我认为它被放弃是因为一个查询中允许的最大参数数量（2100）。 - Gert Arnold

Answer 1

我无法说EF（Core）设计者为什么决定在翻译Enumerable.Contains时使用常量而不是变量。正如@Gert Arnold在评论中指出的，这可能与SQL查询参数计数限制有关。

有趣的是，当您使用等效的||表达式时，EF（6.2）和EF Core（2.1.2）都会生成带有参数的IN，例如：

var values = new int[] { 1, 2, 3 };
var value0 = values[0];
var value1 = values[1];
var value2 = values[2]; 
var query = context.Things.Where(x =>
    x.Id == value0 ||
    x.Id == value1 ||
    x.Id == value2);

EF6.2生成的查询语句为

SELECT
    [Extent1].[Id] AS [Id]
    FROM [dbo].[Things] AS [Extent1]
    WHERE [Extent1].[Id] IN (@p__linq__0,@p__linq__1,@p__linq__2)

EF Core 2.1执行了类似的操作。

因此，解决方案是将Contains表达式转换为基于||的表达式。它必须使用Expression类方法进行动态处理。为了更容易使用，可以封装在自定义扩展方法中，在其中使用ExpressionVisitor内部执行转换。

像这样：

public static partial class EfQueryableExtensions
{
    public static IQueryable<T> Parameterize<T>(this IQueryable<T> source)
    {
        var expression = new ContainsConverter().Visit(source.Expression);
        if (expression == source) return source;
        return source.Provider.CreateQuery<T>(expression);
    }

    class ContainsConverter : ExpressionVisitor
    {
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.DeclaringType == typeof(Enumerable) &&
                node.Method.Name == nameof(Enumerable.Contains) &&
                node.Arguments.Count == 2 &&
                CanEvaluate(node.Arguments[0]))
            {
                var values = Expression.Lambda<Func<IEnumerable>>(node.Arguments[0]).Compile().Invoke();
                var left = Visit(node.Arguments[1]);
                Expression result = null;
                foreach (var value in values)
                {
                    // var variable = new Tuple<TValue>(value);
                    var variable = Activator.CreateInstance(typeof(Tuple<>).MakeGenericType(left.Type), value);
                    // var right = variable.Item1;
                    var right = Expression.Property(Expression.Constant(variable), nameof(Tuple<int>.Item1));
                    var match = Expression.Equal(left, right);
                    result = result != null ? Expression.OrElse(result, match) : match;
                }
                return result ?? Expression.Constant(false);
            }
            return base.VisitMethodCall(node);
        }

        static bool CanEvaluate(Expression e)
        {
            if (e == null) return true;
            if (e.NodeType == ExpressionType.Convert)
                return CanEvaluate(((UnaryExpression)e).Operand);
            if (e.NodeType == ExpressionType.MemberAccess)
                return CanEvaluate(((MemberExpression)e).Expression);
            return e.NodeType == ExpressionType.Constant;
        }
    }
}

应用到示例查询

var values = new int[] { 1, 2, 3 };
var query = context.Things
    .Where(x => values.Contains(x.Id))
    .Parameterize();

产生所需的翻译。

- Ivan Stoev

---

太棒了（再次）！请注意，另一个关于“另一个”属性的||条件可以将生成的IN转换为常规的OR谓词链。但无论如何，它仍然是参数化的。 - Gert Arnold

抱歉，我遇到了一个错误：LINQ to Entities不认识方法“System.Linq.IQueryable1[EntityModel.tblParamNameProduct] Parameterize[tblParamNameProduct](System.Linq.IQueryable1[EntityModel.tblParamNameProduct])”，而且这个方法无法转换成存储表达式。可能是因为我需要在 .Parameterize() 之后继续使用另一个 .Select()。当我在 .Parameterize() 和另一个 .Select() 之间插入 .ToArray() 时，它可以完美地工作，谢谢。 - or hor

很好的一点是：查询使用参数化的IN(@p__linq__0,@p__linq__1,...)子句。 - or hor

@orhor 问题不在于 Parameterize() 调用后的额外 LINQ 操作符，而是该调用本身作为另一个查询表达式树的一部分，在这种情况下它根本没有被调用，而只是被记忆了，然后当然翻译器会将其报告为未知方法。它确实必须应用于顶层查询根，否则它将无法工作。最简单的方法是在最终查询上调用它 - 它不需要 DbSet，可以在任何可查询对象上使用，包括具有自定义投影 (Select) 等的对象。 - Ivan Stoev

Answer 2

IN查询可以使用参数化，虽然稍微有些绕。您需要使用直接的SQL查询，并手动生成参数化的SQL，例如以下内容：

var values = new object[] { 1, 2, 3 };
var idx = 0;
var query = context.Things.SqlQuery($@"
    SELECT
        [Extent1].[Id] AS [Id]
    FROM [dbo].[PaymentConfigurations] AS [Extent1]
    WHERE [Extent1].[Id] IN ({string.Join(",", values.Select(i => $"@p{idx++}"))})",
    values);

生成的参数名称列表直接嵌入到查询中使用的SQL中，并由“values”参数提供值。请注意，您可能需要确保您的“values”数组是一个“object []”，而不是“int []”，以确保它被解包到SqlQuery params中。这种方法不像LINQ查询那样易于维护，但有时为了效率我们不得不做出这些妥协。

- andrensairr

Answer 3

我建议你看一下LINQKit https://github.com/scottksmith95/LINQKit。

然后你的代码会是这样的：

using (var context = new TestContext())
{
    var values = new int[] { 1, 2, 3 };
    var predicate = PredicateBuilder.New<Thing>();
    foreach (var value in values)
    {
       predicate = predicate.Or(thing => thing.Id == value);
    }

    var query = context.Things.Where(predicate);

    Console.WriteLine(query.ToString());
}

你的SQL语句大致如下：

SELECT
    [Extent1].[Id] AS [Id]
FROM [dbo].[PaymentConfigurations] AS [Extent1]
WHERE [Extent1].[Id] = @__id_0 OR [Extent1].[Id] = @__id_1 OR [Extent1].[Id] = @__id_2

尽管它没有使用WHERE IN，但它使用相同的执行计划（在SQL Server 2019中测试过）。

- Makotosan