Entity Framework Timeouts on SqlQuery

Question

I am using EF 6.1.3, and populating a model with one of the stored procs times out.

var spResult = context.Stuff.SqlQuery("exec GetStuff @p0, @p2", pId, typeId )
.OrderBy(a => a.Barcode)
.ToDataSourceResult(request);

I have a stored proc called GetStuff with two parameters: @id int and @typeId int = null //nullable

In SQL Profiler I can see the stored proc is being executed by EF on the server, with correct parameters.
When I run the stored proc in SSMS with those same parameters I get results in less than 1 second (copy pasted from Profiler to be sure).

If I change the one parameter when calling from EF, I get immediate results!

In Short: stored procedure GetStuff (1, null) works in EF and SSMS while GetStuff(1, 1) times out in EF, but works in SSMS.

The Stored proc is a Select only, with no transactions in SQL or my C# code defined.

Answer 1

This looks like a parameter sniffing issue see simple talk article

Try creating the stored procedure as

CREATE PROCEDURE GetStuff
  @pid INT,
  @typId INT
WITH RECOMPILE
AS
 ....