Setting up Entity Framework for Production Use

Posted by: Sumit Maitra , on 7/11/2012, in Category Entity Framework
Views: 53172
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).

blog-post-entity

- 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

blog-post-controller

- 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

possible-error

- 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

connection-string-in-web-config

  • 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.

web-config-change-default-connection-string

- 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

updated-solution-structure

- 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:

 

database-structure-in-management-studio

- 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:

on-model-creating

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.

exception-on-model-change

Thus to keep up with rapid changes in Schema during development, we change the strategy to DropCreateDatabaseIfModelChanges strategy

drop-and-create-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.

ef-deploy-sample-context-constructor

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.

updated-ef-deploy-sample-context

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

changes-to-db-context-instantiation

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

set-initializer-strategy-to-null

2. Next use Management Studio Tasks to extract the DB Schema as a SQL script.

a. Launch the Generate Scripts Wizard

launch-generate-script-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.

generate-script-selected-tables

c. Save the Script

generate-script-save-files

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.

appharbor-go-to-sql-server

- The configuration page looks as follows

appharbor-database-config

- 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 also download the Zip file

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Sumit is a .NET consultant and has been working on Microsoft Technologies since his college days. He edits, he codes and he manages content when at work. C# is his first love, but he is often seen flirting with Java and Objective C. You can follow him on twitter at @sumitkm or email him at sumitkm [at] gmail


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by Tyler Warshaw on Wednesday, July 11, 2012 10:48 AM
After reading your post I have no doubt that EF is ready for building Enterprise applications. Do you have any idea how many concurrent users can it support?
Comment posted by Michael Friis on Thursday, July 12, 2012 10:35 AM
If you're not interested in manually scripting the database and running the script on your AppHarbor database, you should check out this guide on using automatic migrations with Entity Framework: http://blog.appharbor.com/2012/04/24/automatic-migrations-with-entity-framework-4-3
Comment posted by C Platt on Friday, July 13, 2012 7:41 AM
"Give me a +1 if you think it was a good article"

I am giving you +10 as I think it was an excellent article!
Comment posted by shahrooz jafari on Monday, July 16, 2012 12:22 PM
tnx

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel