Update wizard not responding

Question

Every time I need to update my emdx from database, the update wizard takes an incredible amount of time to do so rendering itself as not responding once you hit the finish (as finish the update) button.

I use Visual Studio 2015 and LocalDb SQL Server 2014. Some people suggested to install the Service Pack 1 to address the issue. I have installed the SP1 for LocalDb, but it has not helped. My installation of VS2015 is also rather new.

I have the latest Entity Framework 6 version (from nuget).

Rummage

Same here with Visual Studio 2015 and real SQL Server 2014 – Prototherian

@jens Have you managed to solve the issue? – Rummage

We believe this is due to a regression in the cardinality estimator in SQL Server 2014. We previously introduced a workaround in EF Tools which consisted on appending OPTION (QUERYTRACEON 9481) to all our schema queries (see entityframework.codeplex.com/workitem/2445 for more details), but unfortunately that introduced functional regressions, e.g. schema queries started failing if the user didn't have enough privileges on the database, so we had to take the workaround out. The regression appeared to be fixed but recently we have had new reports. We are following up with the SQL Server. – Negligence

@Negligence Any update on a fix? – Reincarnate

Here's the issue tracker for the same problem with EF and SQL 2016: github.com/aspnet/EntityFramework6/issues/4. Some people report update statistics commands will help... – Sinusoidal

Answer 1

Setting the compatibility level of the database to 110 has worked for me.

To check the compatibility level, run this script:

select compatibility_level from sys.databases where name = '<YOUR_DB_NAME>'

To set the compatibility level, use this script:

alter database <YOUR_DB_NAME> set compatibility_level = 110

Rummage

To clarify, was was it set to before? Was it set to 70, 80, 90, 100, 120 or 140 before? – Pubes

I think my original value was 120, but it has already been some time since I did the operation. I also believe that the original value can differ depending on what version of local sql you have. – Rummage

Worked like a charm for me. Can anyone shed some light on this that why we have to set the compatibility level to 110? Are there any side effects of it? – Leicestershire

I'm getting this info: Valid values of the database compatibility level are 80, 90, or 100. What should I choose? – Geyser

@Leicestershire Look into the question comments at divega's comment – Rummage

@Geyser What sql server are you running? – Rummage

This worked for me with Visual Studio 2015 Update 3 and a SQL Server 2014 (12.0.2000). – Nd

this worked for me as well, anyone know why the compatibility level fixes this? – Calendula

Setting can also be changed in the properties dialog of the database – Timotheus

Can anyone tell me what is significance of level 110 , what is the problem with 120 ? – Almeria

Answer 2

Running the following on the DB worked for me:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON

Then, after the update, setting it back using:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=OFF

This is per this thread over at the EF6 repo on Github.

It should be noted that the following is also reported in that thread to work though I have not tested it because the former worked so well for me:

UPDATE STATISTICS sys.syscolpars
UPDATE STATISTICS sys.sysschobjs
UPDATE STATISTICS sys.syssingleobjrefs
UPDATE STATISTICS sys.sysiscols

They also punted this back to the SQL Server team and opened up this issue over at Microsoft Connect.

Parsonage

This worked for me. PLEASE NOTE: The accepted answer sets the database compatibility level. I believe that setting it to 110 would set the legacy cardinality estimation on. We need more people testing this answer out before trying the accepted answer. – Stutter

I think that ALTER DATABASE is only for SQL Server 2016. Version 2014 doesn't recognize that command. It recognizes though those UPDATEs but they didn't help, the wizard had the same issue. What worked for me was changing the compatibility level. – Nd

This should be the accepted answer. Added this to my post deployment script IF '$(DatabaseName)' = 'dev-App' ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON – Sachet

Alter Database worked for me as well,I'm using SQL server 2017, not sure why this still exists in 2018 – Construct

Yes, I made LEGACY_CARDINALITY_ESTIMATION=ON @mssql2016 and it worked. Do we really need to make it off again? What happens if we dont? It doesnt explain here learn.microsoft.com/en-us/sql/t-sql/statements/… – Warsle

@Warsle Since the DB we use to generate the edmx is our dev DB, I never set it back to OFF. – Parsonage

@Warsle Just found a doc that explains the different Cardinality Estimation methods in more detail with info regarding when one may be more advantageous than another learn.microsoft.com/en-us/sql/relational-databases/performance/… – Parsonage

Answer 3

Today, my coworkers and I left the wizard alone and let it update for ~10 minutes. While it took quite a while, it did complete. This is the best solution for us (for now), since we are unable to set the compatibility level of our DB without the proper permissions.

Reincarnate

Yes, it does the job but in a terribly long time. – Rummage

@Rummage Yes, it is irritating. I spent ~30 minutes the other day updating a large model. Quite frustrating. – Reincarnate

Mine has been going for about an hour and I'm only trying to add 1 table (this is the initial setup). Classic "halting problem", but I think I'll give up on it now... alas. – Gaylord

Answer 4

Changing SQL Server compatibility level or trace-flag 9481 is no option for me.

I gave EntityFramework Reverse POCO Generator a try.

https://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838

It's a configurable generic T4 Template and works very well till now.

It even has an Option for the mentioned trace-flag

IncludeQueryTraceOn9481Flag = false; // If SqlServer 2014 appears frozen / take a long time when this file is saved, try setting this to true (you will also need elevated privileges).

Ironically it works fast even if the flag is off :) Seems like they are using different queries for metadata compared to the VS EF Designer.

Prototherian

Answer 5

I still had to do this with Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64) Jul 3 2017 02:25:44 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) using Entity Framework 6.2.0. How in the world has this not yet been solved?!

Slipcase

Answer 6

MariaDB Lethargic Manual Remedy

• Using EF6, VisualStudio 2015 against MariaDB 10.2.
• Just like @Santhos indicated, blowing away a ton of time for me. Seems like VisualStudio is slow at parsing that huge XML file (my development machine only has 8gb RAM). No joke, about 15 minutes to refresh.
• I learned that manually 'resetting' a couple of files, then proceeding through the GUI update squared me away quickly. I'm in and out with shiny new models ~1-2 mins.

Manual Refresh Steps

1. Replace the contents of your .edmx.diagram file with the EDMX Diagram Boilerplate below
2. Replace the contents of your .edmx file with the EDMX Runtime Boilerplate below
3. Return to VisualStudio, double-click your EDMX > in the right click in the empty screen > Update Models from Database

EDMX Diagram Boilerplate

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
 <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram DiagramId="820459acb0f543cfaf7db8643f38c2d6" Name="Diagram1" ZoomLevel="85">
        </Diagram>
    </edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

EDMX Runtime Boilerplate

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="ShareDirectModel.Store" Provider="MySql.Data.MySqlClient" ProviderManifestToken="5.5" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
        <EntityContainer Name="ShareDirectModelStoreContainer">
          </EntityContainer>
      </Schema></edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="ShareDirectModel" Alias="Self" annotation:UseStrongSpatialTypes="false" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
        <EntityContainer Name="ShareDirectContext" annotation:LazyLoadingEnabled="true">
          </EntityContainer>
        </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="ShareDirectModelStoreContainer" CdmEntityContainer="ShareDirectContext">
          </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
    <Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </Connection>
    <Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="true" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="true" />
        <DesignerProperty Name="UseLegacyProvider" Value="false" />
        <DesignerProperty Name="CodeGenerationStrategy" Value="None" />
        <DesignerProperty Name="DDLGenerationTemplate" Value="$(VSEFTools)\DBGen\SSDLToMySQL.tt" />
      </DesignerInfoPropertySet>
    </Options>
    <!-- Diagram content (shape and connector positions) -->
    <Diagrams></Diagrams>
  </Designer>
</edmx:Edmx>

Terzas

Answer 7

This kind of problem usually happen because the server running SQL server it self is not in good condition. Maybe the disk space or memory on the server is getting too low to finish the task.

Check the server that running your DB.

Withoutdoors