C# EF ASP.NET Web API timeout - how can I call a long running query?

Question

I have a C# code that I want to execute.

  str = "SELECT u.LastName " +
                      ",COALESCE(COUNT(DISTINCT pc.PhraseId), 0) AS CreatedByQty " +
                      ", COALESCE(COUNT(DISTINCT pm.PhraseId), 0) AS ModifiedByQty " +
                      "FROM dbo.AspNetUsers u " +
                      "LEFT JOIN dbo.Phrase pc " +
                      "ON u.Id = pc.CreatedBy " +
                      "LEFT JOIN dbo.Phrase pm " +
                      "ON u.Id = pm.ModifiedBy " +
                      "GROUP BY u.LastName ";
                threeColQuery = db.Database.SqlQuery<threeCol>(str);
                threeColReport = await threeColQuery.ToListAsync();
                return Ok(threeColReport);

I know from running the query in SQL Server manager that it takes about 30 seconds.

When running in a Web API Call I get this:

"innerException":{"message":"An error has occurred.","exceptionMessage":"The wait operation timed

Is there a way I can allow a WebAPI call to run a long running query?

You can set this programmatically in the controller like this:- HttpContext.Server.ScriptTimeout = 300; — Saurabh Srivastava
– Saurabh Srivastava, Commented Feb 13, 2017 at 10:28
You can change the timeout of your DbContext like this db.Database.CommandTimeout = 60;. Would you mind posting the code of your api? — Alisson Reinaldo Silva
– Alisson Reinaldo Silva, Commented Feb 13, 2017 at 10:28
Actually I forgot to ask whether the timeout is in your client when waiting the api, or inside your api when executing the query. — Alisson Reinaldo Silva
– Alisson Reinaldo Silva, Commented Feb 13, 2017 at 10:29

nikovn · Accepted Answer · 2017-02-13 10:51:02Z

2

Modifying the timeout settings on Entity Framework should do the trick.

db.Database.CommandTimeout = 60;

I would also advise against setting a global default timeout via the connection string. A minute is way too high for most queries, your 30-second query is an exception to the rule (thus you're knowingly increasing the timeout period).

By preventing all timeout exceptions across your app you lose a good indicator of another query misbehaving - leading to long term performance problems.

edited Feb 13, 2017 at 10:51

answered Feb 13, 2017 at 10:32

nikovn

2,0185 gold badges24 silver badges31 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:54:40Z

0

Increase the connection timeout with Sql.

Two approach to do that

Increase the connection timeout for this query like

db.Database.CommandTimeout = 60

Set the timeout property globally. For that follow this answer

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Feb 13, 2017 at 10:32

Ashish Rajput

1,5291 gold badge11 silver badges20 bronze badges

Collectives™ on Stack Overflow

C# EF ASP.NET Web API timeout - how can I call a long running query?

2 Answers 2

Comments

Comments

Your Answer

Linked

Collectives™ on Stack Overflow

C# EF ASP.NET Web API timeout - how can I call a long running query?

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related