Entity Framework "SELECT IN" not using parameters

Question

Why is Entity Framework putting literal values in generated SQL rather than using parameters when using a "SELECT IN":

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

This produces the following SQL:

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

I see a lot of cached query plans in SQL Server. Is there anyway of making EF to put parameters rather than harcoded values, or activating parameter sniffing is the only option?

It happens also in EF Core.

Answer 1

I can't say why EF (Core) designers decided to use constants instead of variables when translating Enumerable.Contains. As @Gert Arnold pointed out in the comments, could be related to SQL query parameter count limit.

Interestingly, both EF (6.2) and EF Core (2.1.2) generate IN with parameters when you use the equivalent || expression like:

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 generated query is

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 does something similar.

So the solution is to convert the Contains expression to || based expression. It has to be dynamically using Expression class methods. And to make it easier to use, could be encapsulated in a custom extension method, which internally user ExpressionVisitor to perform the conversion.

Something like this:

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

Applying it to the sample query

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

produces the desired translation.

Answer 2

Parameterised queries with IN can be done, though it's a little round-about. You will need to use a direct SQL query, and generate the parameterised SQL manually, something like the following:

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

The generated list of parameter names are directly embedded into the SQL used in the query, and are provided values by the values parameter. Note that you may need to ensure that your values array is an object[] and not int[] to ensure it's unpacked into the SqlQuery params. This approach is not so easy to maintain as a LINQ query, however sometimes we have to make these compromises for the sake of efficiency.