Using Entity Framework with a different version of SQL Server

Question

I think most of you can work with the latest version of SQL Server and deploy it on the target machine, which has a relative old version. Similar to using SQL Server 2008 on a production computer and SQL Server 2005 on a target server. Working in this situation with the Entity Framework, it disappointed me a little.

I designed a database schema using SQL Server 2008, has a data type table field. the application works fine on my machine. But on the target server, the entity infrastructure generates a T-SQL query for SQL Server 2008, which results in the data type not supporting an exception (datatime2). The only walk I know is to change the ProviderManifestToken in the edmx file every time you update the entity model. Is there any way to make this work smarter? Thanks!

Answer 1

UPDATE: I wrote a review on my blog in my series of tips to talk a little more about it.

-

, , - ProviderManifestToken.

EF , , .

, SSDL (Storage Database Model) EDMX SQL-, ProviderManifestToken, , .

, SSDL , .

CSDL/MSL/SSDL , , :

• ""
• " " " "
• SSDL ( bin\debug) SQL2005, DateTime2 DateTime ..
• ConnectionString, SSDL...

:

var connStr = @"metadata=.\model.csdl|.\modelSQL2005.ssdl|.\model.msl;
              provider=System.Data.SqlClient;
              provider connection string="
                  Data Source=.\SQLEXPRESS;
                  Initial Catalog=TipsDatabase;
                  Integrated Security=True;
                  MultipleActiveResultSets=True
              "";

using (var ctx = new MyContext(connStr))
{

}

, , , , .

,

Answer 2

ProviderManifestToken . .

Answer 3

MSBuild.Community Tasks, BeforeBuild, XmlUpdate, ProviderManifestToken 2005 , - , .

BeforeBuild:

<Target Name="BeforeBuild">
<XmlUpdate Prefix="ssdl"
            Namespace="http://schemas.microsoft.com/ado/2009/02/edm/ssdl"
            XPath="//ssdl:Schema/@ProviderManifestToken"
            XmlFileName="Model.edmx"
            Value="2005"/>

Answer 4

You can try setting the compatibility mode of your database.

Thus, even if you are on SQL 2008 Server, the database is a SQL Server 2005 database.

I have not tested this, but theoretically this should trick EF into talking to the SQL Server 2005 database.