EF Core takes a lot of time, sometimes, to perform SELECT query

It is not new question when SQL Server may slowdown queries because of Parameter Sniffing. Problem can be solved by converting parameters to constants or by adding OPTION(RECOMPILE) to the end of the query. This answer adds DbCommandInterceptor to DbContextOptions and appends OPTION(RECOMPILE) hint to particular queries.

Configuring DbContext

builder.UseSqlServer(connectionString)
    .UseRecompileExtensions(); // registering interceptor 

How to use in queries:

var name = "SomeName";
var result = context.SomeItems
    .Where(x => x.Name == name)
    .WithRecompile() // it marks query as a query which needs RECOMPILE query hint
    .ToList();

Then to SQL Server will be sent the following SQL:

SELECT [s].[Id], [s].[Name]
FROM [SomeItems] AS [s]
WHERE [s].[Name] = @__name_0
OPTION(RECOMPILE)

And implementation of extensions:

I have put everything into one static class for simplifying answer. Tested on EF Core 6, but should work for lower versions also.

public static class RecompileExtensions
{
    private const string RecompileTag = "recompile_query_tag";
    private const string RecompileComment = "-- " + RecompileTag + "\r\n";

    public static DbContextOptionsBuilder UseRecompileExtensions(this DbContextOptionsBuilder builder)
    {
        return builder.AddInterceptors(RecompileInterceptor.Instance);
    }

    public static IQueryable<T> WithRecompile<T>(this IQueryable<T> query)
    {
        return query.TagWith(RecompileTag);
    }

    private class RecompileInterceptor : DbCommandInterceptor
    {
        public static RecompileInterceptor Instance = new();

        public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
            CancellationToken cancellationToken = new CancellationToken())
        {
            CorrectCommand(command);

            return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
        }

        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            CorrectCommand(command);

            return base.ReaderExecuting(command, eventData, result);
        }

        private static void CorrectCommand(DbCommand command)
        {
            var newQuery = command.CommandText.Replace(RecompileComment, "");

            // if query was changed, we have to append RECOMPILE option
            if (!ReferenceEquals(newQuery, command.CommandText))
            {
                // remove rest of the comment
                if (newQuery.StartsWith("\r\n"))
                    newQuery = newQuery.Substring(2);

                newQuery += "\r\nOPTION(RECOMPILE)";
                command.CommandText = newQuery;
            }
        }
    }
}

UPDATE

Starting from SQL Server 2016 it is possible to disable Parameter Sniffing via ALTER DATABASE SCOPED CONFIGURATION

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;