Entity Framework 4.0 – Learn to Create Databases from the Model

For application developers focusing on the needs of their code, rather than worrying about the complexities of data representation, the Entity Framework's abstractions are essential. But eventually a database needs to be created, and Hima is here to show us how it's done.

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.

  1. Storage Layer: this is defined with Store Schema Definition Language (SSDL). Table structures and their relationships are defined here.
  2. Conceptual Layer: this is defined with Conceptual Schema Definition Language (CSDL). Business entities and their relationships are described in the conceptual schema.
  3. 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.

1325-image001.png

Figure 1: Adding a new item to the class library project.

1325-image004.jpg

Figure 2: Adding Company.edmx to the project.

The moment we click the Add button, the Entity Data Model Wizard appears. This gives us two options to select from:

  1. Generate from database
  2. Empty model

1325-image005.png

Figure 3: Adding an 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.

1325-image007.jpg

Figure 4: The System.Data.Entity reference, added after an .edmx file is created.

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.

1325-image008.png

Figure 5: Entity Framework toolbar for creating entities and relationships between entities.

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.

1325-image010.png

Figure 6: Adding a new entity to the Model.

A dialogue box for adding an Entity appears, as shown below.

1325-image012.png

Figure 7: Creating the Employee entity.

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:

  1. Right click on the entity to add new properties such as EmpName, Description, DOB and Address.

    1325-image013.png

    Figure 8: Creating scalar properties for the entities.

  2. 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.

1325-image015.jpg

Figure 9: Selecting the Employee entity’s properties.

1325-image017.jpg

Figure 10: Editing the Employee entity’s properties.

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

1325-image018.png

Now let us create a department entity with the properties Id, DeptName, DeptDescription, and DeptCode, as shown to the left.

Fig11: The Department entity.

1325-image019.png

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.

Figure 12: The Manager entity.

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…

1325-image021.jpg

Figure 13: Adding an Association relationship to the table.

This opens the following window, with the option to add relationships between these two tables.

1325-image022.png

Figure 14: Adding an association between Employee and Department.

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…

1325-image024.jpg

Figure 15: Adding an inheritance relationship for entities.

In the dialog box, select Employee as the base entity as and Manager as the derived entity.

1325-image025.png

Figure 16: Adding inheritance between the Employee and Manager entities.

1325-image027.jpg

Figure 17: The final model after applying all the relationships.

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.

1325-image028.png

Figure 18: Conceptual Model Properties.

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.

1325-image029.png

Figure 19: Opening the .edmx with the XML 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.

1325-image031.jpg

Figure 20: Generate Database from the Model

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.

1325-image032.png

Figure 21: Connection string credentials.

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.

1325-image033.png

Figure 22: Choosing the data connection for the creation of a database from our model.

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.

1325-summary_settings.png

Figure 23: The generated Company.edmx.sql file

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.

Summary

In this article we have learnt how to create a database from the model in EF 4.0, an approach also called ‘Model First’. We’ve looked at the structure of an .edmx file, and its significance, and learnt how to create entities, properties for the entities, and relationships between the entities. As we’ve seen, Model First Development helps to create a model for the entities, then has Visual Studio 2010 generate the DDL to create a database with matching tables, columns and relationships for the entities.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

  • 74853 views

  • Rate
    [Total: 302    Average: 4/5]
  • pinaldave

    Excellent Read
    Excellent very detailed read!

    Really enjoyed it and the attention to details is excellent.

    Thanks,

  • darkhorse

    Good Post
    The way you explained is good.

  • Sivakumar Vellingiri

    Excellent Article
    Very useful info Bindu and thanks for posting.

  • Vijaya

    Very useful info
    I am glad that I have learnt something that is new to be. I used to think that EF is too difficult. But by reading this I came to know that its so easy. Such an easy explanation , presented in a simple and undderstanable way.
    Thanks to the author and Simple talk. I am happy that I found this link.

  • d312

    Very Useful Article
    Its very well explained.
    Thanks a lot for posting.

  • Anonymous

    Great Resource !
    Easy to understand , I can say that EF 4.0 is big hit. Nice features. Thanks for the explanation

  • Kunal Chowdhury

    Excellent Explanation
    Well explained Hima. Good Job.

  • Sravan Kumar

    Wow EF
    Its a great feature in EF. Now we can create database from model. VS 2020 comes with this option. It is clearly explained step by step.

  • Anonymous

    ravi
    Good work Hima,Very useful to all…….

  • Anonymous

    chinnu
    nice job

  • Surya Pratap

    A Nice Article on EF
    The article is quite nice and covers the basics of setting up and getting to steam on EF.

    That said, EF is very constrained when it comes to databases, hopefully MS will allow EF to come out of its infancy and work with other databases.

    This is one of the major reasons why EF is not an ORM of choice when your deployment database is not known.

  • Surya Pratap

    A Nice Article on EF
    My bad, EF does support multiple databases as long as they have the ADO.Net interface in the drivers.

  • Saikrishna Alladi

    Great Article on Entity Framework
    Very great article on Entity Framework,
    thanks hima

  • Omprakash Kyadas

    A Very Good Article
    The article you have explained covers the basic about creating a EF model.

  • Shravan Kumar Kasagoni

    Good Resource for EF
    good article

  • Bhaskar

    Good
    Good one

  • Amit P

    Good!
    Good post!
    But the very first line of the post start with a confusing stuff “Entity Framework (EF) is object relational mapping software” …. no, EF is not s/w, as u said, its framework

  • pradeepabmalali@gmail.com

    Thanks ,madam
    Really nice one,thanks a lot and we expected u to deliver more .net articles lik this..

  • Priyanka

    Highly technical
    Nice article that covers EF, creating database from the model. Espeically useful for the people who are not aware of the features of EF 4.0

  • Manoj Tripathi

    Good Article
    Hi Hima,
    Very good article. Keep contributing.

  • Suresh

    Excellent Wrie Up
    Hima Great Article! Expecting more writings from you in ASP.NET. This article really helpful for me to do hands on and create any type of database from the model. It would be good if you can also write another article on using stored procedures in Entity Framework. Thanks for your time for writing this.

  • Sukanya

    To choose EF or not
    Still we have doubt to choose it or not.
    Any one can tell me confidently to choose MS EF , which do not lead to any problems?
    Still organizations are afraid to use it.

  • Anonymous

    Dont use EF
    EF framework as a whole SUCKS.It works very well for academic or very very small real life project or for an article posted above.

    Try having a 100+ tables in EF and the performance you see will make you commit suicide and blame yourself that why on hell did I ever use EF…

    Let alone I dont want to talk even caching and concurrency problems the EF creates.

    A matter of advice:-

    Stay away from EF atleast till the time Microsoft comes up with EF 10.0 …..

  • Hima Vejella

    Have you tried EF 4.0 or 4.1
    @Anonymous Have you ever tried EF 4.0 or 4.1?

  • twh

    Maintenance?
    I was wondering if you are planning a follow-up article that describes how to maintain EF. Do you make changes in the model or the database? Can you propogate changes either way?

  • Hima Vejella

    Sure
    @twh Sure. I am getting so many email asking questions that is provokes me to write follow up article(1) on this.

  • Steve G.

    Blaming EF
    @Anonymous – regarding EF performance; you can’t blame EF for performance when your programmer creates a mess of a database because he/she’s not a DBA!

    I’m using EF with a database of several hundred tables with no problems.

  • Anonymous

    EF SUCKS
    @Steve G.

    Yup thats the reason I always believe that leave the DB things for the DB guys and NOT to the application developers who know NOTHING on how databases works.

    I would like to know how many Entity models are you using ?

  • Anonymous

    Basic Article – Followup
    Need to discus a few things to get in deeper with you, maybe on twitter ?

  • srikanna

    ya it is good article Please read all..
    ya it is very nice article

  • sqlfanatic

    Very good
    So useful. I have learned a lot now from this tutorial

  • Raghav

    Good post
    very much useful,It will be more helpul for us if you give more guidance on unicorn edition for codefirst.

  • Rich P123

    Great article on creating entity database
    I really liked this article. Great job Hima! Now I know how to create an entity database in VS2010 !!!

  • BenLi

    Why MS could not make a good instruction like this ?
    Truly appreciate this free EF tutorial. Hope MS could hire her for making its documents. After wasting hours of effort trying to understand things from MS provided instructions, I have long given up and now only look for 3rd party contribution in explaining MS applications. Thank you Hima for the generous contribution to the developers’ world.

  • shoeb

    Great article on creating entity database
    Great article

  • Dorababu

    Good one
    Hi Bindu I created as per you said but I am getting an error as an item with the same key has already been added can you tell. These are the relation I have given for Employee Department I have given 1 to many, for Employee Manager many to many along with Inheritance