SQL Server Recognise SP_EXECUTESQL as object rather than Procedure Name

Question

I am using DBContext.Database.SqlQuery<entity> to execute stored procedure from my C# code repository.

It works fine but I want to know that why it is executing procedure like below:

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

rather than

EXEC GetCaseList @CaseStage = 9

And is there any way that my all procedures execute from c# like this
EXEC GetCaseList @CaseStage = 9 rather than exec sp_executesql N'EXEC GetCaseList @CaseStage',N'@CaseStage int',@CaseStage=9?

How can I make SQL Server Profiler to treat procedure name as object rather than SP_EXECUTESQL ?

Note: I want to execute procedure from c# as EXEC GetCaseList @CaseStage = 9 because I am saving trace data through SQL Server Profiler in table format. And in ObjectName column, it is showing sp_executesql as object rather than procedure name(GetCaseList) as object. I can make changes only from c# code.

Answer 1

The problem is that most of the EF performed database calls use DbCommand with CommadType Text, so although SqlServer recognizes SP calls, it executes them as text via sp_executesql.

To get the desired behavior, the command should be setup this way:

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

Unfortunately EF does not provide a standard way of specifying the command type. The solution I'm suggesting is based on:

• Custom SP call SQL syntax using CallPrefix StoredProcedureName in order to not interfere with regular calls
• EF command interception to remove the prefix and change the command type before executing the command.

Here is the implementation:

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

All you need is to add the above class to your project, call Sp.CallInterceptor.Install() once, for instance inside your DbContext static constructor:

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

and then change your SP calls like this (using your sample):

from:

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

to:

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

which will generate (for paramList.CaseStageID == 9):

EXEC GetCaseList @CaseStage = 9

Answer 2

Use of sp_executesql in entity framework/ADO.net is intentional.It was observed that sometimes in the generated sql it appeared that EF was very decisive between directly executing the query and sometimes using sp_executesql.The sp_executesql comes into play when there is a client side parameterization helping in the re-use of one parameterized compiled plan. When there is no parameter specified then SQL Server tries to do an auto parameterization helping in reuse of query plan.

It appears that the decision to use sp_executesql or a direct sql batch is governed by ADO.Net’s SQLCommand object. It appears per the Tabular Data Stream (TDS) there’s only 2 ways to execute a SQL query – use RPC to execute SQL stored procedure and use SQL Batch for T-SQL. So when we have a parameterized query we tend to use RPC and call sp_executesql.More about query execution pattern.

More information about Query Parameterization here