Entity Framework 4.0 - Learn to Create Databases from the Model
The ADO.NET Entity Framework (EF) is object relational mapping software for ADO.NET, used to develop applications that interact with data. It provides a mapping between the relational database schemas and objects. This technology is helpful for architecting, designing and developing at a conceptual level without worrying about details. ADO.NET Entity Framework allows developers to program against the Entity Relationship (Object) model, as opposed to querying against the relational database, mainly concentrating on the data. The main benefit is to make data oriented applications maintenance friendly.
EF - Benefits
- The application won’t be tied to hard-coded dependencies on the storage schema or database.
- The conceptual model and the storage-specific schema mappings can change without changing the application code.
- Developers can work more easily with an object model that can be mapped to various storage schemas, and can be implemented in different databases.
- The ability to map multiple conceptual models to a single storage schema.
Model First Development was introduced in VS 2010, along with .NET Framework 4.0, and the essential idea behind the Entity Framework is to query the model, rather than the database. In this article I will walk through how to generate databases from the model.
Understanding the model
EF maps to the database tables using a model. This model is responsible for mapping the application’s entities and their relationships to the data held in the physical database. It’s a combination of three layers, and is stored as an Entity Data Model (.edmx) file. .edmxis an XML based file that consists of these 3 layers.
- Storage Layer: this is defined with Store Schema Definition Language (SSDL). Table structures and their relationships are defined here.
- Conceptual Layer: this is defined with Conceptual Schema Definition Language (CSDL). Business entities and their relationships are described in the conceptual schema.
- Mapping Layer. This is defined with Mapping Specification Language (MSL). It maps the conceptual schema to the storage schema. The mapping schema describes how the business entities map to the database schema, and how database tables map to the entities in the model.
If you right click on an .edmx file and open it with XML Text Editor, you can view the EDMX schema layers.
At runtime, the .edmx is split into three different files: .ssdl, .csdl and .msl.
To construct any database, we need define the tables for the data storage. Here, tables are mapped to entities, hence the need to have entities and their relationships defined in the model. The model can be created using an .edmx file in Entity Framework and each entity in a specific Entity Framework is mapped as a table inside the database, and each property in an entity is mapped to a corresponding column in the specified table.
Creating the model
Let us start by creating a Class Library Application in Visual Studio 2010, so that we can re-use the models. We select the Add ->new Item , then the Data tab in the Visual Studio 2010 Installed Templates list, and finally select ADO.NET Entity Data Model. We’ll name our .edmx ‘Company’, and click the Add button, in the lower right corner, as shown below.
The moment we click the Add button, the Entity Data Model Wizard appears. This gives us two options to select from:
- Generate from database
- Empty model
We aren’t going to worry about the first option as we are interested in creating a database from the model only. We’ll select the second option, Empty model, and click the Finish button. As you can see from the screenshot above, this option creates an empty model as a starting point for visually designing a conceptual model from the toolbox. When the project is compiled, classes will be generated from the model. We’ll specify a database connection to map the conceptual model to the storage model later.
If you observe the Solution Explorer, it creates a new reference related to Entity Framework, System.Data.Entity, responsible for working with the Entity Framework related APIs. This reference is added only after adding an .edmx file.
Creating entities in the .edmx
Now we need to define the model in this .edmx file by creating entities, which can be done by dragging items from the EF toolbox or from the model itself.
In a Company, we’ll have Employees, Departments and Managers as entities. Let’s create the Employee entity.
We right click on the .edmx file and Click Add → Entity as shown below.
A dialogue box for adding an Entity appears, as shown below.
We’ll name the entity ‘Employee’. The Entity Set is automatically pluralized by the IDE, based on the Entity Name.
It also creates a Key property - ID - of type Integer. This Key is called the Entity Key in the model, and mapped as the Primary Key in the database. If you do not want to create the Entity Key property for a particular entity you can uncheck this property. Next, we’ll press OK.
Now we have created an entity, we can start adding some more properties for it. There are two ways of doing this:
- Right click on the entity to add new properties such as EmpName, Description, DOB and Address.
- 2. Select the Id property and hit enter to add some more scalar properties (scalar properties map to a single field in the storage layer).
Editing entity properties
To edit the attributes for entity properties, you select the property and right click it, then navigate to properties. The properties that you set are reflected in the database that is going to be created from the model.
Note that here the identity property for the primary key column is set automatically by the IDE. The Nullable attribute allows or disallows any null values inside the column.
Creating more entities for the model
Now let us create a department entity with the properties Id, DeptName, DeptDescription, and DeptCode, as shown to the left.
Let us also create a Manager entity with ManagerId, EmployeeID as properties. The key for this entity is the combination of ManagerId and EmployeeID. One employee can report to multiple managers.
Applying relationships to the entities
An employee cannot be member of multiple departments. A department, however, should accommodate multiple employees, so the relationship between employee and department is one to many. Let us add a new association between these two tables. For this, we right click on the model, and click Add → Association…
This opens the following window, with the option to add relationships between these two tables.
We need to make provision for the department entity to have many instances of the Employee entity, so we map the relationship between the department and the Employee entities as one to many, as shown above. Clicking the check box “Add foreign key properties to the ‘Employee’ entity” helps to create the foreign key relationships between these two tables automatically.
If we observe the Employee entity, we notice that departmentid is being added to the employees table for the foreign key relationship in the database.
A manager can have more than one employee reporting to him, and an employee can report to multiple managers, so the relationship between the Employee entity and the manager entity is many to many. We need to add a many to many association between these two entities. Because a manager is also an employee of the company, the manager class gets inherited from the Employee class. To add this inheritance relationship, right click on the model and select Add →Inheritance…
In the dialog box, select Employee as the base entity as and Manager as the derived entity.
We’ve now finished adding relationships between our entities, and are ready to generate our database.
Changing DatabaseSchema Name
Entity Framework Model uses dbo as the default schema name. In order to change that, right click on the .edmx in the VS2010 IDE, and click on Properties. The ConceptualEntityModel properties window appears as shown below. Make sure that you change the name according to the project, so that the script files are generated accordingly.
If we look at Company.edmx in the automatic editor selector, we can view the XML schema code that’s been generated from the design. SSDL, CSDL, and MSL are created by the IDE, based on the entities and their relationships. Each time we make changes to the model, this schema gets recreated. To view this, we need to right click on the .edmx file and open with the XML (Text) Editor.
Here we can examine how the Entities and their relations are mapped in the conceptual model, and how the storage schema and conceptual schema are actually mapped in the mapping layer.
We can also examine the Company.designer.cs file, a partial class inherited from ObjectContext.
All the entities that are created from the IDE inherit from Entityobject. Their code is generated automatically with properties and datacontract attributes. Whenever we modify and save the .edmx file, the classes or the automatic code are regenerated by the IDE.
Generating the database from the model
Now we need to right click and select Generate Database from the Model, from the context menu.
We need to have an empty or existing database created in SQL Server to generate a database from the model. I have created an empty database, called Company, in SQL Server 2008. If you do not have existing connections, then click on new Connection button and specify your credentials, as shown below.
Now, choose the appropriate data connection from the dropdown list, as shown below. Here we also have the option to save the connection string in app.config, which contains connection string metadata and various settings for the project. The syntax is somewhat different from the normal connection string.
Pressing Next generates a file called Company.edmx.sql. Note that the constraints, foreign key relationships, and primary keys in the tables are created based on the properties specified in the model.
Finally, click on the Finish button. The IDE creates this .sql script file for us:
This script can be given to the DBA to run, creating the database. Or, we can right click on the script file and press Execute SQL or Control + Shift +E, to run a script against SQL Server 2008 database from the IDE.
The SQL script is generated from the SSDL contained in the .edmx file. This script contains lots of DDL statements to create the database tables, which correspond to the tables described in the SSDL. Entities are mapped as tables in the database and scalar properties are mapped as columns in the corresponding tables.
EXCELLENT Article by