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.

Did you check it is throwing Timeout Expception or some thing else is there in Inner Exception — Rajeev Kumar
– Rajeev Kumar, Commented Mar 30, 2015 at 11:17
Looks like a parameter sniffing issue. Have you tried creating the stored procedure WITH RECOMPILE? — Steve Ford
– Steve Ford, Commented Mar 30, 2015 at 11:22
@callisto results are different between EF and ssms, unfortunately. My solution has been to increase the CommandTimeout, or try to optimize the used queries. You can look into SET STATISTICS xxx for more information regarding debugging queries (TIME and IO provide a lot of information) — default
– default, Commented Mar 30, 2015 at 11:50
@SteveFord Please put yours in an answer so I can mark it as correct. Many thanks for this! — callisto
– callisto, Commented Mar 30, 2015 at 12:06

Steve Ford · Accepted Answer · 2015-03-31 10:54:55Z

3

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

answered Mar 31, 2015 at 10:54

Steve Ford

7,81323 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:30:37Z

0

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

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Mar 30, 2015 at 11:23

user4679596

5 Comments

default Over a year ago

for Entity Framework 6 there are easier solutions

user4679596 Over a year ago

yes this is the refrence Click for Refrence

default Over a year ago

yes, that is the question/answer that I linked to. It is however not the code example that you are displaying. The code you have in your answer is used for EF5, not EF6 (the OP is using EF6).

callisto Over a year ago

@Dexter My timeout is set to 180 seconds, but the same query returns instantly from SSMS.

user4679596 Over a year ago

can you post the Complete error that occurred during query execution !

Collectives™ on Stack Overflow

Entity Framework Timeouts on SqlQuery

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related