Download the code to this articleI have to admit that I'm a fanboy of Microsoft's code-first feature that was released with ADO.NET Entity Framework 4.1. Code-first design lets you create the domain model for your application by building Plain Old CLR Objects (POCOs), with no Entity Framework code whatsoever and without using any base classes. You can create a context class through some clever features that almost magically turns the objects into fully functional entity data objects. Even more amazing, you don't have to worry about creating the database at all. Assuming that you can live with the default conventions, Entity Framework will automatically create the database for you when you first run the application. And if you can't live with the default conventions, you can override them using either data annotations on the model or with the DbModelBuilder API, often called the "fluent API" in Entity Framework.

Code-first is a really great feature for developers, letting us focus on the data objects in an application rather than on the database or the Entity Data Model. In fact, a code-first application won't even have a model, an .edmx file in the project, or any XML mapping code. Entity Framework takes care of inferring the model, including the conceptual, storage, and mapping models, at runtime. It's really a code-centric style of writing database applications.

Code-first design is implemented so nicely that I’m confident that it'll become the method of choice for new application development. My enthusiasm was so complete for code-first design that I led a session about it during PASS Summit 2011 in the application development track.

While I was planning and writing the session, I realized that I had a problem to address. The conference is attended by both developers and DBAs, and past experience told me that I was sure to have some DBAs in my session no matter how developer-focused the topic was. I had to be able to address the inevitable questions about how code-first design affects DBAs' concerns, such as scalability, security, and stability of a database server and its databases. Code-first design is at its heart a developer technology, one that cuts deep into the DBA's world.

Expecting tough questions, I prepared for questions that I'd likely get from DBAs during and after the session. I figured the biggest concerns would be about deploying code-first databases, particularly to production servers. I got ready to talk about deployment strategies and scenarios, wherein the automatic database initialization, creation, and data seeding happen within development and test environments, and hooking into an organization's typical deployment procedures for production servers. I was ready.

Overall, I think the session went pretty well. (No one threw any rotting fruit at me on stage, which is always a good sign.) I was surprised and pleased by the breadth of questions from both developers and DBAs in the audience. We got into some deep discussions about creating and customizing database objects. Unfortunately, we didn't have enough time during a single 75-minute session for me to come up with more examples on the fly.

The most interesting questions that came from my session notes fell into the following two broad categories:

  1. How do you create other database objects not directly supported by code-first design, such as indexes and constraints?
  2. How do you work with database schema names other than the database owner (DBO)?

I'll save the second question for a later column, but I'll address the issue of creating new database objects.

Initializing and Seeding a Code-First Database

Before I get into the details of creating other database objects, I need to review code-first's support for initializing and seeding databases. By default, if you re-run a code-first application in which the database already exists and you've made changes to the model, you'll get the exception that says "the model has changed and the database doesn't match anymore." You have three options for resolving this problem:

  • You can use a tool such as SQL Server Management Studio (SSMS) to manually delete the database and re-run the application. Code-first will recreate the database the next time you run the application, just like it created the database the first time you ran the application.
  • You can manually update the database schema so that it corresponds to the model. This can be a little tricky because code-first creates an EdmMetadata table in the database with a hash of the shape of the model used to create it, which code-first uses to detect changes. If you delete this table, Entity Framework will assume you know what you're doing and won’t compare the current model with the current database schema.
  • You can use the code-first development feature to automatically recreate the database whenever the model changes without any manual intervention from you.

The automatic database recreation feature is particularly useful in the early stages of application development when your model is changing with almost every build. It gives you the flexibility to refactor and restructure your application and model code as often as you want. There's a bit of overhead to drop and recreate a database, particularly complicated ones, but with SQL Server Management Studio Express (SSMSE) or CE, it's quite fast.

There are three options for using this feature, which uses a class in the System.Data.Entity namespace for implementation:

  • CreateDatabaseIfNotExists—Creates the database only if it doesn't exist.
  • DropCreateDatabaseAlways—Always drops and recreates the database when you run the application.
  • DropCreateDatabaseIfModelChanges—Drops and recreates the database if code-first detects that the model has changed since the database was created. This uses the hash in the EdmMetadata table.

The easiest way to enable this feature is to add a Database.SetInitializer method call when the application starts. In a web application, the Application_Start event method in Global.asax would be a good place for it, and the Main method in other types of applications would be appropriate. Just put the code wherever it'll run when the application starts.

But you have far more customization options if you create a new class and inherit from one of the previously discussed database drop/create classes. For example, code in the sample application's OrderingModel.cs class module, shown in Figure 1, creates a generic list of Customer objects and initializes the list with two new Customer objects, setting the values of various properties for each. It then uses the ForEach method on the List object to add each customer to the context, which was passed into the Seed method as an argument.

Figure 1: Creating a list of customer objects
public class OrderingInitializer : DropCreateDatabaseIfModelChanges<OrderingContext>
{
            protected override void Seed(OrderingContext context)
            {
                        List<Customer> customers = new List<Customer>
            {
                        new Customer
                        {
                                    FirstName = "Don",
                                    LastName = "Kiely",
                                    Company = "AppDev",
                                    Email = "donkiely@computer.org",
                                    Phone = "800-578-2062",
                                    Created = DateTime.Now
                        },

                        new Customer
                        {
                                    FirstName = "Carol",
                                    LastName = "Kleckner",
                                    Company = "Gambardella's",
                                    Email = "carol@g.com",
                                    Phone = "907-555-1212",
                                    Created = DateTime.Now
                        }
            };

            customers.ForEach(c => context.Customers.Add(c));
}

To use this new class, you need to invoke the SetInitializer method in your application startup code. In the sample ASP.NET MVC application, you do so in Global.asax in the Application_Start event procedure in the following code:

protected void Application_Start()
{
            Database.SetInitializer<Ordering.Models.OrderingContext>(
                        new Ordering.Models.OrderingInitializer());
            …
}

 

It's in the overridden Seed method where you can create additional database objects. The sample application includes the code shown in Figure 2 in the overridden Seed method to create indexes on various fields in the Customer, Address, and Order tables in the code-first database.

Figure 2: Creating indexes in the Seed method
// Create indexes on customer company name, address postal code, and order date

context.Database.ExecuteSqlCommand("CREATE INDEX IX_Customers_Company ON Customers(Company)");

context.Database.ExecuteSqlCommand("CREATE INDEX IX_Addresses_PostalCode ON Addresses(PostalCode)");

context.Database.ExecuteSqlCommand("CREATE INDEX IX_Orders_OrderDate ON SalesOrders(OrderDate)");

Whenever the code-first application recreates the database, it'll seed the database with data and create the three indexes. You can use the same technique to execute any kind of data definition language (DDL) code to fine-tune the database schema just about any way you like.

I modified a very simple sales ordering application that I developed for an Entity Framework course for AppDev to include this index creation code. You can download this sample ordering application by clicking the 141557.zip link at the top of this page. It's an ASP.NET MVC 3 application, so you'll need to have all the MVC 3 tools installed to use the application. When you run the application, it'll create a SQL Server Express database called Ordering.Models.OrderingContext and seed the Customers table with data and create the three indexes.

In other Entity Framework news, I just got my copy of Programming Entity Framework Code First: Creating and Configuring Data Models from Your Classes by Julia Lerman and Rowan Miller. This is an add-on book to Lerman's fabulous second edition of Programming Entity Framework: Building Data Centric Apps with the ADO.NET Entity Framework. The latter book is required reading and reference for anyone using Entity Framework, and the former book looks to be a great resource for the new code-first features.