mvc - procedimientos almacenados entity framework c#

SQL Server reconoce SP_EXECUTESQL como objeto en lugar de Nombre del procedimiento

Question

Estoy usando DBContext.Database.SqlQuery<entity> para ejecutar el procedimiento almacenado desde mi repositorio de código C #.

Funciona bien, pero quiero saber por qué está ejecutando el procedimiento como a continuación:

exec sp_executesql N''EXEC GetCaseList @CaseStage'',N''@CaseStage int'',@CaseStage=9

más bien que

EXEC GetCaseList @CaseStage = 9

¿Y hay alguna forma en que todos mis procedimientos se ejecuten desde c # como este
EXEC GetCaseList @CaseStage = 9 lugar de exec sp_executesql N''EXEC GetCaseList @CaseStage'',N''@CaseStage int'',@CaseStage=9 ?

¿Cómo puedo hacer que SQL Server Profiler trate el nombre del procedimiento como objeto en lugar de SP_EXECUTESQL?

Nota: Deseo ejecutar el procedimiento desde c # como EXEC GetCaseList @CaseStage = 9 porque estoy guardando datos de rastreo a través de SQL Server Profiler en formato de tabla. Y en la columna ObjectName , muestra sp_executesql como objeto en lugar de nombre de procedimiento (GetCaseList) como objeto. Puedo hacer cambios solo desde el código c #.

Answer 1

Creo que esto se debe a que el EF necesita dinámica para generar el comando SQL, por lo que es necesario utilizar exec sp_executesql para que dynamic ejecute el sql.

Answer 2

El problema es que la mayoría de las llamadas de base de datos EF realizadas utilizan DbCommand con CommadType Text , por lo que aunque SqlServer reconoce las llamadas SP, las ejecuta como texto a través de sp_executesql .

Para obtener el comportamiento deseado, el comando debe configurarse de esta manera:

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

Lamentablemente, EF no proporciona una forma estándar de especificar el tipo de comando. La solución que estoy sugiriendo está basada en:

• Sintaxis SQL de llamada de SP personalizada utilizando CallPrefix StoredProcedureName para no interferir con las llamadas regulares
• Interceptación del comando EF para eliminar el prefijo y cambiar el tipo de comando antes de ejecutar el comando.

Aquí está la implementación:

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

Todo lo que necesita es agregar la clase anterior a su proyecto, llame a Sp.CallInterceptor.Install() una vez, por ejemplo, dentro de su constructor estático DbContext :

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

y luego cambie sus llamadas SP de esta manera (usando su muestra):

de:

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

a:

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

que generará (para paramList.CaseStageID == 9 ):

EXEC GetCaseList @CaseStage = 9

Answer 3

El uso de sp_executesql en entity framework / ADO.net es intencional. Se observó que a veces en el sql generado parecía que EF era muy decisivo entre la ejecución directa de la consulta y, a veces, el uso de sp_executesql. El sp_executesql entra en juego cuando hay un lado del cliente parametrización que ayuda en la reutilización de un plan compilado parametrizado. Cuando no hay ningún parámetro especificado, SQL Server intenta hacer una parametrización automática ayudando a la reutilización del plan de consulta.

Parece que la decisión de utilizar sp_executesql o un lote directo de sql se rige por el objeto SQLCommand de ADO.Net. Según el flujo de datos tabulares (TDS), solo hay 2 formas de ejecutar una consulta SQL: use RPC para ejecutar el procedimiento almacenado de SQL y utilice SQL Batch para T-SQL. Entonces, cuando tenemos una consulta parametrizada, tendemos a usar RPC y llamar a sp_executesql.More sobre el patrón de ejecución de consultas.

Más información acerca de la parametrización de consultas aquí

Answer 4

Hay varios motivos de la siguiente manera:

El motivo principal (1): la cadena TSQL se crea solo una vez, después de eso cada vez que se llama a la misma consulta con sp_executesql, SQL Server recupera el plan de consulta de la memoria caché y lo vuelve a utilizar.

(2): sp_executesql permite parametrizar las declaraciones, por lo que es más seguro que EXEC en términos de inyección SQL.

ejemplo de comparación, revise el siguiente enlace: comparación de EXEC y EXEC sp_executesql