Entity framework working with both production and development database

Question

I am going to start a new project and i want to try EF model first and i want to be able to work with a development database in MSSQL and have an production database in MySQL.

Is there anyone out there with experience in this? Is this possible?

I have read about Entity Designer Database Generation Power Pack. Does it support this?

I want it to be simple to add columns and table created to the model to both my production (MySQL) and development database.

Answer 1

This will not work because type of the database is part of EDMX file (model first). You will need two different EDMX files with same entities but different storage definition (SSDL part of EDMX file). You will also need MySql provider on your development machine (I'm not sure it this also demands MySql itself). And for last you will need different connection strings using metadata from correct EDMX file.

This whole is pretty bad idea because you must still test your application against MySQL. The idea that you will develop application on one database server and it will just work on another is not entirely correct.

Answer 2

ASP.Net WebDeploy allows you to automatically transform your web.config based upon where you are deploying to. eg it can send one connection string when deploying to the test server and a different connection string when deploying to the live server.

http://www.iis.net/download/WebDeploy

The high level steps:

1. Create a new build configuration through configuration manager
2. Set the values appropriately in each newly created config file
3. Build and publish
4. Done and never have to remember to change values in multiple config files (development, staging, release etc).

http://msdn.microsoft.com/en-us/library/dd465318.aspx

However - why would you develop using one database and have it in production in another? The differences would mean that whatever you developed would not be suitable for production.