CODEWITHSUNDEEP
c#sql-serverentity-framework-6.1
callisto
callisto

Reputation: 5083

Entity Framework Timeouts on SqlQuery

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.

Upvotes: 2

Views: 4385

Answers (2)

Steve Ford
Steve Ford

Reputation: 7763

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
 ....

Upvotes: 3

user4679596
user4679596

Reputation:

add this code to your context class

{
        Database.SetInitializer<yourDatabaseContextName>(null);
        var adapter = (System.Data.Entity.Infrastructure.IObjectContextAdapter)this;
        var objectContext = adapter.ObjectContext;
        objectContext.CommandTimeout = 120; //2 minutes
    }

click this for more information

Upvotes: 0

Related Questions