Introduction
The upcoming release of Visual Studio 2010 will contain the Entity Framework 4 which is Microsoft's second release of the Entity Framework. The new and improved EF4 contains a boat load of new features which many developers have suggested after using version 1. It also comes closer to the features offered in other Object-Relational Mapping tools such as NHibernate. Object-Relational Mapping tools are used to eliminate much of the tedious code needed to have an application persist and retrieve data from a database. The developer uses a visual interface to build classes that map to tables, relationships, stored procedures, and other objects in a database. One of the great new features of EF4 is the ability to create an ADO.NET Entity Data Model and then build the database from the model. Previously the developer needed to create the database first and then generate the model. EF4 still supports reverse engineering a database but being able to use Visual Studio to design a database based off of an object model is a big step forward with this tool.
Today, we are going to discuss a way to create a database using Model-First Development Technique. ASPHostCentral.com, as the premier ASP.NET and Windows Hosting provider, proudly presents this article to anyone and we believe it will help many ASP.NET communities; especially to those who are using ASP.NET 4 Framework. In case you are looking for ASP.NET 4 Hosting, you can always consider ASPHostCentral.com and you can start from our lowest Standard Plan @$4.99/month to host your WCF-service site.
The goal of this article is to show you how to create an ADO.NET Entity Data Model using the Entity Framework 4. This article uses Visual Studio 2010 Beta 2 so some of the steps may change once the final version is released to production. Future articles will build upon this application to dive deeper into the EF4 to demonstrate how to query and display data, incorporate stored procedures, customize the classes generated by the EF4, and much more.
Creating a Database using Model-First Development
Step 1: Create a New Solution
1. Launch Visual Studio 2010 Beta 2
2. Click the New Project link on the Start Page. The New Project dialog box should appear
3. Click on the Visual C# node from the tree view of installed templates
4. Select Empty ASP.NET Web Application from the list of templates
5. Enter OrderSystem for the project name and click the OK button
Visual Studio will create a solution file in the folder you specified and also create a sub folder that contains the web site's project file and config files
Step 2: Create an ADO.NET Entity Data Model
The next step is to create the ADO.NET Entity Data Model. For this application we'll model the concept of users and addresses. A user can have more than one address so we'll build an entity data model that models this relationship
1. Right click on the OrderSystem project in the Solution Explorer and select Add à New Item…
2. Click the Data node under the Visual C# node in the Installed Templates tree
3. Select the ADO.NET Entity Data Model template
4. Change the name to OrderDB.edmx and click the Add button
5. Visual Studio will display the Entity Data Model Wizard. This is where you can decide to build the model from an existing database or create the model first and then build the database
6. Choose Empty model from the wizard and click the Finish button
The OrdersDB.edmx file will be added to your project and the file will be displayed in the Entity Framework Designer
Step 3: Create the Entities and Associations
The next step is to model the user and user's addresses relationship. Let's first create the user entity.
1. Right click on the Entity Data Model Designer and select AddàEntity… from the pop-up menu
2. The Add Entity dialog box should appear. Enter UserAccount for the Entity name. Notice that as you type the Entity name the Entity Set name pluralizes the Entity name. Think of the Entity name as the class that represents a record and the Entity set as the class that represents the table of those records
3. By default the Entity Framework will create a Primary Key called Id. If you wish to not create a primary key you can uncheck the Create key property checkbox from the dialog box. For this example we want a primary key so leave the box checked
4. Click the OK button. The UserAccount entity will be added to the entity diagram
5. The next step is to add properties to this entity. Properties will map to fields in a table. We'll first add the First and Last name properties
6. Right click on the UserAccount entity and select AddàScalar property from the pop-up menu
7. Change the property name to FirstName
8. In the properties window change the MaxLength to 50. Scalar properties are strings by default
9. Add another property called LastName the same way and set its MaxLength to 50
The next step is to add and insert date and update date properties. I like to have the inserted and updated dates on every entity just for the sake of trouble shooting. These are two properties that will be added to every entity so it is the perfect candidate for a Complex Type. A Complex Type allows you to define a set of properties and then associate them with multiple entities.
10. In the Model Browser window, left click on the Complex Types nodes. Sometimes you have to click more than once for the node to be selected. I'm using Beta 2 so hopefully this will be fixed when it goes live. Once the node is selected right click and select Create Complex Type from the pop-up menu
11. Change the name of the complex type to AuditFields
12. Right click on the AuditFields complex type in the Model Browser and select AddàScalar PropertyàDateTime from the pop-up menu
13. Change the name to InsertDate
14. Follow the same steps to add the UpdateDate property to the complex type
15. Now you can add the Complex property to the UserAccount entity. Right click the UserAccount entity and select AddàComplex Property from the pop-up menu
16. Change the name to AuditFields. The type should have already defaulted to AuditFields
When the database is created from this entity it will contain the two audit fields defined in the complex type.Now let's add the address entity. A user can have more than one address so there is a one-to-many relationship between these two entities
17. Right click the designer and select AddàEntity from the pop-up menu
18. Change the name to Address and then click the OK button
19. Add scalar properties to the Address entity for Address1, Address2, City, State, and Zip. All of these properties should be strings with lengths of 50, 50, 50, 2, and 5 respectively
20. Change Address2 to be nullable by settting the Nullable property to True in the properties window. All other properties are required
21. Now add the Audit Fields to this entity by right clicking the Address entity and selecting AddàComplex Property from the pop-up menu. Set the name to AuditFields and the type to AuditFields
The next step is to create an association between the UserAccount and Address entities.
22. Right click on the UserAccount entity and select AddàAssociation from the pop-up menu
23. The Add Association dialog appears. It assumes correctly that you want to create a one-to-many relationship between these two entities. You use the Multiplicity drop down to define the variations of relationships but for this example you can leave the defaults and click the OK button. Notice that a UserAccountId property was added to the Address table
Step 4: Generate the Database
Now that the entities are defined we can create the database. Visual Studio makes this quite simple. A script is generated with the correct DDL statements to create tables, indexes, and relationships which you can then execute against your database
1. Right click on the Designer and select Generate Database from Model… from the pop-up menu. The Generate Database Wizard will appear
2. Click the New Connection… button
3. Enter your Server Name. Choose either User Windows Authentication or Use SQL Server Authentication. Whichever you choose you need a user that has permissions to create a database on the server. For this example I'll use Windows Authentication
4. Enter OrderSystem for the name of the database and click the OK button
5. You should get a message stating "The database 'OrderSystem' does not exist or you do not have permissions to see it. Would you like to attempt to create it?" Click the Yes button
6. The database should be created and you'll be returned to the Generate Database Wizard dialog. Click the Next button
7. The wizard will now generate the DDL statements needed to create this database
8. Click the Finish button
9. A new file will be added to the project called OrderDB.edmx.sql. The file contains the DDL statements to create the database. The text of the file is as follows:
-- --------------------------------------------------
-- Date Created: 01/17/2010 09:39:04
-- Generated from EDMX file: C:\Documents and Settings\VinceVarallo\
-- my documents\visual studio 2010\Projects\OrderSystem\OrderSystem\OrderDB.edmx
-- --------------------------------------------------
SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
GO
USE [OrderSystem]
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]')
GO
-- --------------------------------------------------
-- Dropping existing FK constraints
-- --------------------------------------------------
-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------
-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------
-- Creating table 'UserAccounts'
CREATE TABLE [dbo].[UserAccounts] (
[Id] int NOT NULL,
[FirstName] nvarchar(50) NOT NULL,
[LastName] nvarchar(50) NOT NULL,
[AuditFields_InsertDate] datetime NOT NULL,
[AuditFields_UpdateDate] datetime NOT NULL
);
GO
-- Creating table 'Addresses'
CREATE TABLE [dbo].[Addresses] (
[Id] int NOT NULL,
[Address1] nvarchar(50) NOT NULL,
[Address2] nvarchar(50) NULL,
[City] nvarchar(50) NOT NULL,
[State] nvarchar(2) NOT NULL,
[Zip] nvarchar(5) NOT NULL,
[AuditFields_InsertDate] datetime NOT NULL,
[AuditFields_UpdateDate] datetime NOT NULL,
[UserAccountId] int NOT NULL
);
GO
-- --------------------------------------------------
-- Creating all Primary Key Constraints
-- --------------------------------------------------
-- Creating primary key on [Id] in table 'UserAccounts'
ALTER TABLE [dbo].[UserAccounts] WITH NOCHECK
ADD CONSTRAINT [PK_UserAccounts]
PRIMARY KEY CLUSTERED ([Id] ASC)
ON [PRIMARY]
GO
-- Creating primary key on [Id] in table 'Addresses'
ALTER TABLE [dbo].[Addresses] WITH NOCHECK
ADD CONSTRAINT [PK_Addresses]
PRIMARY KEY CLUSTERED ([Id] ASC)
ON [PRIMARY]
GO
-- --------------------------------------------------
-- Creating all Foreign Key Constraints
-- --------------------------------------------------
-- Creating foreign key on [UserAccountId] in table 'Addresses'
ALTER TABLE [dbo].[Addresses] WITH NOCHECK
ADD CONSTRAINT [FK_UserAccountAddress]
FOREIGN KEY ([UserAccountId])
REFERENCES [dbo].[UserAccounts]
([Id])
ON DELETE NO ACTION ON UPDATE NO ACTION
GO
-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------
It is important to note that the tables weren't added to the database yet. In order to actually create the tables you need to right click in the OrderDB.edmx.sql file and select Execute SQL from the pop-up menu. You'll be prompted to log into the server that contains your database. Once you are logged in the script will execute and the objects will be added to your database
Conclusion
That's all you need to do to create a database using the new Entity Framework's Model First methodology. This is a big improvement over the first edition because it allows you to use Visual Studio to work through the design of you objects first and then VS can figure out how to create the database tables, indexes, and relationships for you.