Is it possible to use the same Entity Framework model for both SQL Express & SQL CE 4.0?

Question

I'm currently working on a SOA project where at some locations it would make sense for the service to use a lightweight database (SQL CE 4.0) while at other locations a more robust database is desirable (SQL Express right now, but possibly scaling up to larger editions).

Even though the model and table structure is identical for both SQL Express and SQL CE, I can't figure out how to get Entity Framework to use the same EDMX to work with both databases. The conceptual model is identical for both, and the only difference in the storage model is the provider name that is used to access to the database.

Am I missing something, or do I need to keep two basically identical models around, one for each database.

I'm using .NET 4.0, Entity Framework and VS 2010 SP1

Answer 1

Unfortunatelly I think you have to create two separate SSDLs with different provider and manually keep them in sync (this can be actually done by some post build script wich will copy ssdl file and replace a provider). Still you have to be sure that table structures are same:

• same table names
• same column names
• same data types

The last point can be critical because as I know SQL CE doesn't support some types common in SQL Server. For example I think navarchar(max) and nvarbinary(max) is not supported in SQL CE.

To force EF to create SSDL file instead of including it as a resource change Metadata Artifcat Processing (in properties of EDMX designer) to Copy to output directory and modify connection string. Here is a related article.

Answer 2

You can do this if you don't mind switching to Code First. Gallery Server Pro 3.0 uses Code First to target either SQL CE or SQL Server, depending on the user's preference. There is a single code base with the only difference being the connection string in web.config.

It's an open source project so you can see how it's done in the code.