efcore: IN() list queries are not parameterized, causing increased SQL Server CPU usage

Everyone,

We spent some time investigating this; below is a write-up of the current situation, the possible ways forward and some first conclusions. The tl;dr is that we think it makes sense to implement an OPENJSON-based approach, where the (array parameter (e.g. int[]) is transmitted as a JSON string parameter (`[1,2,3]') and unpacked to a pseudo-table, which we can then be used in the SQL query as usual. As many people have shown an interest here, it would be good to receive feedback on the below!

The problem

It’s very common to filter rows based on an array of (primitive) values that exists on the client. In an EF LINQ query, this looks like the following:

var ids = new[] { 10, 20, 30 };
var customers = await context.Customers.Where(c => ids.Contains(c.Id)).ToArray();

This conceptually translates to a SQL IN expression. However, since array parameters aren’t supported by most databases, EF unpacks the array into the SQL, currently inserting its values as constants:

SELECT * FROM [Customers] AS [c]
WHERE [c].[Id] IN (10, 20, 30);

This technique unfortunately means that different array values cause different SQL to get generated. This can cause severe performance problems, since it pollutes database query plans, causing re-planning for each execution and evicts plans for other queries. Less importantly, EF cannot (currently) cache the SQL, as it depends on parameter values; this means some query processing must occur for each execution.

In the benchmark below, this technique is called In_with_constants.

IN expression with parameters

To improve this, we could simply embed the array values as parameters instead of constants:

DECLARE @id1 INT = 10, @id2 INT = 20, @id3 INT = 30;

SELECT * FROM [Customers] AS [c]
WHERE [c].[Id] IN (@id1, @id2, @id3);

For all arrays of identical lengths (3 in our case), this produces the same SQL and is therefore optimal. However, varying array lengths still cause differing SQLs, triggering the performance issues. Also, EF still cannot cache the SQL.

In the benchmark below, this technique is called In_with_padded_parameters with PaddingCount = 0.

Padding

A common way to alleviate the differing length problem is to “pad” the IN expression with extra “useless” parameters, up to an arbitrary boundary. For example, the following pads our 3-value array to 5 values:

DECLARE @id1 INT = 10, @id2 INT = 20, @id3 INT = 30, @i4 INT = 30, @i5 INT = 30;

SELECT * FROM [Customers] AS [c]
WHERE [c].[Id] IN (@id1, @id2, @id3, @id4, @id5);

Array lengths of 5 and below therefore use the same SQL, at the cost of sending duplicated data to the server. The bigger the padding boundary, the less different SQLs are produced for different array lengths, but the more padding parameters are added on the average, increasing query overhead.

This padding technique has a long history and can be used with various ORMs (nHibernate, jooq, Dapper), though it sometimes requires an opt-in.

In the benchmark below, this technique is called In_with_padded_parameters with PaddingCount > 0.

Sending the values as an array

Rather than expanding an array parameter out to individual values in a SQL IN expression (whether as constants or as parameters), ideally we’d be able to just send the array parameter directly. Doing this directly usually isn’t supported, since databases don’t typically support array types in any way. The two exceptions to this are:

• PostgreSQL, which has native support for arrays. The EF PostgreSQL provider already translates LINQ Contains to WHERE x = ANY (@arrayParam), where @arrayParam is a simply .NET array. This yields optimal performance with optimal simplicity.
• SQL Server, which supports table-valued parameters or TVPs, which allows sending an array of arbitrary user-defined types. However, this requires pre-creation of the user-defined type (via CREATE TYPE), making it generally unsuitable for EF, and does not perform well (see Temporary_table_with_inner_join in the benchmark below).

However, all modern databases support parsing a JSON string representing an array and evaluating it as a relational resultset (e.g. pseudo-table); this allows us to send a simple string parameter containing e.g. [10,20,30], and use a table-valued SQL function to extract the values out as a table with 3 rows containing those values. Once that’s done, regular SQL can be used to implements the Contains logic. The following uses an inner join to filter out only customers with IDs matching the incoming JSON array (this technique is Array_param_with_inner_join in the benchmarks):

DECLARE @ids NVARCHAR(MAX) = '[10,20,30]';

SELECT * FROM [Customers] AS [c]
JOIN OpenJson(@ids) WITH ([Value] integer '$') AS v ON v.Value = [c].[Id];

A subquery-based approach was also benchmarked (Array_param_with_exists_subquery):

DECLARE @ids NVARCHAR(MAX) = '[10,20,30]';

SELECT * FROM [Customers] AS [c]
WHERE EXISTS (
    SELECT 1
    FROM OpenJson(@ids) WITH ([Value] integer '$') AS [v]
    WHERE v.[Value] = [c].[Id]);

This technique has the following advantages compared to the expansion-based techniques discussed above:

• Only one SQL is every needed, regardless of the values or the number. This is optimal both for the database server (single plan), and for EF (it can cache the SQL).
• Since no parameter padding is needed, no extra parameter data ever needs to be sent.

Links to the JSON-unpacking functions in the different databases:

• SQL Server has OPENJSON. This function is only available with compatibility level 130 or higher (Azure SQL Database and SQL Server 2016 and later; the previous version - SQL Server 2014 goes out of support in 2024). An alternative OPENXML also exists with better compatibility, but performs significantly worse.
• SQLite has json_each.
• MySQL and MariaDB have json_table.
• PostgreSQL has jsonb_to_recordset, though it isn’t required thanks to the native support for arrays.
• Oracle has JSON_TABLE.

Benchmark

using System;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Text.Json;
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;
using Npgsql;

BenchmarkRunner.Run<Benchmark>();

// Uncomment the following for quick testing that all tests technically work
// [SimpleJob(warmupCount: 1, targetCount: 1, invocationCount: 1)]
public class Benchmark
{
    // [Params(Database.SQLServer, Database.PostgreSQL)]
    [Params(Database.SQLServer)]
    public Database Database { get; set; }

    [Params(2, 10, 100, 1000)]
    public int NumSearchValues { get; set; }

    [Params(true, false)]
    public bool Found { get; set; }

    private int[] _searchValues = null!;

    private const int TotalRows = 1000;

    [Params(0, 5, 10, 50, 100, 500)]
    public int PaddingCount { get; set; }

    private DbConnection _connection = null!;
    private DbCommand _command = null!;

    public void CommonSetup()
    {
        _connection = Database switch
        {
            Database.SQLServer => new SqlConnection("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false"),
            Database.PostgreSQL => new NpgsqlConnection("Host=localhost;Username=test;Password=test;SSL Mode=disable"),
            _ => throw new UnreachableException()
        };
        _connection.Open();
        _command = _connection.CreateCommand();

        _connection.Execute("DROP TABLE IF EXISTS data");
        _connection.Execute("CREATE TABLE data (id INTEGER, int INTEGER)");
        _connection.Execute("CREATE INDEX IX_data ON data(int)");

        var builder = new StringBuilder("INSERT INTO data (id, int) VALUES");
        for (var i = 0; i < TotalRows; i++)
        {
            if (i > 0)
                builder.Append(",");
            builder.Append($" ({i}, {i + 100})");
        }

        _command.CommandText = builder.ToString();
        Console.WriteLine("Seed SQL: " + builder);
        _connection.Execute(builder.ToString());

        _searchValues = new int[NumSearchValues];
        for (var i = 0; i < _searchValues.Length; i++)
        {
            _searchValues[i] = Random.Shared.Next(TotalRows) + (Found ? 0 : 1000);
        }
    }

    [GlobalSetup(Target = nameof(In_with_constants))]
    public void In_with_constants_setup()
    {
        if (PaddingCount != 0)
            throw new NotSupportedException();

        CommonSetup();

        var builder = new StringBuilder("SELECT id FROM data WHERE int IN (");
        for (var i = 0; i < _searchValues.Length; i++)
        {
            if (i > 0)
                builder.Append(",");
            builder.Append(_searchValues[i]);
        }

        builder.Append(")");
        _command.CommandText = builder.ToString();
        Console.WriteLine("Benchmark query: " + _command.CommandText);
    }

    [GlobalSetup(Target = nameof(PG_any_with_parameter))]
    public void PG_any_with_parameter_setup()
    {
        if (Database != Database.PostgreSQL || PaddingCount != 0)
            throw new NotSupportedException();

        CommonSetup();

        _command.CommandText = "SELECT id FROM data WHERE int = ANY($1)";
        var p = _command.CreateParameter();
        p.Value = _searchValues;
        _command.Parameters.Add(p);

        Console.WriteLine("Benchmark query: " + _command.CommandText);
    }


    [GlobalSetup(Target = nameof(In_with_padded_parameters))]
    public void In_with_padded_parameters_setup()
    {
        CommonSetup();

        var builder = new StringBuilder("SELECT id FROM data WHERE int IN (");
        for (var i = 0; i < _searchValues.Length + PaddingCount; i++)
        {
            if (i > 0)
                builder.Append(",");

            var searchValue = i < _searchValues.Length ? _searchValues[i] : _searchValues[^1];

            switch (Database)
            {
                case Database.SQLServer:
                    var parameterName = $"@p{i + 1}";
                    builder.Append(parameterName);
                    _command.Parameters.Add(
                        new SqlParameter { ParameterName = parameterName, Value = searchValue });
                    break;
                case Database.PostgreSQL:
                    builder.Append($"${i + 1}");
                    _command.Parameters.Add(new NpgsqlParameter { Value = searchValue });
                    break;
                default:
                    throw new ArgumentOutOfRangeException();
            }
        }

        if (Database == Database.SQLServer)
        {
            ((SqlCommand)_command).EnableOptimizedParameterBinding = true;
        }

        builder.Append(")");
        _command.CommandText = builder.ToString();
        Console.WriteLine("Benchmark query: " + _command.CommandText);
    }

    [GlobalSetup(Target = nameof(Array_param_with_exists_subquery))]
    public void Array_param_with_exists_subquery_setup()
    {
        if (PaddingCount != 0)
            throw new NotSupportedException();
        CommonSetup();

        switch (Database)
        {
            case Database.SQLServer:
                _command.CommandText =
"""
SELECT id FROM data AS d WHERE EXISTS (
    SELECT 1
    FROM OpenJson(@p) WITH ([Value] integer '$') AS v
    WHERE v.Value = d.int)
""";
                _command.Parameters.Add(new SqlParameter("p", JsonSerializer.Serialize(_searchValues)));
                break;
            case Database.PostgreSQL:
                _command.CommandText =
"""
SELECT id FROM data AS d WHERE EXISTS (
    SELECT 1
    FROM unnest($1) AS v
    WHERE v.v = d.int)
""";
                _command.Parameters.Add(new NpgsqlParameter { Value = _searchValues });
                break;
        }

        Console.WriteLine("Benchmark query: " + _command.CommandText);
    }

    [GlobalSetup(Target = nameof(Array_param_with_inner_join))]
    public void Array_param_with_inner_join_setup()
    {
        if (PaddingCount != 0)
            throw new NotSupportedException();
        CommonSetup();

        switch (Database)
        {
            case Database.SQLServer:
                _command.CommandText =
"""
SELECT id FROM data AS d
JOIN OpenJson(@p) WITH ([Value] integer '$') AS v ON v.Value = d.int;
""";
                _command.Parameters.Add(new SqlParameter("p", JsonSerializer.Serialize(_searchValues)));
                break;
            case Database.PostgreSQL:
                _command.CommandText =
"""
SELECT id FROM data AS d
JOIN unnest($1) AS v ON v.v = d.int;
""";
                _command.Parameters.Add(new NpgsqlParameter { Value = _searchValues });
                break;
        }

        Console.WriteLine("Benchmark query: " + _command.CommandText);
    }

    [GlobalSetup(Target = nameof(OpenXml_with_inner_join))]
    public void OpenXml_with_inner_join_setup()
    {
        if (Database != Database.SQLServer || PaddingCount != 0)
            throw new NotSupportedException();
        CommonSetup();

        _command.CommandText =
"""
DECLARE @idoc INT;
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
SELECT id FROM data AS d
JOIN OpenXML(@idoc, '/Root/Value', 1) WITH ([value] integer) AS v ON v.Value = d.int;
EXEC sp_xml_removedocument @idoc;
""";
        var builder = new StringBuilder("<Root>");
        for (var i = 0; i < _searchValues.Length; i++)
            builder.Append($@"<Value value=""{_searchValues[i]}""/>");
        builder.Append("</Root>");

        _command.Parameters.Add(new SqlParameter("doc", builder.ToString()));

        Console.WriteLine("Benchmark query: " + _command.CommandText);
    }

    [GlobalSetup(Target = nameof(Temporary_table_with_inner_join))]
    public void Temporary_table_with_inner_join_setup()
    {
        if (Database != Database.SQLServer || PaddingCount != 0)
            throw new NotSupportedException();
        CommonSetup();

        // Note: this creates the TVP's type once at setup. This is because it's not possible to batch the creation
        // of the type and its use in the same SqlCommand, so that would require multiple roundtrips (better batching
        // via TDS RpcMessage would like make this work).
        // So for now, we're creating it just once, although that isn't usable by EF.
        _command.CommandText =
"""
DROP TYPE IF EXISTS int_wrapper;
CREATE TYPE int_wrapper AS TABLE (int INT);
""";
        _command.ExecuteNonQuery();

        // Note that we also create the SqlDataRecords only once at setup, which is also unrealistic and makes TVP
        // look much better than it is.
        var records = _searchValues.Select(i =>
        {
            var record = new SqlDataRecord(new SqlMetaData("int", SqlDbType.Int));
            record.SetInt32(0, i);
            return record;
        }).ToArray();

        _command.CommandText = "SELECT id FROM data AS d JOIN @ints AS ints ON ints.int = d.int";
        _command.Parameters.Add(new SqlParameter("ints", SqlDbType.Structured)
        {
            TypeName = "int_wrapper",
            Value = records
        });

        Console.WriteLine("Benchmark query: " + _command.CommandText);
    }

    [Benchmark]
    public void In_with_constants() => _command.ExecuteNonQuery();

    [Benchmark]
    public void PG_any_with_parameter() => _command.ExecuteNonQuery();

    [Benchmark]
    public void In_with_padded_parameters() => _command.ExecuteNonQuery();

    [Benchmark]
    public void Array_param_with_exists_subquery() => _command.ExecuteNonQuery();

    [Benchmark]
    public void Array_param_with_inner_join() => _command.ExecuteNonQuery();

    [Benchmark]
    public void OpenXml_with_inner_join() => _command.ExecuteNonQuery();

    [Benchmark]
    public void Temporary_table_with_inner_join() => _command.ExecuteNonQuery();

    [GlobalCleanup]
    public void Cleanup()
    {
        _command.Dispose();
        _connection.Dispose();
    }
}

public enum Database
{
    SQLServer,
    PostgreSQL
}

Observations:

• In_with_constants (the current EF technique) is included only for completeness. Although it is always the fastest technique, this does not represent actual performance in a real application; since the benchmark repeatedly executes the same query, the performance issues related to constantly changing SQL don’t manifest.
• Table-valued parameters perform surprisingly badly; this benchmarks creates the TVP type once at setup, and even caches the SqlDataRecord and SqlMetaData instances.
• There are some anomalous results for increasing parameter padding values
• For smaller value counts, In_with_padded_parameters is faster than the OPENJSON-based techniques (Array_param_with_inner_join, Array_param_with_exists_subquery). However, In_with_padded_parameters time grows much faster as the number of values increases, making OPENJSON better in the general case, with arbitrary value counts.

Summary

• Unfortunately, there isn’t one technique which performs best in all scenarios; for smaller value counts (< 15), padded parameters performs better, whereas with larger ones OPENJSON performs better.
• Ideally, a hybrid approach would be implemented, using parameter padding in lower counts and automatically switching to OPENJSON as counts increase. However, EF’s query and caching architecture makes this non-trivial.
• Since OPENJSON scales better in general scenario (arbitrary value numbers), and since we want to unlock arbitrary querying of OPENJSON-based array parameters (#30426), we think it makes sense to start with the OPENJSON-based implementation. Then, we can look at adding parameterized IN with padding as an optimization for lower value counts.
• Note that OPENJSON can’t always be used (older SQL Server versions, spatial types are incompatible with it). For those cases we’ll currently revert back to the current behavior (IN with constants). We may improve this in the future to IN with parameters.

I wrote an extension to EF6 to handle this exact problem for our product and apparently it is extremely similar to what Nick is proposing above. By replacing this where clause: .Where(entity => myEnumerable.Contains(entity.prop)) with this extension method: .BatchedWhereKeyInValues(entity => entity.prop, myEnumerables, batchSize: 100), my solution will break the list of values into batches, and then produce a properly parameterized query that generates the desired SQL.

For example, this expression: query.BatchedWhereKeyInValues(q => q.Id, values: {1,2,5,7,8,9}, batchSize: 3)

would effectively become the following two queryables, where the numbers are supplied as variables that allow EF to parameterize the query rather than embed them as constants: query.Where(q => q.Id == 1 || q.Id == 2 || q.Id == 5) query.Where(q => q.Id == 7 || q.Id == 8 || q.Id == 9)

EF then converts those LINQ expressions into this SQL expression: WHERE Id IN (@p__linq__0, @p__linq__1, @p__linq__2)

Check out the source here: https://gist.github.com/kroymann/e57b3b4f30e6056a3465dbf118e5f13d

Here’s an alternative solution I discovered thanks to a semi-related SO post by @davidbaxterbrowne:

var records = await _context.Items
	.AsNoTracking()
	.FromSql(
		"SELECT * FROM dbo.Items WHERE Id IN (SELECT value FROM OPENJSON({0}))",
		JsonConvert.SerializeObject(itemIds.ToList()))
	.ToListAsync();

It’s specific to SQL Server 2017+ (or Azure SQL Server), and in my testing with a list of 50,000 IDs against a remote Azure SQL database it was 5x faster than a standard batching solution (dividing into lists of 1,000 IDs).

@kroymann Works like a charm! Big Thanks!

public static class QueryableExtension<TQuery>
    {
        internal static IEnumerable<IQueryable<TQuery>> WhereIn<TKey>(IQueryable<TQuery> queryable,
            Expression<Func<TQuery, TKey>> keySelector, IEnumerable<TKey> values, int batchSize)
        {
            List<TKey> distinctValues = values.Distinct().ToList();
            int lastBatchSize = distinctValues.Count % batchSize;
            if (lastBatchSize != 0)
            {
                distinctValues.AddRange(Enumerable.Repeat(distinctValues.Last(), batchSize - lastBatchSize));
            }

            int count = distinctValues.Count;
            for (int i = 0; i < count; i += batchSize)
            {
                var body = distinctValues
                    .Skip(i)
                    .Take(batchSize)
                    .Select(v =>
                    {
                        // Create an expression that captures the variable so EF can turn this into a parameterized SQL query
                        Expression<Func<TKey>> valueAsExpression = () => v;
                        return Expression.Equal(keySelector.Body, valueAsExpression.Body);
                    })
                    .Aggregate((a, b) => Expression.OrElse(a, b));
                if (body == null)
                {
                    yield break;
                }

                var whereClause = Expression.Lambda<Func<TQuery, bool>>(body, keySelector.Parameters);
                yield return queryable.Where(whereClause);
            }
        }

// doesn't use batching
        internal static IQueryable<TQuery> WhereIn<TKey>(IQueryable<TQuery> queryable,
            Expression<Func<TQuery, TKey>> keySelector, IEnumerable<TKey> values)
        {
            TKey[] distinctValues = values.Distinct().ToArray();


            int count = distinctValues.Length;
            for (int i = 0; i < count; ++i)
            {
                var body = distinctValues
                    .Select(v =>
                    {
                        // Create an expression that captures the variable so EF can turn this into a parameterized SQL query
                        Expression<Func<TKey>> valueAsExpression = () => v;
                        return Expression.Equal(keySelector.Body, valueAsExpression.Body);
                    })
                    .Aggregate((a, b) => Expression.OrElse(a, b));

                var whereClause = Expression.Lambda<Func<TQuery, bool>>(body, keySelector.Parameters);
                return queryable.Where(whereClause);
            }

            return Enumerable.Empty<TQuery>().AsQueryable();
        }
    }

Usage:

int[] a = Enumerable.Range(1, 10).ToArray();
var queries = QueryableExtension<User>.WhereIn(dbContext.Users, t => t.Id, a, 5);
foreach (var queryable in queries)
{
    _ = queryable.ToArray();
}

var everything = QueryableExtension<User>.WhereIn(dbContext.Users, t => t.Id, a);
_ = everything.ToArray(); 

@yv989c great to hear! In my benchmark above, XML performed worse than than the current EF technique (expansion to constants), but I may have not optimized that technique as much as I should have. In any case, I think that at the point where we are, simply supporting JSON and allowing an opt-out back to the current technique for old SQL Server versions is good enough.

I’m currently actively working on this, as part of a much larger change that should allow fully queryable parameters, constants and even columns across different providers. Stay tuned.

I have updated one of @divega 's proposals above for 3.1: https://erikej.github.io/efcore/sqlserver/2020/03/30/ef-core-cache-pollution.html

It is good to see that “advanced” solutions have been proposed here. Is it feasible to create a rather simple but quick fix first? Simply making the list a bunch of parameters (e.g. @p0, @p1, ...) would be much better than the literals. That way there’s one plan for each list count in contrast to one plan for each set of constants.

Also such queries don’t aggregate well in logging systems like Application Insights, cause every query has unique sql.

@mrlife unfortunately not - this is quite a non-trivial optimization, and we’re now in a feature freeze, stabilizing the release. I’m very hopeful we’ll be able to tackle this for 7.0.

Been investigating solutions in the meantime and found that the string_split function idea from above can be separated out into its own queryable for flexible reuse. For example…

Add keyless entity to your model:

public class StringSplitResult
{
    public string Value { get; set; }
}

Some methods like this on your context:

public IQueryable<string> AsQuery(IEnumerable<string> values) =>
    SplitString(string.Join(",", values));

public IQueryable<string> SplitString(string values, string separator = ",") =>
    Set<StringSplitResult>()
        .FromSqlInterpolated($"SELECT Value FROM string_split({values}, {separator})")
        .Select(x => x.Value);

You could do something like this:

var codes = new[] {"CODE1", "CODE2"};
var item = context.Set<Entity>()
    .Where(entity => context.AsQuery(codes).Contains(entity.Code))
    .FirstOrDefault();

Which produces SQL:

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (21ms) [Parameters=[p0='CODE1,CODE2' (Size = 4000), p1=',' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [e].[Id], [e].[Code]
FROM [Entities] AS [e]
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT Value FROM string_split(@p0, @p1)
    ) AS [s]
    WHERE ([s].[Value] = [e].[Code]) OR ([s].[Value] IS NULL AND [e].[Code] IS NULL))

@snappyfoo we’re still working out the mechanics in our planning process and the Github project board. The important thing is that this issue has the consider-for-next-release label, which means it’s definitely on the table for 6.0.

I just wanted to share here some lexperiments I did some time ago with different alternative approaches to Enumerable.Contains using existing EF Core APIs:

https://github.com/divega/ContainsOptimization/

The goal was both to find possible workarounds, and to explore how we could deal with Contains in the future.

I timed the initial construction and first execution of the query with different approaches. This isn’t a representative benchmark because there is no data and it doesn’t measure the impact of caching, but I think it is still interesting to see the perf sensitivity to the number of parameters.

The code tests 4 approaches:

Test("standard Contains", context.People.Where(p => (ids.Contains(p.Id))));

Test("Parameter rewrite", context.People.In(ids, p => p.Id));

Test("Split function", 
    context.People.FromSql(
        $"select * from People p where p.Id in(select value from string_split({string.Join(",", ids)}, ','))"));

Test("table-valued parameter", 
    context.People.FromSql(
        $"select * from People p where p.Id in(select * from {context.CreateTableValuedParameter(ids, "@p0")})"));

Standard Contains

This is included as a baseline and is what happens today when you call Contains with a list: the elements of the list get in-lined as constants in the SQL as literals and the SQL cannot be reused.

Parameter rewrite

This approaches tries to rewrite the expression to mimic what the compiler would produce for a query like this:

var p1 = 1;
var p2 = 2; 
var p3 = 3;
context.People.Where(p => (new List<int> {p1, p2, p3}.Contains(p.Id))).ToList();

It also “bucketizes” the list, meaning that it only produces lists of specific lengths (powers of 2), repeating the last element as necessary, with the goal of favoring caching.

This approach only works within the limits of 2100 parameters in SQL Server, and sp_executesql takes a couple of parameters, so the size of the last possible bucket is 2098.

Overall this seems to be the most expensive approach using EF Core, at least on initial creation and execution.

Split function

This approach was mentioned by @NickCraver and @mgravell as something that Dapper can leverage. I implemented it using FromSql. It is interesting because it leads to just one (potentially very long) string parameter and it seems to perform very well (at least in the first query), but the STRING_SPLIT function is only available in SQL Server since version 2016.

Table-valued parameter

For this I took advantage of the ability to create table-valued parameters that contain an IEnumerable<DbDataRecord> (I used a list rather than a streaming IEnumerable, but not sure that matters in this case) and pass them as a DbParameter in FromSql. I also added a hack to define the table type for the parameter if it isn’t defined. Overall this seems to be the second most lightweight approach after the split function.

@yv989c @pfritschi I’ve done some work in the recent days to improve the SQL quality around this, specifically #30976 which switches to using IN instead of EXISTS where possible, and #30983 which switches to OPENJSON with WITH where ordering preservation isn’t required (e.g. with IN/EXISTS). All this work should hopefully make it into preview6, and should make the EF-generated SQL as optimal as possible.

#30984 tracks synthesizing an ordering value for parameter collections where ordering is needed; I don’t intend to work on this at the moment.

Hey @roji , maybe I can further elaborate on the issues described by @m-gasser:

Our initial implementation did not have the WITH clause and we had issues in production this way. So at least for us, it will not be good enough without the WITH clause, we tried.

Can you share more details about this? What specific problems did you have?

If you use OPENJSON without the data type definition for the values, SQL Server will throw the Warning Type conversation in expression (CONVERT_IMPLICIT(int,OPENJSON_DEFAULT.[value],0)) may affect "CardinalityEstimate" in query plan choice. As the warning explains, having an incorrect cardinality estimation can lead to inefficient query plans, because this estimation is a key factor in determining which join and index operations are used. You will notice this issue if you work with bigger data sets or have many joins in your query.

However, if you define the data type of your JSON values with WITH (...), SQL Server is able to estimate the cardinality correctly and you will get a more stable query execution plan.

We also size the json parameter to VARCHAR(100), VARCHAR(4000) or VARCHAR(max), depending on how many parameters are provided. I can not remember the details but I guess this allows SQL Server to keep different query plans optimized for varying number of parameters.

Here as well I’d be interesting in understanding this better - any concrete info would be very helpful.

When your JSON values are strings and you want to provide the data type (eg. OPENJSON(@display_names) WITH([value] VARCHAR(100) '$') AS [n] WHERE [n].[value] = [u].[DisplayName], you have to make sure, that the length of VARCHAR (in this example 100) is consistent for the same query. You should use the target column definition if possible, or if you cannot use the column definition you should try to use gradations.

In the picture above I have executed the same queries with different parameters, one time with a consistent VARCHAR length and one time dynamically calculated depended on the longest string in the parameter. As you can see, if you have a different VARCHAR lengths, SQL Server will think that it is a different query and generate a new query plan. This causes a lot of overhead for each execution and also prevents SQL Server form using the Query Store and Plan Cache efficiently.

After further tests I also noticed that (as mentioned by @m-gasser), you get different query execution plans, if the json string is defined with different lengths. So EXEC sp_executesql '...', N'@json_string VARCHAR(4000)', @json_string='["a","b","c"]' will generate a different query plan then EXEC sp_executesql '...', N'@json_string VARCHAR(2000)', @json_string='["a","b","c"]', even if the query and data type definitions are exactly the same.

NOTE: See additional benchmarking below with SqlClient EnableOptimizedParameterBinding

Did a quick perf investigation of parameterized vs. non-parameterized WHERE x IN (...) with lots of values.

Key notes and takeaways:

• This benchmark does not factor in the price of plan cache pollution, so it shows non-parameterized queries in a better light than the reality. In a real application with many query types, the non-parameterized IN queries would cause other queries to get evicted, affecting their perf in a potentially significant way. In this benchmark there’s just one SQL, so that obviously doesn’t show.
• Parameterized queries are indeed slower on SQL Server. A somewhat significant gap starts to show even with 10 values (~230us or ~28%), with the gap seemingly widening with the number of values. The 1000 element case seems anomalous with a huge gap, but only in the “not found case”.
• In PG they’re also a tiny bit slower, but far less than SQL Server. With 1000 IN values, the difference is 871us (8%) and 283us (2%) for the “found” and “not found” cases. This is almost sure to not be worth the price of losing command preparation (due to pollution), for the general case.
• Note that PG already has an efficient translation for this LINQ construct, since it has 1st class array support.

To summarize, I hope we can end up with a solution that doesn’t use the SQL IN construct at all, thereby avoiding all of this (see the INNER JOIN-based solutions above).

| Method | NumValues |   Database | IsFound | IsParameterized |        Mean |     Error |    StdDev |      Median |
|------- |---------- |----------- |-------- |---------------- |------------:|----------:|----------:|------------:|
|   Test |         1 | PostgreSQL |   False |           False |    304.7 us |   8.26 us |  24.36 us |    306.6 us |
|   Test |         1 | PostgreSQL |   False |            True |    304.9 us |   8.67 us |  25.56 us |    311.0 us |
|   Test |         1 | PostgreSQL |    True |           False |    311.0 us |   8.43 us |  24.71 us |    314.0 us |
|   Test |         1 | PostgreSQL |    True |            True |    314.7 us |   9.22 us |  27.20 us |    317.2 us |
|   Test |        10 | PostgreSQL |   False |           False |    331.9 us |  14.88 us |  43.88 us |    343.4 us |
|   Test |        10 | PostgreSQL |   False |            True |    344.7 us |  11.92 us |  35.16 us |    346.0 us |
|   Test |        10 | PostgreSQL |    True |           False |    350.0 us |  12.44 us |  36.69 us |    356.4 us |
|   Test |        10 | PostgreSQL |    True |            True |    357.2 us |  11.32 us |  33.37 us |    359.6 us |
|   Test |       100 | PostgreSQL |   False |           False |    671.5 us |  34.80 us | 101.51 us |    656.9 us |
|   Test |       100 | PostgreSQL |   False |            True |    724.5 us |  48.19 us | 142.10 us |    716.4 us |
|   Test |       100 | PostgreSQL |    True |           False |    719.6 us |  50.41 us | 147.83 us |    695.3 us |
|   Test |       100 | PostgreSQL |    True |            True |    730.6 us |  50.48 us | 148.86 us |    715.9 us |
|   Test |      1000 | PostgreSQL |   False |           False |  9,738.7 us |  53.92 us |  45.02 us |  9,746.8 us |
|   Test |      1000 | PostgreSQL |   False |            True | 10,021.4 us | 199.92 us | 273.65 us |  9,981.0 us |
|   Test |      1000 | PostgreSQL |    True |           False |  9,299.2 us | 182.40 us | 355.76 us |  9,247.7 us |
|   Test |      1000 | PostgreSQL |    True |            True | 10,170.5 us | 201.69 us | 207.12 us | 10,190.8 us |

|   Test |         1 |  SQLServer |   False |           False |    541.9 us |  10.80 us |  18.34 us |    548.0 us |
|   Test |         1 |  SQLServer |   False |            True |    546.4 us |  10.88 us |  11.64 us |    546.3 us |
|   Test |         1 |  SQLServer |    True |           False |    547.8 us |  10.86 us |  25.81 us |    554.7 us |
|   Test |         1 |  SQLServer |    True |            True |    552.8 us |  10.89 us |  16.63 us |    554.0 us |
|   Test |        10 |  SQLServer |   False |           False |    582.3 us |  11.44 us |  20.63 us |    590.0 us |
|   Test |        10 |  SQLServer |   False |            True |    808.9 us |  23.28 us |  68.65 us |    825.0 us |
|   Test |        10 |  SQLServer |    True |           False |    587.7 us |  11.72 us |  28.53 us |    595.7 us |
|   Test |        10 |  SQLServer |    True |            True |    817.7 us |  26.45 us |  77.99 us |    845.9 us |
|   Test |       100 |  SQLServer |   False |           False |  1,046.6 us |  63.10 us | 186.05 us |  1,111.1 us |
|   Test |       100 |  SQLServer |   False |            True |  1,853.9 us |  76.86 us | 226.62 us |  1,878.4 us |
|   Test |       100 |  SQLServer |    True |           False |  1,088.7 us |  57.03 us | 168.16 us |  1,134.6 us |
|   Test |       100 |  SQLServer |    True |            True |  1,900.9 us |  99.60 us | 293.69 us |  1,949.8 us |
|   Test |      1000 |  SQLServer |   False |           False |  2,522.5 us |  67.90 us | 199.15 us |  2,524.7 us |
|   Test |      1000 |  SQLServer |   False |            True | 20,636.4 us | 329.94 us | 275.52 us | 20,653.9 us |
|   Test |      1000 |  SQLServer |    True |           False | 20,642.5 us | 410.18 us | 770.42 us | 20,602.6 us |
|   Test |      1000 |  SQLServer |    True |            True | 20,818.2 us | 412.19 us | 677.24 us | 20,777.1 us |

BenchmarkRunner.Run<Benchmark>();

public class Benchmark
{
    private DbConnection _connection;
    private DbCommand _command;

    private const int Rows = 100000;

    [Params(1, 10, 100, 1000)]
    public int NumValues { get; set; }

    [Params("SQLServer", "PostgreSQL")]
    public string Database { get; set; }

    [Params(true, false)]
    public bool IsFound { get; set; }

    [Params(true, false)]
    public bool IsParameterized { get; set; }

    private Guid _existingGuid;

    [GlobalSetup]
    public Task Setup()
        => Database switch
        {
            "SQLServer" => SetupSqlServer(),
            "PostgreSQL" => SetupPostgreSQL(),
            _ => throw new NotSupportedException("Unknown database: " + Database)
        };

    private async Task SetupSqlServer()
    {
        var connection = new SqlConnection("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false");
        await connection.OpenAsync();
        var command = connection.CreateCommand();
        command.CommandText =
"""
DROP TABLE IF EXISTS data;
CREATE TABLE data (id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(), some_int INT);
""";
        await command.ExecuteNonQueryAsync();

        foreach (var chunk in Enumerable.Range(0, Rows).Chunk(1000))
        {
            var builder = new StringBuilder("INSERT INTO data (some_int) VALUES ");
            for (var i = 0; i < chunk.Length; i++)
            {
                if (i > 0)
                    builder.Append(", ");
                builder.Append($"({chunk[i]})");
            }

            command.CommandText = builder.ToString();
            await command.ExecuteNonQueryAsync();
        }

        command.CommandText = "SELECT TOP(1) id FROM data";
        _existingGuid = (Guid)(await command.ExecuteScalarAsync())!;

        var builder2 = new StringBuilder("SELECT some_int FROM data WHERE id IN (");

        command.Parameters.Clear();

        if (IsParameterized)
        {
            for (var i = 0; i < NumValues; i++)
            {
                if (i > 0)
                    builder2.Append(", ");

                builder2.Append("@p" + i);

                command.Parameters.AddWithValue("p" + i, i == NumValues - 1 && IsFound ? _existingGuid : Guid.NewGuid());
            }
        }
        else
        {
            for (var i = 0; i < NumValues; i++)
            {
                if (i > 0)
                    builder2.Append(", ");
                builder2
                    .Append("'")
                    .Append(i == NumValues - 1 && IsFound ? _existingGuid : Guid.NewGuid())
                    .Append("'");
            }
        }

        builder2.Append(")");
        command.CommandText = builder2.ToString();

        _connection = connection;
        _command = command;
    }

    private async Task SetupPostgreSQL()
    {
        var connection = new NpgsqlConnection("Server=localhost;Username=test;Password=test");
        await connection.OpenAsync();
        var command = connection.CreateCommand();
        command.CommandText =
"""
DROP TABLE IF EXISTS data;
CREATE TABLE data (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), some_int INT);
""";
        await command.ExecuteNonQueryAsync();

        foreach (var chunk in Enumerable.Range(0, Rows).Chunk(1000))
        {
            var builder = new StringBuilder("INSERT INTO data (some_int) VALUES ");
            for (var i = 0; i < chunk.Length; i++)
            {
                if (i > 0)
                    builder.Append(", ");
                builder.Append($"({chunk[i]})");
            }

            command.CommandText = builder.ToString();
            await command.ExecuteNonQueryAsync();
        }

        command.CommandText = "SELECT id FROM data LIMIT 1";
        _existingGuid = (Guid)(await command.ExecuteScalarAsync())!;

        var builder2 = new StringBuilder("SELECT some_int FROM data WHERE id IN (");

        command.Parameters.Clear();

        if (IsParameterized)
        {
            for (var i = 0; i < NumValues; i++)
            {
                if (i > 0)
                    builder2.Append(", ");

                builder2.Append("$" + (i + 1));

                command.Parameters.Add(new()
                {
                    Value = i == NumValues - 1 && IsFound ? _existingGuid : Guid.NewGuid()
                });
            }
        }
        else
        {
            for (var i = 0; i < NumValues; i++)
            {
                if (i > 0)
                    builder2.Append(", ");
                builder2
                    .Append("'")
                    .Append(i == NumValues - 1 && IsFound ? _existingGuid : Guid.NewGuid())
                    .Append("'");
            }
        }

        builder2.Append(")");
        command.CommandText = builder2.ToString();

        _connection = connection;
        _command = command;
    }

    [Benchmark]
    public Task<object> Test()
        => _command.ExecuteScalarAsync();

    [GlobalCleanup]
    public ValueTask Cleanup()
        => _connection.DisposeAsync();
}

I see @roji. Thanks for taking the time to explain. On my side I see how my current approach might lead to unexpected results when evaluating Skip/Take on the server-side.

Hey @roji, I have fixed this in the latest release of QueryableValues. It now provides ordering guarantees, among other fixes and enhancements. Besides higher memory allocations, the performance cost resulting from the bigger payload and additional complexity was minimal.

Note that if there’s evidence of an actual performance difference between the two variants, there’s the option of emitting OPENJSON with WITH when ordering is irrelevant (e.g. for Contains), but without it where ordering needs to be preserved. But since this can add some complexity, I’d rather have some proof that it’s worth it first… Any help you can provide on this would be greatly appreciated - otherwise I can do some investigating too.

I ran a slim version of my Contains benchmarks using the latest version of QueryableValues with Microsoft.EntityFrameworkCore.SqlServer version 7.0.5 and 8.0.0-preview.4.23259.3. Unfortunately, the preview version performed significantly worse on types other than Int32. However, it does fix the query plan cache pollution issue.

Here are the BenchmarkDotNet results:

Server instance specs

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)
Oct  8 2022 05:58:25
Copyright (C) 2022 Microsoft Corporation
Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22621: ) (Hypervisor)

Benchmarks

• Using Microsoft.EntityFrameworkCore.SqlServer v7.0.5 Open Interactive Chart

• Using Microsoft.EntityFrameworkCore.SqlServer v8.0.0-preview.4.23259.3 Open Interactive Chart

I’d rather have some proof that it’s worth it first… Any help you can provide on this would be greatly appreciated - otherwise I can do some investigating too.

In our company we currently us a custom SqlServerQuerySqlGenerator to generate WHERE conditions in the form Foo IN(SELECT Value FROM OPENJSON(@jsonstr) WITH (Value CHAR(14)). Our initial implementation did not have the WITH clause and we had issues in production this way. So at least for us, it will not be good enough without the WITH clause, we tried. I hope this anecdotal evidence is good enough for you to implement this.

We also size the json parameter to VARCHAR(100), VARCHAR(4000) or VARCHAR(max), depending on how many parameters are provided. I can not remember the details but I guess this allows SQL Server to keep different query plans optimized for varying number of parameters.

Hello everyone! This is my take on providing a solution to this problem. It’s now even more flexible by allowing you to compose a complex type.

I just added some benchmarks showing great results.

Please give it a try. I’m looking forward to your feedback.

Links:

• https://github.com/yv989c/BlazarTech.QueryableValues
• https://www.nuget.org/packages/BlazarTech.QueryableValues.SqlServer/

I’d like to contribute some insights that might be related to the issue @michaelmesser is discussing, as I encountered a similar problem while implementing OPENJSON in QueryableValues.

During performance tests, I discovered that the XML approach outperformed OPENJSON significantly when handling a large number of elements. The issue became apparent when I composed the output from the OPENJSON function against a physical table. The larger the physical table, the worse OPENJSON performed. A closer examination of the execution plan revealed that the query engine was reevaluating/parsing –my best interpretation– the JSON data provided to OPENJSON for each row –kinda– in the physical table. The following image showcases the metrics from my reproduction of the issue:

Per the execution plan, OPENJSON was being executed multiple times, despite its input being constant. If this is true, then CPU cycles were being wasted.

After extensive experimentation and trying various complex approaches, I managed to persuade the query engine to execute the OPENJSON function only once. The outcome can be seen in the following image:

After this tweak, OPENJSON surpassed the performance of all other methods. This improvement was achieved by projecting a dummy column and subsequently sorting the OPENJSON output by it.

This was my experience when testing with the following SQL Server instance specs:

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)
Oct  8 2022 05:58:25
Copyright (C) 2022 Microsoft Corporation
Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22621: ) (Hypervisor)

I hope this information proves helpful.

Up to 1 GB ???

@StefanOverHaevgRZ we’ll try to put this in for preview4, but there’s a chance that might not happen and it’ll only be in preview5.

And yes - the new solution utilizes JSON, so the number of values in the array is no longer important.

Per the above, JSON seems to be the right candidate for the job because of its availability across DB technologies […]

FWIW the other candidate (parameterized IN) is also available across all DBs.

On the other hand, instead of scoping this to only the use of Contains, would it be possible to consider leveraging the power of the JSON format to allows us to compose any in-memory collection with the DB entities?

That’s #30426, which I mentioned in the summary; and yes, the OPENJSON approach wouldn’t be limited only to Contains. However, at this point it’s unlikely we’ll focus on collection parameters which aren’t over simple primitive types (e.g. composite keys which involve tuples), since that will probably introduce some significant additional complexity. I’ll definitely keep it in mind during implementation, but as @ErikEJ wrote, it’s important to focus on this very specific issue (the 2nd most highly voted in this repo) and not let scope creep compromise it.

Note that OPENJSON can’t always be used (older SQL Server versions, spatial types are incompatible with it). For those cases we’ll currently revert back to the current behavior (IN with constants).

How do you plan to implement this? Via an DbOptions setting for older compat levels? Or query the compat level?

A DbOptions setting. We’ll mostly likely default to using OPENJSON by default, and you’d have a context option to request compatibility with older versions (minor breaking breaking change for people using older databases). I think we’ve already had other cases where this was needed, so I think it makes sense.

Maybe: This function is only available with compatibility level 130 or higher (SQL Server 2016 and later and Azure SQL Database)

Thanks, edited the text.

IIRC SQL Server TVPs require an explicit head-of-time CREATE TYPE, so probably not appropriate for this kind of thing. But JSON is probably a possibility (via OPENJSON).

https://gist.github.com/ErikEJ/6ab62e8b9c226ecacf02a5e5713ff7bd

This one, with great comments and suggestions @mrlife

Interesting discussion.

It would be interesting to see benchmark results with the upcoming JSON native type which “will allow you to store JSON documents in a native binary format that is optimized for storage and query performance”. It’s probably more relevant for column data type, but one can also declare JSON parameters/variables.

DECLARE @json JSON = N'
[
    {
        "OrderNumber": "S043659",
        "Date":"2022-05-24T08:01:00",
        "AccountNumber":"AW29825",
        "Price":59.99,
        "Quantity":1
    },
    {
        "OrderNumber": "S043661",
        "Date":"2022-05-20T12:20:00",
        "AccountNumber":"AW73565",
        "Price":24.99,
        "Quantity":3
    }
]';

Although I’m not sure if it’s possible to use this type via c# code.

@canadar only in EF Core 8 - this is quite a serious change/feature, we don’t backport this kind of thing to patch releases (see our planning/release docs).

@yv989c apologies for the long delay, I was travelling.

I’m not an expert on SQL Server internals by any means. I mainly rely on the information provided by the execution plan and stats (e.g. SET STATISTICS) in an effort to make an educated assessment.

Oh absolutely - and all your comments/feedback here is greatly appreciated; you’ve certainly gone deeper here than I have.

IME the more info you provide to the query engine, the better it can optimize its plan.

That’s definitely true and I also would prefer to use OPENJSON with WITH rather than apply casting in the projection, if only for SQL cleanliness. Note that if there’s evidence of an actual performance difference between the two variants, there’s the option of emitting OPENJSON with WITH when ordering is irrelevant (e.g. for Contains), but without it where ordering needs to be preserved. But since this can add some complexity, I’d rather have some proof that it’s worth it first… Any help you can provide on this would be greatly appreciated - otherwise I can do some investigating too.

@michaelmesser thanks for your repro - I can see the (quite strange) phenomenon occuring. I’ll investigate this internally and post back here on the results.

@yv989c yeah, the OPENJSON form with WITH unfortunately doesn’t provide the array index to order by. Note that Azure Synapse does provide an array identity function (docs), but this unfortunately isn’t yet supported elsewhere. I’ve received confirmation that one should not rely on the order of rows being the same as the input array - an ORDER BY is necessary in order to guarantee that; as a result I’ve implemented this without the WITH clause, and added casting on the projected column instead.

I attached SQL to reproduce OPENJSON with WithUnorderedPrefetch and without WithUnorderedPrefetch. Run TestCreate.sql, TestInsert.sql, and then TestQuery.sql. While the difference in this example is hundreds of milliseconds, the performance difference with the real data is many seconds.

Test.zip

I tried to use OPENJSON and STRING_SPLIT to work around this issue. With all the relevant pages in memory everything works as expected. However, when pages have to be read from disk the performance is very slow. OPENJSON and STRING_SPLIT have low estimates. This leads to the INNER LOOP JOIN not having WithUnorderedPrefetch enabled. Without prefetching, the query has physical reads instead of read-ahead reads. Tricking the cardinality estimator into giving a higher estimate fixes the issue, but results in less readable code. How will EF Core’s solution handle this case?

Oh I see…

I’m not sure to what extent we need to actually look at the SQL Server compatibility level in those cases… The thing with this issue is that there’s a fallback: in modern SQL Server we use JSON, otherwise we fall back to IN+constants (since Contains must work, we can’t have it broken on old versions).

With STRING_AGG and CONCAT_WS there’s no “bad” translation to fall back to if they’re not supported, AFAIK. So a compatibility check would at most allow us to provide the user with a more informative “you need to upgrade” error, rather than a cryptic SQL Server “syntax error”.

In any case, CONCAT_WS is tracked by #28899, I already have an old PR #28900 which I’ll need to finish at some point. STRING_AGG is already in - we can indeed add a check to throw an informative message there (though that seems somewhat low-value).

@Meligy we’re definitely aware of that approach - see the very first post above.

I’ve been doing some research on this question and will soon post a plan of action here.

@ErikEJ true - ideally we’d have raw, ADO.NET benchmarks that show bare database performance; but they do provide an indication. We’ll like do some such benchmarking when this is actually implemented for EF.

Thanks!

Thanks, curious as to how that actually works, will dig into it

@danielgreen I’m glad you’re curious. You can find those details here. 🙂

@danielgreen looks like it is passing a xml string, not a tvp

I’d like to be able to run an adhoc query passing in an array of ints, the way you can pass a table valued parameter to a stored proc. Does the QueryableValues extension enable that?

@danielgreen You can find that precise example here.

I’d like to be able to run an adhoc query passing in an array of ints, the way you can pass a table valued parameter to a stored proc. Does the QueryableValues extension enable that?

Credit where credit is due on the OPENJSON suggestion 😃 https://github.com/dotnet/efcore/issues/13617#issuecomment-491399486

Indeed!

[…] whether anything can be done to influence the estimate, as EF Core would know the actual number before sending the query.

Sure… Though we’d probably want to avoid anything that involves embedding a (constant) query hint or similar in the SQL, as that would bring us back to the original problem (SQL which depends on the collection size).

Thanks @ErikEJ for the link! Yeah, ideally JSON would perform at least nearly as well, allowing us to just always use that and not worry about delimiters.

@stevendarby Looks like @aaronbertrand did quite a lot of testing in this area already, as always “it depends” but json is not all bad: https://sqlperformance.com/2016/04/sql-server-2016/string-split-follow-up-2

@roji I said as much in my posts so don’t think I’ve oversold it as an option for some to explore- definitely some work to do for the general case 😃

@stevendarby it’s an interesting direction, but at least in the general case there are also varying number of values in the IN clause, so it’s limited in any case. That’s why an INNER JOIN-based solution could be a better way forward here.

@cjstevenson - The value you provided as example does not cause any SQL injection. EF Core escapes quotes properly in the SQL. Generated SQL for example

      SELECT [b].[Id], [b].[Value]
      FROM [Blogs] AS [b]
      WHERE [b].[Value] IN (N'legit_value\''); SELECT * FROM some_table limit 10; -- ')

This is not a security issue. This issue has nothing to do with values and SQL injection, please file a new issue with repro steps if you are seeing a bug. Please refer to security policy if you are submitting a report for security issue.

Might be interesting for others, here’s how we solved the problem by using LINQKit.Core:

var predicate = PredicateBuilder.New<User>();
foreach (var id in ids)
{
	predicate = predicate.Or(user => user.Id == id);
}

var throwAway = context.Users.Where(predicate).ToList();

This will generate a parameterized query that looks like this:

SELECT ...
FROM   [Users] AS [u]
WHERE  [u].[Id] = @id1 
    OR [u].[Id] = @id2
    OR [u].[Id] = @id3
    ...

Related issue: https://github.com/aspnet/EntityFrameworkCore/issues/12777