A common question from developers is regarding the Entity Framework's relationship with LINQ to SQL, and which technology they should use when creating data-centric applications. Let's take a look at the advantages each have over the other.

LINQ to SQL advantages over the Entity Framework:

Entity Framework advantages over LINQ to SQL:

Here are some of the important concepts involved in the Entity Framework and some of the terms that are used throughout this chapter:

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 29-1). Call it AdventureWorksLTModel.edmx.

Figure 29-1. Figure 29-1

This will start the Entity Data Model Wizard that will help you get started building an Entity Framework model.

Figure 29-2. Figure 29-2

This will show the dialog shown in Figure 29-2 that enables you to select whether you want to automatically create a model from a database (Generate from Database), or start with an empty model (Empty Model).

The Empty Model option is useful when you want to take the approach of creating 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, as previously stated you will be creating an entity model from the AdventureWorksLT database, so for the purpose of this example use the Generate from Database option, and get the wizard to help you create the entity model from the database.

Figure 29-3. Figure 29-3

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

If you are using 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 in order for it to create a connection to the database. If you don't select the checkbox to save the connection settings in the App.config file you will also need to pass the model the details on how to connect to the database before you can query it.

Figure 29-4. Figure 29-4

In the next step, the wizard uses the connection created in the previous step to connect to the database and retrieve its structure (that is, its tables, views, and stored procedures), which is displayed in a tree for you to select the elements to be included in your model (see Figure 29-4).

Other options that can be specified on this screen include:

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. From here you can view the model in the Entity Framework and adjust it as per your requirements and tidy it up as per your tastes (or standards) to make it ideal for querying in your code.

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

Figure 29-5. Figure 29-5

You'll note that 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 will automatically move the association lines and try and keep them neatly laid out. Entities will automatically snap to a grid, which you can view by right-clicking the designer surface and selecting Grid

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

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 29-6). The button sandwiched between these zoom in/out buttons zooms to 100% when clicked.

Figure 29-6. Figure 29-6

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 will 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 29-7 shows an entity in its normal expanded state, with the Properties/Navigation Properties groupings rolled up and completely rolled up.

Figure 29-7. Figure 29-7

You can expand all the collapsed entities in one go by right-clicking the designer surface and selecting Diagram

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

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

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 29-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 item(s) in the designer (an entity, association, or inheritance), enabling you to modify their values as required.

Figure 29-8. Figure 29-8

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 29-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 are displayed 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.

Figure 29-9. Figure 29-9

The second picture in Figure 29-9 demonstrates the searching functionality available in the Model Browser tool window. As previously discussed, 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 very 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 Address, which highlighted all the names in the hierarchy (including entities, associations, properties, and so on) that contained the search term. You'll note that 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 are finished 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 29-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 of mapping 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, we'll focus here just on the features of mapping entity properties to tables and views.

Figure 29-10. Figure 29-10

The table/view mapping has a hierarchy (under the Column column) showing the table(s) 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, as previously discussed). 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 will map the columns to one table, if it's another type it will map 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 will contain 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 whether the table should be used as the source of the data for the given properties.

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 is providing 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 wish 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.

Adding Properties to an Entity

Let's now look at the process of adding properties to an entity. Three types of properties exist:

• Scalar properties: Properties with a primitive type, such as string, integer, boolean, and so on.

• Complex properties: A grouping of scalar properties in a manner similar to a structure in code. Grouping properties together in this manner can make your entity model a lot more readable and manageable.

• Navigation properties: Used to navigate across associations. For example, the SalesOrderHeader entity contains a navigation property called SalesOrderDetails that enables you to navigate to a collection of the SalesOrderDetail entities related to the current SalesOrderHeader entity. Creating an association between two entities automatically creates the required navigation properties.

The easiest way to try this for yourself is to delete a property from an existing entity and add it back again manually. Delete a property from an entity (select it in the designer and press the Delete key). Now to add it back again, right-click the entity and select Add

The next step is to set the type of the property, for which you'll have to move over to the Properties tool window to set. The default type is string, but you can change this to the required type by setting its Type property.

Properties that you want to designate as entity keys (that is, properties that are used to uniquely identify the entity) need their Entity Key property set to True. The property in the designer will have a picture of a little key added to its icon, making it easy to identify which properties are used to uniquely identify the entity.

You can set numerous other properties on a property, including assigning a default value, a maximum length (for strings), and whether it's nullable. You can also assign the scope of the getter and setter for the property (public, private, and so on), useful for, say, a property that will be mapped to a column with a calculated value in the database where you don't want the consuming application to attempt to set the value (by making the setter private).

The final task is to map the property to the store model. You do this as described earlier in the chapter using the Mapping Details tool window.

Creating Complex Types

Create a complex type on the Customer entity grouping the various customer name-related properties together in a complex type and thus making the Customer entity neater. Though you can create a complex type from scratch, the easiest way to create a complex type is to refactor an entity by selecting the scalar properties on the entity to be included in the complex type and having the designer create the complex type from those properties. Follow these instructions to move the name-related properties on the Customer entity to a complex type:

• Select the name-related properties on the Customer entity (FirstName, LastName, MiddleName, NameStyle, Suffix, Title) by selecting the first property, and while holding down the Ctrl key selecting the other properties (so they are all selected at the same time).

• Right-click one of the selected properties and select the Refactor into New Complex Type menu item.

• In the Model Browser will be the new complex type that it created, with its name displayed in a textbox for you to name to something more meaningful. For this example, simply call it CustomerName.

• The Entity Framework designer will have created a complex type, added the selected properties to it, removed the selected properties from the entity, and added the complex type that it just created as a new property on the entity in their place. However, this property will just have ComplexProperty as its name, so you will want to rename it to something more meaningful. Select the property in the designer, press F2, and enter Name in the textbox.

You will now find that by grouping the properties together in this way, the entity will be easier to work with in both the designer and in code.

Figure 29-11. Figure 29-11

Creating an Entity

So far you've been modifying existing entities as they were created by the Entity Data Model Wizard. However, let's now take a look at the process of creating 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 we'll walk through the required steps to get an entity configured from scratch.

You have two ways of manually creating entities. The first is to right-click the designer surface and select Add

The other way of creating an entity is to drag and drop the Entity component from the Toolbox onto the designer surface. However, you'll note that 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 will 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:

• If required, create an inheritance relationship by specifying that the entity should inherit from a base entity.

• Create the required properties on the entity, setting at least one as an entity key.

• Map these properties to the storage schema (using the Mapping Details tool window).

• Create any associations with other entities in the model.

• Validate your model to ensure that the entity is mapped correctly.

Note

All entities must have an entity key that can be used to uniquely identify the entity. Entity keys are conceptually the same as a primary key in a database.

As discussed earlier, 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 are able to bring those different sources together into a single entity to make working with the data a lot easier in code.

Note

Make sure you don't focus too much on the structure of the database when you are creating your entity model — the advantage of designing a conceptual model is that it enables you to design the model based on how you plan to use it in code. Therefore, focus on designing your entity model, and then you can look at how it will map to the database.

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

Figure 29-12. Figure 29-12

This dialog includes:

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

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

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 of specifying 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.

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 will 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 will automatically validate the model each time you compile the project. However, again, an invalid model will not stop the project from successfully compiling.

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 29-13) that will obtain the schema from the database, compare it to the current storage model, and extract the differences. These differences are displayed 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.

Figure 29-13. Figure 29-13

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 was covered in the previous chapter, specifically focusing 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.

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 once you're finished you'll use a using block to maintain the lifetime of the variable:

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 AdventureWorksLTEntities).

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.

Of course, working with data rarely involves the use of a single table/entity, which is where the navigation properties used by associations are very 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 very easy to obtain the addresses for a customer.

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

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

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. What is happening here is as you navigate through the CustomerAddress entity to the Address entity is that it's doing another database query to get the collection of CustomerAddress entities for the customer, and then in the for loop doing yet another database query for each CustomerAddress entity to get the corresponding Address entity! This is known as lazy loading — where the entity model only requests data 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 will request all the data in one database query, thus removing all the aforementioned issues, because when navigating through these associations the entity model will now have the entities in memory and 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:

As mentioned earlier, 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.

Though you are fundamentally building a data model with the Entity Framework rather than business objects, it is possible to 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 AdventureWorksLT 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.

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