Entity Framework and Different Environments (Dev/Production)

Question

I've recently undertaken a new project using ASP.NET MVC and I'm not terribly familiar with the technology. I'm still in the very early stages and am just about to start teaching myself how to use Entity Framework to deal with my data access code. I'm just having a rather tough time making heads or tails of what software/tool/feature can do what, but I think I'm on the right track with EF.

I'm looking for something that is going to be able to see the differences in my local database to that in my production database (schema, not the actual data in it) and be able to publish that to a SQL Server in our local network. In my mind, I think of it like a DVCS for databases.

Traditionally (like most I'd say) I would write my own data access code using SQL in my code or stored procedures etc. and I'd deal with the database side of things myself. That is no longer an option for me though because I need to set up separate environments for development and production, and I don't really have the time nor desire to manually write all of that code any more. Dev will be local to the dev machine and production will be hosted on a server on our local network.

I've recently started using the "Publish" feature in Visual Studio and I have managed to set that up to work pretty well, publishing new versions to the directory which my IIS server is looking at to serve up the site.

I use Git and Sourcetree to deal with DVCS for my project code, however I've never personally used or found a solution to do something similar for databasing. At first I thought I was looking for some kind of DVCS for SQL Server, but that didn't return much. Essentially I just want some kind of tool that is going to manage version differences between local and production databases, and I think that is something EF can do for me.

For the databasing, I'm using SQL Server. I haven't set up a database yet for this project, so I'm thinking code-first EF would be the best approach. I want to ensure that EF is using a database in SQL Server though, which I believe I can set by specifying a connection string to use as opposed to one in Visual Studio. I need db access through SSMS occasionally so I can play with indexes and backup plans and such that I'd normally do through SSMS, but I'd still like EF to do the bulk of the work for me.

1. Can I achieve my desired goals using Entity Framework the way I'm hoping? That is, having a tool that deals with database differences and updates between versions? I don't want to have to figure out what tables/columns etc. I've changed locally and have to somehow work that into the production database, but I also don't want the data in the production db to change, just the structure...
2. I'd love for anyone to provide advice on how this setup should be constructed, or even links to a tutorial on how to set it up. I'm sure I'm not the first person to want to do this, and I know I wont be the last.

Essentially I'd just like to click the "Publish" button and have all local differences in both code AND database be published to the production server.

Answer 1

Can I achieve my desired goals using Entity Framework the way I'm hoping?

That can be easily done by the EntityFramework. What you are looking for is called "EF Migrations". The EF Migrations scans your POCO classes to see if they match your database. If there is something new or changed, it will update the database tables for you. To learn how to use EF Migrations check out this link https://msdn.microsoft.com/en-us/data/jj591621.aspx

The connection string can be resolved by Web.Config/App.Config transformations. The Web.Config transformation automatically replaces the development connection string by the production connection string during the publishing. To see how to use web.config transformation, take a look at this link http://www.asp.net/mvc/overview/deployment/visual-studio-web-deployment/web-config-transformations

During the publishing, you can tell VisualStudio to check which migrations were still not applied to the production database, and apply those migrations, automatically. This article http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/migrations-and-deployment-with-the-entity-framework-in-an-asp-net-mvc-application shows how to apply migrations when publishing to Windows Azure.

Hope it helps!

Answer 2

In entity framework, if you're working with an existing database, you can set the connection string at runtime.

The DbContext constructor allows you to include a connection string either by name (in the app.config or web.config file depending on the application) or by the full SQL connection string, but with the additional metadata section.

For example if you had a DbContext defined as:

public YourModelContainer() : base("name=YourModelContainer")
{
}

note that the call to base("name=...") allows you to specify a named entity framework connection string in your config file.

To build one at runtime, you would create an overloaded constructor with:

public YourModelContainer(string connectionString) : base(connectionString)
{
  // where connection string includes the metadata section
  // metadata=res://*/Models.YourModel.csdl|res://*/Models.YourModel.ssdl|res://*/Models.YourModel.msl;provider=System.Data.SqlClient;provider connection string="data source=.;initial catalog=YourDatabase;user id=dev;password=p455w0rd;MultipleActiveResultSets=True;App=EntityFramework"
  // This is just a concatenation of the metadata with a SQL connection string, you can use the EntityConnectionStringBuilder class if you're building this manually at runtime
}

see https://stackoverflow.com/a/26043339/474702