CODEWITHSUNDEEP
c#sql-serverentity-frameworksql-server-ce
landi
landi

Reputation: 343

Same EDMX file for different Providers

I'm working on a project where I have a local database (SQL CE) which is used as sort of a buffer while no connection to the server exists. On the server I want to use the same database-layout.

Of course I want to use the same EDMX-File which is in a Common.dll available on the Server and the Client.

In the Client I have a connection string with provider=System.Data.SqlServerCe.3.5 while it is provider=System.Data.SqlClient on the server.

My problem comes when I want to save sth on the Server-side: "The provider manifest given is not of type 'System.Data.SqlClient.SqlProviderManifest'."

Is there even a chance to use the same EDMX-File on both parts? Or is there any best practice how to handle such a constellation?

Thank you for your help!

EDIT: My main Question is: Is it possible to enable an EDMX File to use with different Providers? In my case System.Data.SqlServerCe.3.5 and System.Data.SqlClient!

Upvotes: 4

Views: 1640

Answers (3)

Lawrence Wagerfield
Lawrence Wagerfield

Reputation: 6611

We have the exact scenario working on a production application. We used a T4 template to generate a SQLCE EDMX file based on a canonical SQL EDMX file. This leaves you with 2 EDMX files which you can switch between when instantiating your single context....

    serverType = "sqlserverce" (or) "sqlserver";
    var entityBuilder = new EntityConnectionStringBuilder
    {
        Provider = ...,
        ProviderConnectionString = ...,
        Metadata = string.Format("res://*/{0}.{1}.csdl|res://*/{0}.{1}.ssdl|res://*/{0}.{1}.msl", EdmxName, serverType)
    };

The SQLCE EDMX T4 code looks like this...

<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ output extension=".edmx" #>
<#@ assembly name="System.Xml.dll" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Xml" #>
<#
 var document = new XmlDocument();
 document.Load(this.Host.ResolvePath("DbContext.edmx"));

 var namespaceManager = new XmlNamespaceManager(document.NameTable);
 namespaceManager.AddNamespace("edmx", "http://schemas.microsoft.com/ado/2008/10/edmx");
 namespaceManager.AddNamespace("ssdl", "http://schemas.microsoft.com/ado/2009/02/edm/ssdl");

 var storageModelsNode = document.SelectSingleNode("//edmx:StorageModels", namespaceManager);

 foreach (XmlElement schemaNode in storageModelsNode.SelectNodes("ssdl:Schema", namespaceManager))
 {
     schemaNode.SetAttribute("Provider", "System.Data.SqlServerCe.4.0");
     schemaNode.SetAttribute("ProviderManifestToken", "4.0");

     foreach (XmlElement propertyNode in schemaNode.SelectNodes("ssdl:EntityType/ssdl:Property[@Type='varbinary(max)']", namespaceManager))
     {
         propertyNode.SetAttribute("Type", "image");
     }

     foreach (XmlElement propertyNode in schemaNode.SelectNodes("ssdl:EntityType/ssdl:Property[@Type='varchar']", namespaceManager))
     {
         propertyNode.SetAttribute("Type", "nvarchar");
     }
 }

 var stringBuilder = new StringBuilder();

 using (var stringWriter = new StringWriter(stringBuilder))
 using (var xmlWriter = new XmlTextWriter(stringWriter) { Formatting = Formatting.Indented })
 {
     document.WriteTo(xmlWriter);
 }

 Write(stringBuilder.ToString());
#>

Upvotes: 3

Arthur Vickers
Arthur Vickers

Reputation: 7533

This can be done by keeping different copies of the SSDL, as mentioned in the other answer, but it's usually much easier to do it with Code First. Code First will automatically create the different models based on the connection being used. So you just need to point the same code at either the SQL CE database or the SQL Server database. No messing with different SSDLs required.

Upvotes: 0

landi
landi

Reputation: 343

I have found a solution which is described over here: http://www.codeproject.com/Articles/309858/Preparing-an-Entity-Framework-model-for-multi-prov

It's not perfect as you have to copy a part (the SSDL) out of the EDMX file. So you need to remember to update this file as soon as you make changes to your EDMX file.

If someone has a better way I'm still open for it.

Upvotes: 0

Related Questions