locked
Error when trying to add table to my EF Model RRS feed

  • Question

  • User-1188570427 posted

    I'm adding some new tables to my EF model and I am seeing this error now:

    I ran this line of code:

    SET QUERY_GOVERNOR_COST_LIMIT 40000

    and it still fails and gives me the error below. What could this issue be?

    Unable to generate the model because of the following exception: 'System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The query has been canceled because the estimated cost of this query (4212) exceeds the configured threshold of 3000. Contact the system administrator.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
    at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
    at System.Data.SqlClient.SqlDataReader.Read()
    at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.StoreRead()
    --- End of inner exception stack trace ---
    at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.HandleReaderException(Exception e)
    at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.StoreRead()
    at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
    at System.Data.Entity.Core.Query.ResultAssembly.BridgeDataReader.SetShaper(Shaper`1 shaper, CoordinatorFactory`1 coordinatorFactory, Int32 depth)
    at System.Data.Entity.Core.Query.ResultAssembly.BridgeDataReader.<>c__DisplayClass4.<.ctor>b__0()
    at System.Data.Entity.Core.Query.ResultAssembly.BridgeDataReader.EnsureInitialized()
    at System.Data.Entity.Core.Query.ResultAssembly.BridgeDataReader.Read()
    at System.Data.Entity.Core.EntityClient.EntityDataReader.Read()
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadDataTable[T](String sql, Func`2 orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable`1 filters, String[] filterAliases)
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadRelationships(IEnumerable`1 filters)
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadStoreSchemaDetails(IList`1 filters)
    at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GetStoreSchemaDetails(StoreSchemaConnectionFactory connectionFactory)
    at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel()
    at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List`1 errors)
    at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List`1 errors)
    at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'.
    Loading metadata from the database took 00:00:02.2571511.
    Generating the model took 00:00:09.6555569.

    Monday, February 25, 2019 2:59 AM

Answers

  • User-1188570427 posted

    tvb2727

    I backed up my database and restore locally to SQL server and manually changed the QUERY_GOVERNOR_COST_LIMIT property.  Then the model loaded without any issues.

    I suppose I have a table that is causing an issue?

    When you changed QUERY_GOVERNOR_COST_LIMIT property in local db then restored it to the server db. Then the server db QUERY_GOVERNOR_COST_LIMIT property changed and this solved your problem.

    No, the server db for my hosting site: QUERY_GOVERNOR_COST_LIMIT will not change once I restore it.

    I was just able to generate the model locally because I was able to change my local database server property. It is on the server -> properties and not the database -> properties.

    My hosting site wouldn't change it for their server because of performance issues.  They capped it at 3000.

    I must have a database table that generates a lot of query cost when making the model via Entity Framework and this new hosting site has that setting lower than the hosting site that it use to be on etc.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 1, 2019 5:04 PM

All replies

  • User-2054057000 posted

    Tip: Use the Query Governor to Control Excessive Query Execution.

    The query governor does not allow the execution of any query that has a running time that exceeds a specified query cost. The query cost is the estimated time, in seconds, required to execute a query, and it is estimated prior to execution based on an analysis by the query engine. By default, the query governor is turned off, meaning there is no maximum cost. To activate the query governor, complete the following steps:


    1. In the Server Properties dialog box, go to the Connections page.
    2. Select the option Use Query Governor To Prevent Long-Running Queries.
    3. In the box below the option, type a maximum query cost limit. The valid range is 0 through 2,147,483,647. A value of 0 disables the query governor; any other value sets a maximum query cost limit.
    4. Click OK.

    With sp_configure, the following Transact-SQL statement will activate the query governor:
    exec sp_configure "query governor cost limit", <limit>

    You can also set a per-connection query cost limit in Transact-SQL using the following statement:
    set query_governor_cost_limit <limit>

    Note Before you activate the query governor, you should use the Query view to estimate the cost of current queries you are running on the server. This will give you a good idea of a value to use for the maximum query cost. You can also use the Query view to optimize queries.

    The above solution is taken from here.

    Monday, February 25, 2019 10:53 AM
  • User-1188570427 posted

    Thanks, I'll look into this.

    My hosting site is saying that I need to make my databases locally and then restore them to their servers.

    I did notice one thing. When I go to make the model, I see it is saying 5.x when I think it should be 6.x

    Tuesday, February 26, 2019 4:26 AM
  • User-1188570427 posted

    Tip: Use the Query Governor to Control Excessive Query Execution.

    The query governor does not allow the execution of any query that has a running time that exceeds a specified query cost. The query cost is the estimated time, in seconds, required to execute a query, and it is estimated prior to execution based on an analysis by the query engine. By default, the query governor is turned off, meaning there is no maximum cost. To activate the query governor, complete the following steps:


    1. In the Server Properties dialog box, go to the Connections page.
    2. Select the option Use Query Governor To Prevent Long-Running Queries.
    3. In the box below the option, type a maximum query cost limit. The valid range is 0 through 2,147,483,647. A value of 0 disables the query governor; any other value sets a maximum query cost limit.
    4. Click OK.

    With sp_configure, the following Transact-SQL statement will activate the query governor:
    exec sp_configure "query governor cost limit", <limit>

    You can also set a per-connection query cost limit in Transact-SQL using the following statement:
    set query_governor_cost_limit <limit>

    Note Before you activate the query governor, you should use the Query view to estimate the cost of current queries you are running on the server. This will give you a good idea of a value to use for the maximum query cost. You can also use the Query view to optimize queries.

    The above solution is taken from here.

    UPDATE: I contact my hosting site admin and they stated they couldn't change this value due to performance issues. They said take a backup of my database to a local SQL server and then update it there and restore back to their hosting site. I'm still confused though - do I have a bad table that is causing this issue?

    Tuesday, February 26, 2019 1:46 PM
  • User-1188570427 posted

    I backed up my database and restore locally to SQL server and manually changed the QUERY_GOVERNOR_COST_LIMIT property.  Then the model loaded without any issues.

    I suppose I have a table that is causing an issue?

    Tuesday, February 26, 2019 7:11 PM
  • User-2054057000 posted

    I backed up my database and restore locally to SQL server and manually changed the QUERY_GOVERNOR_COST_LIMIT property.  Then the model loaded without any issues.

    I suppose I have a table that is causing an issue?

    When you changed QUERY_GOVERNOR_COST_LIMIT property in local db then restored it to the server db. Then the server db QUERY_GOVERNOR_COST_LIMIT property changed and this solved your problem.

    Friday, March 1, 2019 10:32 AM
  • User-1188570427 posted

    tvb2727

    I backed up my database and restore locally to SQL server and manually changed the QUERY_GOVERNOR_COST_LIMIT property.  Then the model loaded without any issues.

    I suppose I have a table that is causing an issue?

    When you changed QUERY_GOVERNOR_COST_LIMIT property in local db then restored it to the server db. Then the server db QUERY_GOVERNOR_COST_LIMIT property changed and this solved your problem.

    No, the server db for my hosting site: QUERY_GOVERNOR_COST_LIMIT will not change once I restore it.

    I was just able to generate the model locally because I was able to change my local database server property. It is on the server -> properties and not the database -> properties.

    My hosting site wouldn't change it for their server because of performance issues.  They capped it at 3000.

    I must have a database table that generates a lot of query cost when making the model via Entity Framework and this new hosting site has that setting lower than the hosting site that it use to be on etc.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 1, 2019 5:04 PM