ProviderManifestToken 2008 or 2012

Question

The application:

• .NET 4.5 C#
• Uses EF6 with database first approach
• Supports SQL Server 2008R2, 2012 and 2014

This question is regarding the ProviderManifestToken attribute of the auto-generated edmx file.

Depending on which version of the database is used (different developers have different version of the database) to update the model from the database, the value of the ProviderManifestToken attribute either gets set to 2008 or 2012. Until we discontinued support for SQL Server 2005, we made sure that the value of this attribute remained 2005 (see this SO article for more information).

I am wondering if there are similar concerns between 2008 and 2012. What does this attribute really do? It is safe for me to leave it to either value without causing any issues at run time? Or should I make sure that this is always set to 2008 or always set to 2012 to make sure that the application works fine with the versions of the database that we support?

MSDN is not very useful in describing this property. It states that ProviderManifestToken is

A string that identifies that version of the database server being used. For example, the SQL Server provider uses the string "2008" for SQL Server 2008. This cannot be null but may be empty.

Thanks!

Answer 1

Since EF 6.1.2, having 2012as ProviderManifestToken value will cause EF to generate SQL using offset ... fetch ... syntax (not supported in MS Sql 2008) in a bunch of cases, like paging. So, if you let it get changed to 2012, you will very likely have troubles on apps running over MS Sql 2008.

Setting it to 2008 for running on newer Sql Server should be safe for now: deprecated features are usually not dropped until v+2 (where v is the first version in which they have been deprecated). Sorry, I am no more able to find a reference for this statement.

If you need to have it match the target DB for each of your deployments, this other SO answer may help you.

If you wish to set it as 2008 in any cases, ensuring that development DBs are all set to 100(SQL Server 2008) compatibility level should do the trick. Unfortunately it looks like EF designer does not always honor the compatibility level of the database on which it runs for updating the model. So when using it, it may "upgrade" the ProviderManifestToken value, causing troubles if the dev commit it and let it get deployed.

I was not able of reproducing this on my own workstation. But some devs in my company had the trouble, though they claim their local db is in 100 compatibility level.

It appears they were not having their SQL Server Management Studio (SSMS) default scripting option correctly set. (Tools => Options... => SQL Server Object Explorer => Scripting => Script for server version). Just in case, I made them change it, and for now they report no more having the undesired ProviderManifestToken value upgrade.

This looks quite weird to me, as SSMS options should not have any impact on EF designer. Only the database on which is run the designer should be taken into account IMHO. And as already stated, I was not able to reproduce this trouble myself, even by tweaking that option.