Multiple back ends for Entity Framework

Question

Can two different ends be used for a .net Entity Framework project?

I need to support full support for programmable stored procedures on an SQL server, when available. I need to support only tables in the .mdb file when the SQL server is unavailable.

All of the business logic above the Entity Framework uses object abstractions rather than directly accessing the database. An object model can use stored procedure calls or dynamic sql to read / write database tables.

Is it possible to create two logically identical entity models, each of which has different mappings to the database (one is managed by the infrastructure, one is managed by stored procedures) and switched between them at runtime based on functionality, the end of the storage mechanism?

Answer 1

You should see this similar question . I'm not sure you can do this at runtime, but I found that this is possible after deployment. Caution, there are definitely pitfalls.

The main differences between the EDMX files that are generated between the various database backends are MSL and SSDL. I created an EDMX file separately from each database. They both have the same logical data model (CSDL). Then I extract the MSL and SSDL files and save them in separate files. Once this is done, you can indicate in the connection string the exact location of these files (as shown):

<add name="DBConnection" connectionString="metadata=C:\sqlServerEntities.csdl|C:\sqlServerEntities.ssdl|C:\sqlServerEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=[machinename];Initial Catalog=[databasename];Persist Security Info=True;User ID=[user];Password=[password];MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" /> <add name="DBConnection" connectionString="metadata=C:\mdbEntities.csdl|C:\mdbEntities.ssdl|C:\mdbEntities.msl;provider=[mdb provider namespace];provider connection string=[DB connection string]" providerName="System.Data.EntityClient" /> 

You will need to use the appropriate connection string at runtime depending on which database you are connecting to. I am concerned that you might have difficulty using stored procedures in one scenario and table-based matching in another.

One more note: you cannot leave both EDMX models in your project, otherwise you will create compiler errors (based on duplicate class definitions). However, you must leave one in your project so that the compiler knows about the generated logical classes.