When using the Entity Framework (certainly the version 4, the first guess but I never tried), the model is "ties" to the version of the database that was indicated in the initial phase. This bond is written to the model, specifically in ssdl in property ProviderManifestToken and this property is not editable by the designer. Obviously from that property depends on the type of SQL generated for the various interrogazioni.
Questo può portare a problemi/fastidi se nell’ambiente di sviluppo si usa una versione di SQL Server differente da quella che si ha in ambiente di test/produzione. Se ad esempio si sviluppa su SQL Server 2008 ma sull’ambiente di produzione del cliente si ha SQL Server 2005 il nostro progetto rischia di non funzionare, soprattutto se usiamo dei campi data: la versione per SQL 2008 sfrutta il tipo datetime2 che in SQL 2005 non esiste.
La soluzione che ho adottato e che funziona correttamente in un sistema già rilasciato presso un cliente prevede i seguenti passi:
- Impostare il modello affinché non “embeddi” i file, tra cui l’ssdl, nella dll ma li copi in the output folder - in other words in the properties of the model set Metadata Artifact Processing to "Copy to Output Directory" instead of "Embed in Output Assembly"
- Create a simple console application that takes the file and go ssdl to replace the value of ProviderManifestToken (at the bottom shows a simple code example), and "linked" to the project that contains the template
- Set the post-event buil the project containing our model to call the console application and then copy the newly created Template files (CSDL, MSL and SSDL) in the "bin" of our startup project (education xcopy)
- Set the configuration file (web.config in my case) that the connection string points to the copied files correctly with the post-build operation
The simple console application code that I have prepared is as follows:
1: static void Main ( string [] args)
2: { 3: ModelName string = args [0];
4 : string filePath = string . Format ( ". / {0}. ssdl" , ModelName);
5: 6: was edmx = new XmlDocument ();
7: edmx.Load (filePath); 8: was NSM = new XmlNamespaceManager (edmx.NameTable);
9: nsm.AddNamespace ( "a" , "http://schemas.microsoft.com/ado/2009/02/edm/ssdl" );
10: was x = edmx.SelectSingleNode ( "/ a: Schema" , NSM);
11: x.Attributes [ "ProviderManifestToken" ]. Value = "2005" ;
12: edmx.Save (filePath); 13:} In this case wants the name of the model in order to deduce the input file name ssdl, and sets the default ProviderManifestToken in 2005. Obviously the change to take in input the value to be set to ProviderManifestToken is trivial.
Some useful links:
- Known Issues in the. NET Framework Data Provider for SQL Server (SqlClient) for the Entity Framework - Chapter "Targeting the Correct SQL Server Version"
- Entity Framework on SQL 2000 vs.. Sql 2005 and ProviderManifestToken – Spiegazione del perché hanno messo il provider manifest token nel file ssdl
- Entity Framework uses incompatible types (e.g. datetime2) when deployed to systems with older SQL Server versions, even when source database had compatibility level set correctly – Segnalazione del comportamento durante la fase di sviluppo e marcato come “won’t fix”
P.S. L’errore dato dall’applicativo dal quale è emerso questo comportamento legato al ProviderManifestToken è stato “ SQL Server in use does not support datatype datetime2 ”.
