Introduction to Entity Framework: Part II - Designing the Database
This is part two of my tutorial series on Entity Framework. If you have not read part one and do not have a good understanding of what an Object Relational Mapper (ORM) is, then please read part one first.
Table of Contents
Database First vs Model First
There are two ways to design a model in Entity Framework (EF). Before we can build our model you must first understand the difference between the two.
The first way to design your model in EF is to first design the database. If you've ever had any experience setting up a SQL database before then nothing really changes in this step. Simply design your database as you normally would, keeping a few concepts in mind as you do it (we'll go over these concepts shortly).
The second way to design your model in EF is to do the designing in EF itself and then instruct EF to build the database for you. This is an interesting route to take. It has the benefit of allowing the developer to again remain SQL ignorant (whether that's a good thing or not is debatable) as well as letting you use the EF design surface to map out your entities the way you like them. My issue with this approach is two-fold. Firstly the paradigm of model first seems to me like starting from the middle. When I'm designing my model in EF with no knowledge of the underlying database structure it becomes this weird disconnect; it's like framing a house and then instructing the frame to go ahead and pour the foundation for you.
The paradigm alone is not a huge knock against it as that would just be a matter of choice, however there is one technical issue I still have with the model first approach. When doing the model first EF will actually run a SQL script to setup your database. The issue with this is that if you create an entity in the designer and EF generates a corresponding table in your database, then your application stores some data in that table, and then you go to modify your entity, EF will re-generate your tables for that entity, wiping all associated data previously stored for that entity.
In my opinion, model first is still too buggy to be a very popular choice. There is one option that allows EF to update the database schema without wiping data, known as the Entity Designer Database Generation Power Pack (quite a mouthful). I toyed with this extension a little bit and while it's heading in the right direction I just don't think it's quite there yet. It's also not very easy to figure out and setup. Personally I'd wait for Microsoft to make EF support these abilities natively before I'd consider model first to be a viable approach.
For the purposes of this tutorial I'm going to instruct you how to do database first modeling with Entity Framework. It is currently the easiest approach to take and also the easiest to understand. If you've never dealt with a database before I am going to show you how to setup a very basic SQL Server Express database for your application using only Visual Studio. For larger applications you would probably want to take advantage of a full-blown SQL Server instance and use the SQL Server Management Studio to design and make changes to your database, but for learning purposes SQL Server Express will do just fine.
Designing the Database
Before we can do anything with EF you will want to first design your underlying database. Follow the steps below to create an instance of a SQL Server Express Database and add some tables to it.
First open up Visual Studio and create a new console application. You can add Entity Framework models to any .NET application but for this tutorial I'm going to show you everything in a simple console application so as not to confuse you with out of scope knowledge about different application types.
Right-click on your project node in solution explorer and add a new item. Navigate until you find "Service-based Database". Let's name it "SampleDatabase.mdf and click "Add". Another screen will pop up asking you how you would like to model your database. We could go ahead and use this dialog to create our Entity Framework model file but for now just click "cancel", we'll create that manually when we are done setting up our database. You should now have a SampleDatabase.mdf file in your solution explorer.
Double click your new database file and you should see the server explorer window appear and your sample database selected. Expand the node next to it and right-click "Tables". Select "Add New Table". You should now see a mostly blank screen with a small table containing three columns.`
We are going to create a database for a fictitious blog application. The application will store users, blog posts, and post comments. Let's first create the table for our users. Modify your table to look like this:
- After putting in the data for the columns you need to set the "Id" column as the primary key. Right-click the space to the left of the column name and click "Set Primary Key". Next left click the space next to column name so as to select the whole column entry. Then down below in the "Column Properties" window navigate to the "Identity Specification" node and expand it. Set the "(Is Identity)" option to "Yes".
- Save the table and name it "Users".
Follow the same procedure and add two more tables. Be sure to add the primary keys and set identities to yes.
- Save the table and name it "BlogPosts".
- Save the table and name it "PostComments".
The last thing we are going to do is add relationships between your tables. These relationships define how the data stored in the database is associated with one another. Open up your BlogPosts table, right-click an empty area of the design surface and go to "Relationships", then follow these instructions:
- Click the "Add" button to generate a new relationship entry in the window.
- In the properties on the right change name to be "FK_UserHasManyBlogPosts".
- Expand the "Tables And Columns Specification" node and click the button to the right of it to open up the relationship editor.
- In the "Primary key table" dropdown select "Users".
- Then in the row below that select the "Id" column.
- Then on the right select "UserId" column.
You just designed a relationship that tells the database that a user could have many blog posts. Next open up your PostComments table and open the relationships window. Follow the same steps to create two more relationships.
FK_BlogPostHasManyPostComments
- Primary key table: BlogPosts
- Primary key column: Id
- Foreign key column: BlogPostId
FK_UserHasManyPostComments
- Primary key table: Users
- Primary key column: Id
- Foreign key column: UserId
The hard part is over. Designing the underlying database can sometimes be tedious, but being intimately familiar with how your data is organized and stored is very valuable. While this has the potential to be a semi-confusing process you will see how much it greatly simplifies building the model in part three.