SQL Server Recognize SP_EXECUTESQL как объект, а не имя процедуры

Ответ 1

Проблема заключается в том, что в большинстве вызовов, вызванных EF, используются DbCommand с CommadType Text, поэтому, хотя SqlServer распознает вызовы SP, он выполняет их как текст через sp_executesql.

Чтобы получить желаемое поведение, команда должна быть настроена следующим образом:

DbCommand command = ...;
command.CommandText = "StoredProcedureName";
command.CommandType = CommadType.StoredProcedure;

К сожалению, EF не предоставляет стандартный способ указания типа команды. Решение, которое я предлагаю, основано на:

• Пользовательский синтаксис SQL вызова SQL с использованием CallPrefix StoredProcedureName, чтобы не мешать регулярным вызовам
• EF command interception, чтобы удалить префикс и изменить тип команды перед выполнением команды.

Вот реализация:

using System.Data;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;

public static class Sp
{
    public const string CallPrefix = "CallSP ";

    public static string Call(string name) { return CallPrefix + name; }

    public class CallInterceptor : DbCommandInterceptor
    {
        public static void Install()
        {
            DbInterception.Remove(Instance);
            DbInterception.Add(Instance);
        }

        public static readonly CallInterceptor Instance = new CallInterceptor();

        private CallInterceptor() { }

        static void Process(DbCommand command)
        {
            if (command.CommandType == CommandType.Text && command.CommandText.StartsWith(Sp.CallPrefix))
            {
                command.CommandText = command.CommandText.Substring(Sp.CallPrefix.Length);
                command.CommandType = CommandType.StoredProcedure;
            }
        }

        public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            Process(command);
            base.ReaderExecuting(command, interceptionContext);
        }
    }
}

Все, что вам нужно, - добавить вышеописанный класс к вашему проекту, вызвать Sp.CallInterceptor.Install() один раз, например, внутри статического конструктора DbContext:

public class YourDbContext : DbContext
{
    static YourDbContext()
    {
        Sp.CallInterceptor.Install();
    }
    // ...
}

а затем измените ваши вызовы SP, как это (используя ваш образец):

от

return DataContext.Database.SqlQuery<CaseList>("EXEC GetCaseList @CaseStage", 
    new SqlParameter("@CaseStage", paramList.CaseStageID)).ToList();

в

return DataContext.Database.SqlQuery<CaseList>(Sp.Call("GetCaseList"), 
    new SqlParameter("@CaseStage", paramList.CaseStageID)).ToList();

который будет генерировать (для paramList.CaseStageID == 9):

EXEC GetCaseList @CaseStage = 9

Ответ 2

Использование sp_executesql в структуре сущностей /ADO.net является преднамеренным. Наблюдалось, что иногда в сгенерированном sql оказалось, что EF был очень решающим между непосредственным выполнением запроса и иногда использованием sp_executesql. Sp_executesql вступает в игру, когда есть параметризация клиентской стороны, помогающая в повторном использовании одного параметризованного скомпилированного плана. Если параметр не указан, SQL Server пытается выполнить автоматическую параметризацию, помогая повторно использовать план запроса.

Похоже, что решение использовать sp_executesql или прямую партию sql управляется объектом ADO.Nets SQLCommand. Он отображается в табличном потоке данных (TDS), и только 2 способа выполнить запрос SQL - используйте RPC для выполнения хранимой процедуры SQL и используйте SQL Batch для T-SQL. Поэтому, когда у нас есть параметризованный запрос, мы склонны использовать RPC и вызывать sp_executesql. Подробнее о запросе шаблон выполнения.

Дополнительная информация о параметризации запроса здесь

Ответ 3

Существует несколько причин:

Основная причина (1): Строка TSQL создается только один раз, после чего каждый раз, когда тот же запрос вызывается с sp_executesql, SQL Server извлекает план запроса из кеша и повторно использует его.

(2): sp_executesql позволяет параметризовать операторы, чтобы он был более безопасным, чем EXEC в терминах SQL-инъекции.

пример для сравнения, пожалуйста, просмотрите ссылку ниже: сравнение EXEC и EXEC sp_executesql

Ответ 4

Я думаю, это связано с тем, что EF нужно динамически генерировать команду SQL, поэтому для динамического выполнения sql необходимо использовать exec sp_executesql.