Im facing an issue in entity framework with Linq queries. I have a query which when I run it give this exception

"SqlException: The query has been canceled because the estimated cost of this query (3010) exceeds the configured threshold of 3000. Contact the system administrator"

the server default value for QUERY_GOVERNOR_COST_LIMIT is 3000 as it shows in the error. I want to change it and make it higher for that particular query since I can not change the default value of the server.

so my question is how do you set QUERY_GOVERNOR_COST_LIMIT value in entity framework Linq syntax.

I have already tried and added this db.Database.SqlQuery<string>("SET QUERY_GOVERNOR_COST_LIMIT 15000"); before my query but it does not work since if im not mistaken QUERY_GOVERNOR_COST_LIMIT will work for the same query not 2 different queries

CodePudding user response：

SET QUERY_GOVERNOR_COST_LIMIT is changing limit for current DB connection so you need to be sure that both SQLs are using same connection.

using (var context = new MyDbContext())
{
    // open connection
    context.Database.Connection.Open();

    // set limit
    context.Database.ExecuteSqlCommand("SET QUERY_GOVERNOR_COST_LIMIT 15000");

    // run query with same connection
    var result = context.Database.SqlQuery<...>("SELECT * ");

}