Avoiding SQL Server plan cache pollution with EF Core 3 and Enumerable.Contains()

[ErikEJ]

[roji]

Any reason to generate so many ORs rather than to just put the parameter placeholders in the IN parentheses?

[ErikEJ]

Just because I would not know how to do that! Do you think it makes any difference performance wise?

[joelweiss]

Do you think it makes any difference performance wise?

this says no

[ksks88]

Hey @ErikEJ,

Do you think it would be safe to remove the condition for checking argument length for Postgresql (Npgsql)?

Good job BTW!

[ErikEJ]

Yes, looks like some source say short.MaxValue is the limit, but not sure about the ADO.NET provider - @roji ?

[roji]

Sorry, am missing some context... What exactly are we talking about here? :)

[ksks88]

https://erikej.github.io/efcore/sqlserver/2020/03/30/ef-core-cache-pollution.html

[roji]

Oh, OK. Yeah, PostgreSQL supports up to ushort.MaxValue (65535) parameters for each statement - but a batched command can have more than 65535 parameters (as long as no single statement does). What current argument length check do you have?

[ErikEJ]

Current max for SQL Server is 2048, so it can be much higher for postgress

[roji]

Yep, absolutely.

[ransagy]

Sorry for raising the dead here a bit, But im trying to put it into context and this seemed a better location than the EF Core issue for it (if you think otherwise, let me know and ill mirror there):

You chose to update only the "bucketed" solution from divega's proposals - Is that solely because its more globally supported by the various DB vendors? Strictly in SQL Server (and Azure SQL), do you think the string_split or TVP solutions are preferable in the context of plan cache pollution?

I'm specifically more worried about using FromSql/FromSqlInterpolated if i can avoid it entirely.

[ErikEJ]

I am actually writing a blog post about using string_split just now.

This solution is provider agnostic, string_split SQL Server only.

And when I wrote the code, TVF mapping was not available in EF Core, it is now, so no FromSql needed!

[ransagy]

Then i'll be looking forward for that article and will take another look at TVFs (which i thought not relevant in this context for some reason). Thanks!

[ErikEJ]

I just blogged: "Avoiding SQL Server plan cache pollution due to unparameterized Contains queries generated by EF Core"
https://erikej.github.io/efcore/sqlserver/2021/11/22/efcore-sqlserver-cache-pollution.html

[joelmandell]

I had to use the IQueryableExtensions.In(), to support older SQL Server versions.
And had an issue when using a library that parses the table name from generated query (I am using multiple Interceptors). The issue is when IEnumerable<TKey> values is empty.

It has the check if(!values.Any()) and returns queryable.Take(0).
When that call is used EF Core generates an subquery, and the library that I use (EFCoreSecondLevelCacheInterceptor) fails trying to fetch table name from query.

I resorted to return queryable.Where(x => true) instead. And that seems to work after that. Posting this in case someone else has this problem.

Updated code of that extension method, according to that change:

public static IQueryable<TQuery> In<TKey, TQuery>(
            this IQueryable<TQuery> queryable,
            IEnumerable<TKey> values,
            Expression<Func<TQuery, TKey>> keySelector)
        {
            if (values == null)
            {
                throw new ArgumentNullException(nameof(values));
            }

            if (keySelector == null)
            {
                throw new ArgumentNullException(nameof(keySelector));
            }

            if (!values.Any())
            {
                //.Where instead of .Take(0), cause that seem to produce "funky" SQL.
                return queryable.Where(x => true);
            }

            var distinctValues = Bucketize(values);

            if (distinctValues.Length > 2048)
            {
                throw new ArgumentException("Too many parameters for SQL Server, reduce the number of parameters", nameof(keySelector));
            }

            var predicates = 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);
                })
                .ToList();

            while (predicates.Count > 1)
            {
                predicates = PairWise(predicates).Select(p => Expression.OrElse(p.Item1, p.Item2)).ToList();
            }

            var body = predicates.Single();

            var clause = Expression.Lambda<Func<TQuery, bool>>(body, keySelector.Parameters);

            return queryable.Where(clause);
        }