Abstract: Over the last couple of years, Entity Framework has steadily become the de facto data access story from Microsoft. With EntityFramework Code First, it became even easier to get started with an application. While EF lets you get off the ground and running fast, it’s deployment story (both first time and subsequent upgrades) has been a little sketchy. With the release 4.2 and EF Migrations, the upgrade story has been simplified a little but the first time deployment story still has a couple of points to keep in mind. Today we will explore these points and see how we can deploy a Database that is accessed by EF (Code First). We keep Migrations for another day.
Over the last couple of years, Entity Framework (EF) has matured steadily to become the de facto Data Access story from Microsoft. We all love the speed with which we can get off the ground with our prototypes. However, deploying the database to a typical shared hosting solution has a few challenges. Today we will take a simple application and see what it takes to deploy the database on AppHarbor. Use of AppHarbor is a matter of convenience. We could use any hosting provider; most of them have similar restriction with respect to the database creation and deletion permissions.
The Sample Application
Let’s say we have a very simple application with a single table that maps to an entity called BlogPost. For additional tables, we will use the ASP.NET Internet Template that includes the Authentication and Authorization entities. The Default Providers will create the required tables for these as well.
Before the first run, let’s add the BlogPost entity and add a default set of views using the default scaffold tooling.
The BlogPost Entity and Scaffolding Setup
- Add a new Class – BlogPost, in the Models folder. It has three properties, Id (of type int), Title (of type string) and PostDetails (of type string).
- We save and build our project at this point
- Next we right click on the Controller folder in Solution Explorer and select ‘Add New Controller’ to bring up the New Controller Wizard.
- We setup the Controller as follows:
- Name: BlogPostController
- Scaffolding Options: MVC Controller with read/write actions ad views, using Entity Framework
- Model Class: Select the entity created above e.g. BlogPost
- Data Context Class: Press down arrow to select ‘Add New’ option. This will show another popup where we select the default Context Class name provided. As shown below it should be something like [Solution Name].Models.[ProjectName]Context
- Click on ‘Add’ to continue.
- The Scaffolding infrastructure will try to connect to the default .\SQLEXPRESS database. If it fails to find the DB, it will give an error as follows
- In case of an Error, click on OK to dismiss the dialog and open Web.config file. Update the server name in the ‘Data Source’ to point to the correct SQL Server (below we see the SQL Express instance is called SQLEXPRESSR2 instead of the traditional SQLEXPRESS. Change the ‘Initial Catalog’ name to something like EfDeploySample, instead of the default string
- Now is a good time to ensure there is only one connection string being used. If you use the ‘Internet’ template, a connection string with the name ‘DefaultConnection’ is added and the ‘Authentication’/’Authorization’/’Personalization’ providers use this ‘DefaultConnection’ connection string. Delete the connection from the <ConnectionStrings> section and replace the reference to it, with the above connection string wherever required.
- Repeat the Add Controller step above. If successful, the following will be added to the solution
- A new folder BlogPost will be added in the ‘Views’ folder, with the default views for Create, Delete, Details, Edit and Index.
- The BlogPostController is added to the Controllers folder
- The EfDeploySampleContext is added to the Models folder
- To make use of the Authentication framework that came along, we will add the [Authorize] attribute on top of the BlogPostController, so that actions on it cannot be invoked unless a user is Logged in. With that, our application is set to go.
- Run the Application. Use the ‘Register’ link on the top to register a username. Then navigate to /BlogPost URL to get to the Index page of the Blog Posts. Add a sample Post. With our application up and running, it’s time to attend to the Database Migration piece. If we look at our database in Management Studio, it looks as follows:
- Pretty neat considering the fact that we didn’t write a scratch of SQL to generate those tables.
EF Database Initialization Strategies
EntityFramework comes with a set of default initialization strategies. Without any configuration, it is set up as ‘Create if schema does not exist’. You can explicitly specify it in the DB Context class by overriding the OnModelCreating method as follows:
In this strategy, EF creates the Database if it doesn’t find it. Once created, it will not update the schema unless you drop and recreate again (Yes, Migrations will update without a DB drop, but for now we are not looking at Migrations).
To test the Default strategy, let’s drop our data from our Database and Run the application. As we try to Navigate to the BlogPost index, we will get re-directed to the Login Page. Once we register, if we go back and check the DB the new database would have been created.
Next, login and navigate to BlogPost. Once you are at the Index page, if you refresh the database schema we will see the BlogPost table has been re-created.
Strategy to keep up with rapid changes during prototyping
So far so good, we are fine with our automatic schema setup. However in prototyping stage, we will be adding/modifying and deleting schema elements. In such a case, CreateDatabaseIfNotExists becomes a hindrance because if you don’t drop the database manually, it will throw an InvalidOperationException complaining that the backend has changed.
Thus to keep up with rapid changes in Schema during development, we change the strategy to DropCreateDatabaseIfModelChanges strategy
Now when EF detects a change in Model, it will drop the DB and create the schema again. Remember NO data will be saved.
Connection String and Schema Names
If we look at the constructor of the MVC Tooling generated EfDeploySampleContext, we will notice a string parameter that corresponds to the name of the connection string in the web.config.
As a part of clean setup, it is recommended that the connection string be passed into DB context via the constructor.
Next important thing is to pass the schema name. We often take access to ‘dbo’ for granted, but some hosting providers do not provide us with dbo access. If we come across such a scenario, we have to do additional mapping between entities and the tables.
To handle these two configurations, we update our EfDeploySampleContext constructor by passing the connectionName and schemaName parameters. We then pass the connection name to the base DBContext and use the schema name to map our entities to the tables in DB explicitly.
As highlighted above, the default constructor has been modified to take in the two parameters and a guard clause ensures that if the parameters are not present, the instantiation throws an exception.
To match the above changes, we have to update our controllers so that we can pass these new values. To keep the schema Name configurable, we will add a key in the AppSetting section of our Web.Config and pass that value into the DbContext’s constructor.
Our DbContext initialization code in the controllers would then look as follows
Going Live
With all the above changes we are almost set to go live. The only two things remain
1. Update the DropCreateDatabaseIfModelChanges strategy back to null. This way EntityFramework will not try to make any changes to the DB
2. Next use Management Studio Tasks to extract the DB Schema as a SQL script.
a. Launch the Generate Scripts Wizard
b. Choose Objects and Select all Tables/objects. Do not select the ‘Script entire database and all database objects’ option. It tries to create a new database. This is not what we want. Most hosting providers will not give us ‘create’ or ‘drop’ database rights.
c. Save the Script
Deploying to AppHarbor
Details of how to deploy code on AppHarbor have been explained in this article . We will look at the SQL Server setup more closely here.
- In AppHarbor, you have to create an application and select SQL Server as an AddOn. Most hosting providers will give you a connection string or server-name, user-name and password combination. AppHarbor provides you with the same.
- Once the Add-On is installed you have access to one Database that you can access using the ‘Go To SQL Server’ link.
- The configuration page looks as follows
- In the ‘ConnectionString alias’ setting, edit the alias and provide the same name as the connection string in our web.config i.e. EfDeploySampleContext
- This actually creates a web.config transform that replaces the Web.config’s connection string setting with this database’s connection string, when the code is deployed on AppHarbor
- If you are not deploying to AppHarbor and do not have a transform defined, make sure you update your connection string manually before deploy.
- Connect to the server using the above connection information using the SQL Server Management Studio.
- Open the SQL Script (remove the ‘using [dbname]’ if required) and execute it on the server. That’s it. Done.
- Deploy the code and if AppHarbor does not throw an error, your application is now live. You are good till the next upgrade cycle comes across. We will look at EF Migrations in an upcoming article to handle that scenario.
Conclusion
To conclude, we saw how with a little bit of self-enforced processes we can easily migrate our EF prototypes to production environment. However, the real challenges in a production deployment are the updates and revisions and further schema changes. We will look into EF Migrations in the future that will help us resolve that scenario as well.
The code Repository is at https://github.com/dotnetcurry/EFDeploySample and you can alsodownload the Zip file
No comments:
Post a Comment