In asp.net web api application we are using entity framework and we have set command timeout as below
var ctx = new DbContext();
ctx.Database.CommandTimeout = 180;
Web api returns an timeout error after 180 seconds but when we check for a long running queries in sql server, identified the query is still running in sql server and it is not cancelled after exceeding the command time out.
the below query returns the query which took around 250 + seconds to execute
declare @duration int = 180000000
select [max_duration] / 1000000 MaxDurationInSeconds,
from sys.query_store_runtime_stats qsrs
inner join sys.query_store_plan qsp on qsrs.plan_id = qsp.plan_id
inner join sys.query_store_query qsq on qsp.query_id = qsq.query_id
inner join sys.query_store_query_text qsqt on qsq.query_text_id = qsqt.query_text_id
where max_duration >= @duration
order by max_duration desc
Please suggest a solution so that after command timeout sql server cancels the query execution.