Entity Framework - extremely long execution time for stored procedure

Question

I'm following up on my question yesterday, Entity Framework 6 get complext return value from a stored procedure. My stored procedure now runs under entity framework. However, it times out after 3 minutes, the connection time out.

I run the stored procedure in my SQL Server Management Studio with the line (customer information omitted):

EXEC spGetDupWOs @ProjectName=N'...', @City=N'...', @State=N'LA', @ProposalNum=N'201703080740-001', @County=N'...', @Owner=N'...', @QuoteRecipients=N'...', @ProjectID=-1

It executes in less than a second. When Entity framwork executes it, it takes forever.

Using the SQL Server Profiler, I determined that Entity Framework is sending this line to the SQL server:

exec sp_executesql N'EXEC spGetDupWOs',N'@ProjectName nvarchar(19),@City nvarchar(6),@State nvarchar(2),@ProjectNum nvarchar(12),@County nvarchar(10),@Owner nvarchar(23),@QuoteRecipients nvarchar(23),@ProjectID bigint',@ProjectName=N'...',@City=N'Holden',@State=N'LA',@ProposalNum=N'201703080740-001',@County=N'Livingston',@Owner=N'...',@BID_RECIP=N'...',@ProjectID=-1

When I run this in SSMS, it takes forever to run.

Reading the similar questions it looks like the issue is Parameter Sniffing and a change in execution plan.

Here is my call to execute the stored procedure in my application:

            List<DuplicateProposals> duplicateCheckResults = 
                db.Database.SqlQuery<DuplicateProposals>("spGetDupWOs", 
                spl.ToArray())
                .ToList();

After reading a bunch of articles online, I'm even more confused. How can I change my call to resolve this?

Answer 1

I ended up having to convert the entire call into a single string that I passed to the SqlQuery function.

            string sql = string.Format("exec spGetDupWOs @ProjectName=N'{0}',@City=N'{1}',@State=N'{2}',@ProjectNumber=N'{3}',@County=N'{4}',@Owner=N'{5}',@QuoteRecipients=N'{6}',@ProjectID={7}", 
                project.ProjectName, 
                project.City,
                project.State,
                project.ProjectNumber,
                project.County,
                project.Owner,
                quoteRecipientsList, 
                "null");

Yes, I had to include the N prefix to the strings to make it work, I'm not sure why but it worked.

Thanks for all of the help everyone. I could not have solved this without your help.