Tutorial: Creating a Conceptual Model from an Existing Database using Tools (Entity Framework 4.1)

  • Article

This walkthrough shows how to generate a conceptual model from an existing database. This walkthrough consists of the following basic tasks:

  • Creating the School sample database, which contains the schema and data. This database is used to demonstrate the “database first” scenario.

  • Using the Entity Data Model tools to create the conceptual model based on the School database. The conceptual model is created as part of a Visual Studio 2010 Class Library project.

  • Using the ADO.NET DbContext Generator template to generate object-layer code from the conceptual model, so that that the entities and relationships defined in the conceptual model can be consumed by .Net programming languages. The object-layer code contains definitions of entity classes and typed object context that manages the entity objects during runtime.

This walkthrough also illustrates the following application features:

  • Shows how to implement table-per-type inheritance by modifying the conceptual model using ADO.NET Entity Data Model Designer. Table-per-type inheritance uses a separate table in the database to maintain data for non-inherited properties and key properties for each type in the inheritance hierarchy.

  • Creates a client Windows Presentation Foundation (WPF) application that uses the model built into the class library assembly.

To create the School sample database

  1. Use Management Studio or Management Studio Express to execute a Transact-SQL script shown in the School Database Shema topic.

To create the class library project that contains the conceptual model

In this section you will use the Entity Data Model Tools to create the conceptual model as part of a Visual Studio 2010 Class Library project. The tools generate an XML-based file with an .edmx extension. The .edmx file contains the following sections: conceptual schema definition language (CSDL) which describes the conceptual model; store schema definition language (SSDL) which describes storage model; and mapping specification language (MSL) which describes the mapping between the conceptual and storage models. At build time the tools use the information in the .edmx file to create the .csdl, .ssdl, and .msl metadata files that are needed by the Entity Framework at runtime. You can specify for the metadata files to be either copied to the output directory or embedded into the assembly. Because we are creating a class library project that will be used in other project, the files should be embedded, which is the default option for the .edmx file.

  1. Create a new class library project. Type SchoolModelLib for the project name and DatabaseFirstSchoolModel for the solution name.

  2. Remove the default source code file that was added to the project (Class1.cs or .vb).

  3. Add a new ADO.NET Entity Data Model item to the project, by doing the following:

    1. Right-click on the project and select Add, and then New Item.

    2. Select Data in the Installed Templates pane, and then select ADO.NET Entity Data Model template.

    3. Type SchoolModel.edmx for the model name.

    The Entity Data Model Wizard appears.

  4. In the Choose Model Contents dialog box, select Generate from database. Then click Next.

  5. Click the New Connection button. In the Connection Properties dialog box, enter your server name, select the authentication method, type School for the database name, and then click OK. The Choose Your Data Connection dialog box is updated with your database connection settings. Ensure that Save entity connection settings in App.Config is checked. Then click Next.

  6. Select Department, Course, OnlineCourse, and OnsiteCourse tables from the Choose Your Database Objects dialog box

  7. Ensure that Pluralize or singularize generated object names and Include foreign key columns in the model are selected.

    The wizard performs the following actions:

    • Adds references to the System.Data.Entity, System.Runtime.Serialization, and System.Security assemblies.

    • Generates the SchoolMode.edmx file that defines the conceptual model, the storage model, and the mapping between them. Sets the Metadata Artifact Processing property of the .edmx file to Embed in Output Assembly so the generated metadata files get embedded into the assembly.

    • Creates an App.Config file that contains the connection string. Connection strings used by the Entity Framework contain information that is used to connect to the underlying ADO.NET data provider that supports the Entity Framework. They also contain information about the metadata files.

    • Creates an object-layer code file and adds it under the .edmx file. In the next step, you will you use the ADO.NET DbContext Generator template to replace the object-layer code that was generated by default.

  8. Compile the project.

To generate object-layer code from the conceptual model

In order for the types and relationships defined in the conceptual model to be consumed by programming languages, they must be exposed as typed common language runtime (CLR) objects. The object-layer code contains definitions of entity classes and typed object context that manages the entity objects during runtime. An instance of the object context class encapsulates the following: a connection to the database; metadata that describes the model; and change tracking information of the entity objects. An object context is represented by an instance of the DbContext or ObjectContext class. By default, the Entity Framework tools generate the object-layer code file that contains definitions for persistence aware entity types and the ObjectContext derived type. Starting with the Entity Framework 4.1 you can use a lightweight DbContext type to perform many common tasks. DbContext wraps ObjectContext and hides many complexities of ObjectContext. The ADO.NET DbContext Generator generates persistence ignorant entity types, also known as "plain-old" CLR objects (POCO) types, and DbContext derived type. This section uses the ADO.NET DbContext Generator template to generate an object-layer code based on the conceptual model.

  1. Right-click an empty area on the Entity Designer surface, point to Add Code Generation Item. Select Code in the Installed Templates pane, and then select ADO.NET DbContext Generator. Change the default template name to SchoolModel, and then click OK.

    The wizard performs the following actions:

    1. Adds a reference to the EntityFramework assembly (installed with the Entity Framework 4.1).

    2. Disables the default code generation for the .edmx file and sets the CodeGenerationStrategy property to None.

    3. Creates the SchoolModel.Context.tt and SchoolModel.tt project folders. Under the SchoolModel.Context.tt folder, there is a file that defines typed DbContext. Under the SchoolModel.tt folder, there are files that define entity types.

    If you are working with the Visual Basic project, you might need to click Show All Files in Solution Explorer to see all files in the project.

To implement table-per-type inheritance

In this section you will change the mapping between the OnlineCourse, OnsiteCourse and Course entity types that are mapped to tables of the same names. Because the OnlineCourse and OnsiteCourse entity types contain information that is unique to the two course types and they share a common key, they can be changed to inherit from the Course entity type. As a result of this mapping, the OnlineCourse and OnsiteCourse classes will be deriving from the Course class.

  1. Open the DatabaseFirstSchoolModel solution in Visual Studio.

  2. Select SchoolModelLib project and in Solution Explorer, double-click the SchoolModel.edmx file.

    In the SchoolModel conceptual model, the Course, OnlineCourse, and OnsiteCourse entity types map to tables of the same names. Because the OnlineCourse and OnsiteCourse entity types contain information that is unique to the two course types and they share a common key, they can be changed to inherit from the Course entity type. The rest of the steps in this section demonstrate how to implement table-per-type inheritance.

  3. Right-click the OnlineCourse entity type and select Properties.

  4. In the Properties window, set the Base Type property to Course.

  5. Repeat steps 3 and 4 for the OnsiteCourse entity type.

  6. Right-click the association (the line) between the OnlineCourse and Course entity types. Select Delete.

  7. Right-click the association between the OnsiteCourse and Course entity types. Select Delete.

  8. Right-click the CourseID property of the OnlineCourse entity type, and then select Delete.

  9. Right-click the CourseID property of the OnsiteCourse entity type, and then select Delete.

  10. Select the Course entity type. Set its Abstract property to true in the Properties window.

    A message box appears to tell you that defining an entity type as abstract will remove all existing function mappings for that type. Click OK.

  11. Right-click the OnlineCourse entity type and select Table Mapping.

    The Mapping Details window appears.

  12. Click the Value/Property field that corresponds to the CourseID column.

    The Value/Property field becomes a drop-down list of properties that are available to map to the corresponding column.

  13. Select CourseID from the drop-down list.

  14. Repeat steps 11 through 13 for the OnsiteCourse entity type.

Table-per-type inheritance is now implemented. The OnlineCourse and OnsiteCourse classes now derive from the Course class.

To test the model with a WPF client application

In this section you will create a WPF client application that queries the conceptual model, updates entity objects, and saves the data to the database.

  1. Add a new WPF project to the DatabaseFirstSchoolModel solution. Type SchoolModelWPFTest for the project name.

  2. Add a reference to the EntityFramework dll.

  3. Add a reference to the SchoolModelLib class library project.

  4. Add a connection string to the project. Connection strings used by the Entity Framework contain information that is used to connect to the underlying ADO.NET data provider that supports the Entity Framework. They also contain information about the required model and mapping files. You can specify the connection string by doing one of the following:

    • Specify the connection string in the application's configuration file. When the connection string is added to the App.config file, DbContext retrieves this connection information automatically when creating object queries. You can either copy the App.config file from the SchoolModelLib class library project and paste it into this project. Or, add a new App.config file to this project. To do this, right-click on the project and select Add, then New Item, then select Application Configuration File template. Open the App.config file that was added by the tools to the SchoolModelLib project; copy the connectionStrings element and paste in the App.config that you just added.

    • Alternatively, you can set the connection string programmatically by passing it to the instance of the DbContext type. For more information, see Managing Connections (Entity Framework 4.1).

  5. You can now delete the App.config file for the SchoolModelLib project because it is never used.

  6. Open the MainWindow.xaml, that was added to the project by default, and replace the default XAML code with the XAML code show in the Sample XAML File: WPF Databinding and Entity Framework 4.1 topic. The main components defined in XAML are as follows:

    • The ComboBox control named comboBoxDepartment. This control is bound to the collection of Departments in code behind.

    • Two ViewList controls, that are named listViewOnline and listViewOnsite. The controls are bound to the collections of OnlineCourses and OnsiteCourses related to the currently selected department in code behind. The TextBox controls are embedded in cells of ListView controls, so that you can make changes to objects.

    • The Add buttons (named buttonAddOnline and buttonAddOnsite) and Delete buttons (named buttonDeleteOnline and buttonDeleteOnsite) that enable you to add or deleted OnsiteCourse or OnlineCourse objects.

    • The buttonSave lets you save the changes to the database.

    • The buttonClose closes the application.

  7. Open the MainWindow.xaml.cs (or .vb) file, and then replace the default code behind with the code shown in the topic Sample Code-Behind File: WPF Databinding and Entity Framework 4.1 (see the code comments for more explanation).