The Entity Data Model Wizard

Open the Add New Item dialog for your project, navigate to the Data category, and select ADO.NET Entity Data Model as the item template (as shown in Figure 27-1). Call it AdventureWorks2014Model.edmx.

This starts the Entity Data Model Wizard that can help you start building an Entity Framework model.

As the first step, the dialog in Figure 27-2 enables you to select whether you want to automatically create a model from a database, start with an empty model, start with an empty model for code-first design, or create a code-first model using an existing database.

The empty model option is useful when you want to create your model from scratch, and either mapping it manually to a given database or letting the Entity Framework create a database based on your model.

However, you can create an entity model from the AdventureWorks2014 database, so for the purpose of this example use the EF Designer from Database option, and get the wizard to help you create the entity model from the database.

Moving to the next step, you now need to create a connection to the database (as shown in Figure 27-3). You can find the most recent database connection you’ve created in the drop-down list, but if it’s not there (for example, if this is the first time you’ve created a connection to this database) you need to create a new connection. To do so, click the New Connection button, and go through the standard procedure to select the SQL Server instance, authentication credentials, and finally, the database.

If you use a username and password as your authentication details, you can choose not to include those in the connection string (containing the details required to connect to the database) when it is saved because this string is saved in plain text that would enable anyone who sees it to have access to the database. In this case you would have to provide these credentials to the model before querying it for it to create a connection to the database. If you don’t select the check box to save the connection settings in the App.config file, you also need to pass the model the details on how to connect to the database before you can query it.

Next, the wizard asks you which version of Entity Framework to use. The choices presented include, by default, version 6.0 and 5.0. The reason to include version 5.0 (and to allow you to have older versions as well) is to support existing applications that have not yet upgraded to version 6.0. Keep the default of version 6.0 and click Next.

In the next step, the wizard connects to the database and retrieves its structure (that is, its tables, views, and stored procedures), which displays in a tree for you to select the elements to be included in your model (see Figure 27-4).

Other options that can be specified on this screen include:

• Pluralize or singularize generated object names: This option (when selected) intelligently takes the name of the table/view/stored procedure and pluralizes or singularizes the name based on how that name is used in the model. (Collections use the plural form, entities use the singular form, and so on.)
• Include foreign key columns in the model: The Entity Framework supports two mechanisms for indicating foreign key columns. One is to create a relationship and hide the column from the entity, instead representing it through a relationship property. The other is to explicitly define the foreign key in the entity. If you wish to use the explicit definition, select this option to include it in your entities.
• Import selected stored procedures and functions into the entity model: While the entity data store supports the inclusion of stored procedures and functions, they need to be imported as functions in order to be accessible through the model. If you select this option, the stored procedures and functions that you choose in this dialog will automatically be imported into the model.
• Model Namespace: This enables you to specify the namespace in which all the classes related to the model will be created. By default, the model exists in its own namespace (which defaults to the name of the model entered in the Add New Item dialog) rather than the default namespace of the project to avoid conflict with existing classes with the same names in the project.

Select all the tables in the database to be included in the model. Clicking the Finish button in this screen creates an Entity Framework model that maps to the database. You might get prompted with a security warning about running a text template. The reason is that the generation of the classes used by Entity Framework is accomplished through a T4 Template. Visual Studio will prompt you for confirmation before running a template unless you have previously disabled that warning. See Chapter 43, “Code Generation with T4,” in the online archive for more information about T4 Templates.

Once generated, you can view the model in the Entity Framework designer, adjust it per your requirements, and tidy it up as per your tastes (or standards) to make it ideal for querying in your code.

The Entity Framework Designer

After the Entity Framework model has been generated, it opens in the Entity Framework designer, as shown in Figure 27-5.

The designer has automatically laid out the entities that were created by the wizard, showing the associations it has created between them.

You can move entities around on the designer surface, and the designer automatically moves the association lines and tries to keep them neatly laid out. Entities automatically snap to a grid, which you can view by right-clicking the designer surface and selecting Grid ➪ Show Grid from the context menu. You can disable the snapping by right-clicking the designer surface and unchecking Grid ➪ Snap to Grid from the context menu to have finer control over the diagram layout, but entities line up better (and hence make the diagram neater) by leaving the snapping on.

As you move entities around (or add additional entities to) the diagram, you may find it gets a little messy, with association lines going in all directions to avoid getting “tangled.” To get the designer to automatically lay out the entities neatly again according to its own algorithms, you can right-click the designer surface and select Diagram ➪ Layout Diagram from the context menu.

Entity Framework models can quickly become large and difficult to navigate in the Entity Framework designer. Luckily, the designer has a few tools to make navigating it a little easier. The designer enables you to zoom in and out using the zoom buttons in its bottom-right corner (below the vertical scrollbar — see Figure 27-6). The button sandwiched between these zoom in/out buttons zooms to 100% when clicked.

To zoom to a predefined percentage, right-click the designer surface, and select one of the options in the Zoom menu. In this menu you can also find a Zoom to Fit option (to fit the entire entity model within the visible portion of the designer), and a Custom option that pops up a dialog enabling you to type a specific zoom level.

In addition, selecting an entity in the Properties tool window (from the drop-down object selector) automatically selects that entity in the designer and brings it into view; right-clicking the entity in the Model Browser tool window (described shortly) and selecting the Show in Designer menu item does the same. These make it easy to navigate to a particular entity in the designer, so you can make any modifications as required.

You can minimize the space taken by entities by clicking the icon in the top-right corner of the entity. Alternatively, you can roll up the Properties/Navigation Properties groupings by clicking the +/– icons to their left. Figure 27-7 shows an entity in its normal expanded state, with the Properties/Navigation Properties groupings rolled up, and completely rolled up.

You can expand all the collapsed entities at one time by right-clicking the designer surface and selecting Diagram ➪ Expand All from the context menu. Alternatively, you can collapse all the entities in the diagram by right-clicking the designer surface and selecting Diagram ➪ Collapse All from the context menu.

A visual representation of an entity model (as provided by the Entity Framework designer) can serve a useful purpose in the design documentation for your application. The designer provides a means to save the model layout to an image file to help in this respect. Right-click anywhere on the designer surface, and select Diagram ➪ Export as Image from the context menu. This pops up the Save As dialog for you to select where to save the image. It defaults to saving as a bitmap (.bmp); if you open the Save As Type drop-down list, you can see that it can also save to JPEG, GIF, PNG, and TIFF. PNG is probably the best choice for quality and file size.

It can often be useful (especially when saving a diagram for documentation) to display the property types against each property for an entity in the designer. You can turn this on by right-clicking the designer surface and selecting Scalar Property Format ➪ Display Name and Type from the context menu. You can return to displaying just the property name by selecting the Scalar Property Format ➪ Display Name item from the right-click context menu.

As with most designers in Visual Studio, the Toolbox and Properties tool windows are integral parts of working with the designer. The Toolbox (as shown in Figure 27-8) contains three controls: Entity, Association, and Inheritance. How to use these controls with the designer is covered shortly. The Properties tool window displays the properties of the selected items in the designer (an entity, association, or inheritance), enabling you to modify their values as required.

In addition to the Toolbox and Properties tool windows, the Entity Framework designer also incorporates two other tool windows specific to it — the Model Browser tool window and the Mapping Details tool window — for working with the data.

The Model Browser tool window (as shown in Figure 27-9) enables you to browse the hierarchy of both the conceptual entity model of the database and its storage model. Clicking an element in the Store model hierarchy shows its properties in the Properties tool window; however, these can’t be modified (because this is an entity modeling tool, not a database modeling tool). The only changes you can make to the Store model is to delete tables, views, and stored procedures (which won’t modify the underlying database). Clicking elements in the Conceptual model hierarchy also shows their properties in the Properties tool window (which can be modified), and its mappings display in the Mapping Details tool window. Right-clicking an entity in the hierarchy and selecting the Show in Designer menu item from the context menu brings the selected entity/association into view in the designer.

The second picture in Figure 27-9 demonstrates the searching functionality available in the Model Browser tool window. Because your entity model can get quite large, it can be difficult to find exactly what you are after. Therefore, a good search function is important. Type your search term in the search textbox at the top of the window, and press Enter. In this example the search term was SalesOrder, which highlighted all the names in the hierarchy (including entities, associations, properties, and so on) that contained the search term. The vertical scrollbar has the places in the hierarchy (which has been expanded) highlighted where the search terms have been found, making it easy to see where the results were found throughout the hierarchy. The number of results is shown just below the search textbox, next to which are an up arrow and a down arrow to enable you to navigate through the results. When you finish searching, you can click the cross icon next to these to return the window to normal.

The Mapping Details tool window (as shown in Figure 27-10) enables you to modify the mapping between the conceptual model and the storage model for an entity. Selecting an entity in the designer, the Model Browser tool window, or the Properties tool window shows the mappings in this tool window between the properties of the entity to columns in the database. You have two ways to map the properties of an entity to the database: either via tables and views, or via functions (that is, stored procedures). On the left side of the tool window are two icons, enabling you to swap the view between mapping to tables and views, to mapping to functions. However, focus here just on the features of mapping entity properties to tables and views.

The table/view mapping has a hierarchy (under the Column column) showing the tables mapped to the entity, with its columns underneath it. To these columns you can map properties on your entity (under the Value/Property column) by clicking in the cell, opening the drop-down list that appears, and selecting a property from the list.

A single entity may map to more than one database table/view (bringing two or more tables/views into a single entity). To add another table/view to the hierarchy to map to your entity, click in the bottom row where it says <Add a Table or View> and select a table/view from the drop-down list. When you add a table to the Mapping Details tool window for mapping to an entity, it automatically matches columns with the same name to properties on the entities and creates a mapping between them. Delete a table from the hierarchy by selecting its row and pressing the Delete key.

Conditions are a powerful feature of the Entity Framework that enable you to selectively choose which table you want to map an entity to at run time based on one or more conditions that you specify. For example, say you have a single entity in your model called Product that maps to a table called Products in the database. However, you have additional extended properties on your entity that map to one of two tables based on the value of the ProductType property on the entity — if the product is of a particular type, it maps the columns to one table, if it’s another type, it maps the columns to the other table. You can do this by adding a condition to the table mapping. In the Mapping Details window, click in the row directly below a table to selectively map where it says <Add a Condition>. Open the drop-down list that appears, which contains all the properties on the entity. Select the property to base your condition on (in the given example it would be the ProductType property), select an operator, and enter a value to compare the property to. Note that there are only two operators: Equals (=) and Is. You can add additional conditions as necessary to determine if the table should be used as the source of the data for the given properties.

Creating/Modifying Entities

The Entity Data Model Wizard gave you a good starting point by building an entity model for you. In some cases this may be good enough, and you can start writing the code to query it, but you can now take the opportunity to go through the created model and modify its design as per your requirements.

Because the Entity Framework provides you with a conceptual model to design and work with, you are no longer limited to having a 1:1 relationship between the database schema and an object model in code, so the changes you make in the entity model won’t affect the database in any way. So you may want to delete properties from entities, change their names, and so on, and it will have no effect on the database. In addition, because any changes you make are in the conceptual model, updating the model from the database will not affect the conceptual model (only the storage model), so your changes won’t be lost.

Creating an Entity

So far you’ve been modifying existing entities as they were created by the Entity Data Model Wizard. However, now take a look at the process to create an entity from scratch and then mapping it to a table/view/stored procedure in your storage model. Most of these aspects have already been covered, but walk through the required steps to get an entity configured from scratch.

You have two ways to manually create entities. The first is to right-click the designer surface and select Add New ➪ Entity from the context menu. That pops up the dialog shown in Figure 27-11, which helps you set up the initial configuration of the entity. When you enter a name for the entity in the Entity Name field, you’ll notice that the Entity Set field automatically updates to the plural form of the entity name (although you can change this entity set name to something else if required). The Base Type drop-down list enables you to select an existing entity in your entity model that this entity inherits from (discussed shortly). There is also a section enabling you to specify the name and type of a property to automatically create on the entity and set as an entity key.

The other way to create an entity is to drag and drop the Entity component from the Toolbox onto the designer surface. However, it doesn’t bring up the dialog from the previous method, instead opting to immediately create an entity with a default name, entity set name, and entity key property. You then have to use the designer to modify its configuration to suit your needs.

The steps needed to finish configuring the entity are as follows:

1. If required, create an inheritance relationship by specifying that the entity should inherit from a base entity.
2. Create the required properties on the entity, setting at least one as an entity key.
3. Using the Mapping Details tool window, map these properties to the storage schema.
4. Create any associations with other entities in the model.
5. Validate your model to ensure that the entity is mapped correctly.

 

You aren’t limited to mapping to a single database table/view per entity. This is one of the benefits of building a conceptual model of the database — you may have related data spread across a number of database tables, but through having a conceptual entity model layer in the Entity Framework, you can bring those different sources together into a single entity to make working with the data a lot easier in code.

Creating/Modifying Entity Associations

You have two ways of creating an association between two entities. The first is to right-click the header of one of the entities and select Add New ➪ Association from the context menu. This displays the dialog shown in Figure 27-12.

This dialog includes:

• Association Name: Give the association a name. This becomes the name of the foreign key constraint in the database if you update the database from the model.
• Endpoints: These specify the entities at each end of the association, the type of relationship (one-to-one, one-to-many, and so on), and the name of the navigation properties that it creates on both entities to navigate from one entity to the other over the association.
• Add Foreign Key Properties to the Entity: This enables you to create a property on the “foreign” entity that acts as a foreign key and map to the entity key property over the association. If you’ve already added the property that will form the foreign key on the associated entity, you should uncheck this check box.

The other way to create an association is to click the Association component in the Toolbox, click one entity to form an end on the association, and then click another entity to form the other end of the association. (If it is a one-to-many relationship, select the “one” entity first.) Using this method gives the association a default name, creates the navigation properties on both entities, and assumes a one-to-many relationship. It will not create a foreign key property on the “foreign” entity. You can then modify this association as required using the Properties tool window.

Despite having created the association, you aren’t done yet unless you used the first method and selected the option to create a foreign key property for the association. Now you need to map the property that acts as the foreign key on one entity to the entity key property on the other. The entity whose primary key is one endpoint in the association is known, but you have to tell the Entity Framework explicitly which property to use as the foreign key property. You can do this by selecting the association in the designer and using the Mapping Details tool window to map the properties.

When this is done, you may want to define a referential constraint for the association, which you can assign by clicking the association in the designer and finding the Referential Constraint property in the Properties tool window.

Entity Inheritance

In the same way that classes can inherit from other classes (a fundamental object-oriented concept), so can entities inherit from other entities. You have a number of ways to specify that one entity should inherit from another, but the most straightforward method is to select an entity in the designer, find its Base Type property in the Properties tool window, and select the entity from the drop-down list that this entity should inherit from.

Validating an Entity Model

At times your entity model may be invalid (such as when a property on an entity has not been mapped to the storage model, or its type cannot be converted from/to the mapped column’s data type in the database); however, despite having an invalid entity model your project can still compile.

You can run a check to see if your model is valid by right-clicking the designer surface and selecting the Validate menu item from the context menu. This checks for any errors in your model and displays them in the Error List tool window.

You can also set the Validate On Build property for the conceptual model to True (click an empty space on the designer surface, and then you can find the property in the Properties tool window), which automatically validates the model each time you compile the project. However, again, an invalid model will not stop the project from successfully compiling.

Updating an Entity Model with Database Changes

The structure of databases tends to be updated frequently throughout the development of projects, so you need a way to update your model based on the changes in the database. To do so, right-click the designer surface, and select the Update Model from Database menu item. This opens the Update Wizard (as shown in Figure 27-13) that obtains the schema from the database, compares it to the current storage model, and extracts the differences. These differences display in the tabs in the wizard. The Add tab contains database objects that aren’t in your storage model, the Refresh tab contains database objects that are different in the database from their corresponding storage model objects, and the Delete tab contains database objects that are in the storage model but no longer in the database.

Select the items from these three tabs that you want to add, refresh, or delete, and click the Finish button to have your entity model updated accordingly.

LINQ to Entities Overview

Details about LINQ can be found in Chapter 46, “Language Integrated Queries (LINQ),” in the online archive. That chapter specifically focuses on the use of LINQ to Objects, LINQ to SQL, and LINQ to XML; however, the Entity Framework has extended LINQ with its own implementation called LINQ to Entities. LINQ to Entities enables you to write strongly typed LINQ queries against your entity model and have it return the data as objects (entities). LINQ to Entities handles the mapping of your LINQ query against the conceptual entity model to a SQL query against the underlying database schema. This is an extraordinarily powerful feature of the Entity Framework, abstracting away the need to write SQL to work with data in a database.

Getting an Object Context

To connect to your entity model, you need to create an instance of the object context in your entity model. So that the object context is disposed of when you finish, use a using block to maintain the lifetime of the variable:

VB

Using context As New AdventureWorks2014Entities()
    'Queries go here
End Using

C#

using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
{
    // Queries go here
}

If you need to specify the connection to the database (such as if you need to pass in user credentials or use a custom connection string rather than what’s in the App.config file) you can do so by passing the connection string to the constructor of the object context (in this case AdventureWorks2014Entities).

CRUD Operations

It would be hard to argue against the most important database queries being the CRUD (Create/Read/Update/Delete) operations. Read operations return data from the database, whereas the Create/Update/Delete operations make changes to the database. Create some LINQ to Entities queries to demonstrate retrieving some data from the database (as entities), modify these entities, and then save the changes back to the database.

Navigating Entity Associations

Of course, working with data rarely involves the use of a single table/entity, which is where the navigation properties used by associations are useful indeed. A customer can have one or more addresses, which is modeled in your entity model by the Customer entity having an association with the CustomerAddress entity (a one-to-many relationship), which then has an association with the Address entity (a many-to-one relationship). The navigation properties for these associations make it easy to obtain the addresses for a customer.

Start by using the query from earlier to return a customer entity:

VB

Dim qry = From c In context.Customers
          Where c.CustomerID = 1
          Select c

Dim customer As Customer = qry.FirstOrDefault()

C#

var qry = from c in context.Customers
          where c.CustomerID == 1
          select c;

Customer customer = qry.FirstOrDefault();

You can enumerate and work with the addresses for the entity via the navigation properties like so:

VB

For Each customerAddress As CustomerAddress In customer.CustomerAddresses
    Dim address As Address = customerAddress.Address
    'Do something with the address entity
Next customerAddress

C#

foreach (CustomerAddress customerAddress in customer.CustomerAddresses)
{
    Address address = customerAddress.Address;
    // Do something with the address entity
}

Note how you navigate through the CustomerAddress entity to get to the Address entity for the customer. Because of these associations there’s no need for joins in the Entity Framework.

However, there is an issue here with what you’re doing. At the beginning of the loop, a database query will made to retrieve the customer addresses for the current customer. Then, for each address in the loop, an additional database query will be made to retrieve the information associated with the Address entity! This is known as lazy loading — where the entity model requests data only from the database when it actually needs it. This can have some advantages in certain situations; however, in this scenario it results in a lot of calls to the database, increasing the load on the database server, reducing the performance of your application, and reducing your application’s scalability. If you then did this for a number of customer entities in a loop, that would add even more strain to the system. So it’s definitely not an ideal scenario as is.

Instead, you can request from the entity model when querying for the customer entity that it eagerly loads its associated CustomerAddress entities and their Address entities. This requests all the data in one database query, thus removing all the aforementioned issues, because when navigating through these associations the entity model now has the entities in memory and does not have to go back to the database to retrieve them. The way to request that the model does this is to use the Include method, specifying the path (as a string) of the navigation properties (dot notation) to the associated entities whose data you also want to retrieve from the database at the same time as the actual entities being queried:

VB

Dim qry = From c In context.Customers
                            .Include("CustomerAddresses")
                            .Include("CustomerAddresses.Address")
          Where c.CustomerID = 1
          Select c

Dim customer As Customer = qry.FirstOrDefault()

C#

var qry = from c in context.Customers
                            .Include("CustomerAddresses")
                            .Include("CustomerAddresses.Address")
          where c.CustomerID == 1
          select c;

Customer customer = qry.FirstOrDefault();

Updating a Database from an Entity Model

It’s possible with the Entity Framework to create an entity model from scratch, and then have the Entity Framework create a database according to your model. Alternatively, you can start with an existing database, but then get the Entity Framework to update the structure of your database based on the new entities/properties/associations that you’ve added to your entity model. To update the structure of the database based on additions to your model, you can use the Generate Database Wizard by right-clicking the designer surface and selecting the Generate Database from Model menu item.

Adding Business Logic to Entities

Though you are fundamentally building a data model with the Entity Framework rather than business objects, you can add business logic to your entities. The entities generated by the Entity Framework are partial classes, enabling to you extend them and add your own code. This code may respond to various events on the entity, or it may add methods to your entity that the client application can use to perform specific tasks or actions.

For example, you might want to have the Product entity in your AdventureWorks2014 entity model automatically assign the value of the SellEndDate property when the SellStartDate property is set (only if the SellEndDate property does not have a value). Alternatively, you may have some validation logic or business logic that you want to execute when the entity is being saved.

Each property on the entity has two partial methods that you can extend: a Changing method (before the property is changed) and a Changed method (after the property is changed). You can extend these partial methods in your partial class to respond accordingly to the value of a property being changed.

Plain Old CLR Objects (POCO)

One of the big complaints with the first version of the Entity Framework was that your entities had to inherit from EntityObject (or implement a set of given interfaces), meaning that they had a dependency on the Entity Framework — which made them unfriendly for use in projects where test-driven development (TDD) and domain-driven design (DDD) practices were employed. In addition, many developers wanted their classes to be persistence ignorant — that is, contain no logic or awareness of how they were persisted.

By default, the entities generated from the Entity Model Data Wizard in the Entity Framework v6 still inherit from EntityObject, but you now have the ability to use your own classes that do not need to inherit from EntityObject or implement any Entity Framework interfaces, and whose design is completely under your control. These types of classes are often termed Plain Old CLR Objects, or POCO for short.

Entity Framework Core

A lot of work has been invested into making .NET applications (or a subset of .NET applications, actually) work across different platforms. As part of this effort, Microsoft released Entity Framework Core for use in .NET Core applications. This is a lightweight, cross-platform version of Entity Framework 6.0. There is some missing functionality, but the vast majority of features carry across.

If you are thinking about upgrading from Entity Framework Core to Entity Framework 6.0, you might want to reconsider. Although the name (“Entity Framework”) and the classes are the same, the namespace is different. And, in reality, they are different products even if they fulfill the same function. In fact, it’s possible to use both Entity Framework and Entity Framework Core in the same project. From that standpoint, any move from one version to the other could reasonably be considered to be a port and not an upgrade.