Entity Framework’s Code-First Design: Going Beyond the Basic Database Objects

I 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)?

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 shown in Figure 1 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.

Figure 1: Exception Output