Updating Entity Framework EDMX Models After Database Migration

Question

I have an issue where I have generated multiple EDMXs and models using the Entity Framework Database First approach, and these models were mapped to tables/stored procedures on a remote SQL Server 2012 instance initially. Now that we have migrated to SQL Server 2016, whenever we try to update these EDMX models by adding new tables, refreshing models, etc., the Update Wizard freezes indefinitely.

I have done some research in regards to this issue, and I've seen where some people have had luck changing the Compatibility Level on the databases in question, but that is something we would like to avoid if at all possible. After migrating, the Compatibility Level was set to SQL Server 2016 (130) for all databases on the server, specifically for query optimization purposes per our Microsoft database consultant.

Something I have tried changing manually is the ProviderManifestToken in the EDMX's XML content by setting it from "2012" to "2016", but this seems to cause other issues. I get this error message after doing so:

Error Message

Other possibly useful information:

• Using Visual Studio 2015
• Entity Framework version 6.1.3

Has anyone had any luck migrating from SQL Server 2012 to 2016 for their database while keeping clean mapping to auto-generated Entity Framework models? It seems there would be a built in tool to clean up the EDMXs to map cleanly to a new and updated instance of SQL Server.

Answer 1

What we ended up finding as a solution is to change Legacy Cardinality Estimation to ON for the database in question by going to Properties > Options in SSMS. This allowed us to keep the compatibility level set to SQL Server 2016 on the database.

As an additional note, when updating these models through the Update Wizard in Visual Studio, the models would seemingly take forever to update; we ended up leaving the wizard running over night and the models for one of our databases finally finished updating after ~6 hours. So they will eventually finish updating, but not in a realistic time frame for someone to work with.

After making the configuration change, the models would update instantly, as one would expect.

The issue that tipped us off to making this change can be found on Entity Framework's GitHub page here. It appears that the contributors to Entity Framework have not found fault on their end and have handed this over to the SQL Server team, but no fix has been implemented at the time of this posting (to my knowledge).