Chapter 12. LINQ to Entities Overview
The Entity Framework (EF) is another object-relational data access technology from Microsoft that includes a LINQ implementation. Commonly called LINQ to Entities, EF shipped in Service Pack 1 of the .NET Framework 3.5 release. It is expected to evolve significantly in the coming releases. The first release includes runtime libraries and designer support in Visual Studio for generating classes and metadata files. It supports database and model-driven styles of development.
EF emphasizes model-driven development. It has its own set of concepts and vocabulary that go beyond the CLR classes and database objects. EF can be used with LINQ or Entity SQL. Entity SQL is a query language based on SQL but designed for Entity Framework. However, in this book we are focusing on LINQ, and hence, we will cover the subset of EF pertinent to the use of LINQ. The next section looks at the core concepts and then walks through an example using the EF tools and the runtime.
Understanding Entity Framework Concepts and Components
EF is based on the Entity Data Model (EDM). EDM is an entity relationship (ER) model, as shown in Figure 12.1. EDM uses its own vocabulary, concepts, and artifacts to define data in a format independent of programming languages and relational databases. EDM schemas are used to specify the details of entities and relationships and to implement them in terms of programming languages and database constructs.
Figure 12.1. The EDM entity-relationship model.
EF separates the conceptual model from the storage model, as shown in Figure 12.2. The conceptual model covers types, inheritance, complex members, and relationships. It is described by a conceptual schema normally realized as a .csdl section in an .edmx file. To see the XML, right-click the .edmx file and choose Open With, XML editor. The storage model describes how the data is stored. For relational databases, it covers tables, columns, stored procedures, and so on. It is described by a storage schema typically realized as an .ssdl section in the .edmx file. The conceptual and storage models are bridged with a mapping that relates the concepts from the two models. It is typically captured in an .msl section in the .edmx file. The three spaces—conceptual, storage, and mapping—together bridge the artifacts commonly used by a developer—entity classes in a .cs or .vb file, and the database that stores the data.
Figure 12.2. The logical EDM model spaces described by the XML found in an .edmx file.
The Entity Data Model
An entity is an abstract carrier of data in the application domain. Examples include Customer, Order, and Product. It can be described using a specification language based on XML. It is typically realized in the programming language domain as a C# or VB.NET class that derives from the framework base class EntityObject in the System.Data.Objects.DataClasses namespace. Its metadata is represented by the EntityType class in the System.Data.Metadata.Edm namespace.
A relationship may be between two or more entities. A binary relationship is between two entities such as Customer and Order. They are called ends of the relationship. A unary relationship (sometimes called a self-referencing relationship) involves a single entity typically in two roles, such as Employee and Manager. A ternary relationship involves more than two entities, such as an enrollment relationship that involves Student, Professor, and Course entities. Association is the basic type of relationship, in which each end of the relationship can exist independent of the other and the entities are considered peers. Composition is a more specialized relationship in which one entity is composed in another. Currently, EDM supports unary and binary associations. An association also has a multiplicity attribute that specifies the number of instances of each end. EDM supports the commonly used one-to-many, one-to-one, and many-to-many associations. An association is bidirectional in EDM. You can logically navigate from Customer to Order and from Order to Customer—at least in the model.
The main noteworthy point is that an association is a first-class concept in EDM. It is not merely the navigation properties or the foreign key values. It is realized as navigation properties in the conceptual space and foreign keys in the storage space. Collection-valued navigation properties (such as Customer.Orders) typically use EntityCollection<T>, whereas singleton navigation properties (such as Order.Customer) use the type of the other end. We will look at concrete examples when we review the code generated by the EF designer. The metadata for an association is represented by the AssociationType in the System.Data.Metadata.Edm namespace.
Entity Framework Components
EF is implemented in two separate layers, as shown in Figure 12.3. The EntityClient Data Provider layer is the lower layer that translates from EDM concepts into native SQL concepts. The Object Services layer provides additional services such as entity classes, a LINQ implementation, and an ObjectContext for change tracking.
Figure 12.3. Entity framework layers and components.
LINQ extension methods are implemented for ObjectQuery<T>. LINQ queries against ObjectQuery<T> are translated into Entity SQL and, in turn, into native flavors of SQL supported by an ADO.NET V2 Data Provider. The concepts in the EntityClient layer closely resemble the ADO.NET V2 concepts of Connection, Command, and DataReader. However, they are enhanced by EDM constructs. For example, the connection in the EntityClient layer contains not only the database connection information but also information about the EDM model files.
The EF Object Services layer is very similar to a typical Object Relational Mapping layer. It knows how to translate queries, materialize objects, ensure identity, and track changes. It also manages optimistic concurrency and transactions.
Using the Entity Framework
This section walks you through the steps for using the Entity Framework in Visual Studio 2008 SP1. We will use the graphical designer to generate a model from the Northwind sample database. Then we will use the generated classes for a simple set of operations on objects created from Northwind data.
As described in Chapter 7, “A Quick Tour of LINQ to SQL,” be sure that you have set up a connection to the database. In Visual Studio, select View, Server Explorer, Solution Explorer. In the Server Explorer pane, shown in Figure 12.4, be sure that you can view the tables in your database. Appendix A contains tips for connecting to a database. Next we will outline the steps to generate an entity data model so that it can be used for CRUD operations.
Figure 12.4. Server Explorer showing the Northwind database.
Entity Model Generation
Create a new project in Visual Studio. Here we will use a C# console application named NorthwindEF. In Solution Explorer, right-click the NorthwindEF project and choose Add, as shown in Figure 12.5.
Figure 12.5. Add New Item for using EF.
In the Add New Item dialog, shown in Figure 12.6, select Data in the left pane and ADO.NET Entity Data Model in the right pane, and click Add. Select ADO.NET Entity Data Model, and choose a suitable name, such as Northwind.edmx.
Figure 12.6. The Add Entity data model.
Click Add to bring up the Entity Data Model Wizard, as shown in Figure 12.7. This wizard helps you set up the connection and create a model from a database. Choose Generate from Database and click Next.
Figure 12.7. Entity Data Model Wizard step 1: Generate from the database.
Select the .mdf file or connection from the drop-down for the Northwind database, as shown in Figure 12.8. The Entity connection string shows the information used by the EntityClient Data Provider. It has information about the three model files and the connection string for use by the next layer—the relational data provider.
Figure 12.8. Entity Data Model Wizard step 2: Select a connection.
Click Next to go to the next step: selecting database objects that will be used to generate a model, as shown in Figure 12.9.
Figure 12.9. Entity Data Model Wizard step 3: Expand database objects for the model.
Expand the Tables node, and check Customers and Orders, as shown in Figure 12.10.
Figure 12.10. Entity Data Model Wizard step 4: Select database objects for the model.
Click Finish to generate the model. The wizard generates the Northwind.edmx file, which shows the two entities and the relationship between them (see Figure 12.11). Each entity also shows the navigation properties that let you navigate to another entity. For example, the Customers entity has a property named Orders for navigating to Orders entities. The lower pane shows the mapping information for the entity selected on the designer surface. Note that unlike LINQ to SQL designer, because EF designer does not perform smart plural-singular changes, you may want to use the renaming feature in Visual Studio to create more palatable entity names, such as Customer and Order.
Figure 12.11. Generated model: entities, relationship, and mapping.
In addition to the designer, EF provides a command-line tool, edmgen.exe, for generating classes and model files from a database. We will not cover the details of the tool here because most users typically prefer to use the designer. Refer to the MSDN documentation if you intend to use the command-line tool instead of the graphical designer. (For additional information, see the topic called EDM Generator [EdmGen.exe] found at http://msdn.microsoft.com/en-us/library/bb387165.aspx.)
Understanding the Generated Code
From Solution Explorer, open Northwind.designer.cs under Northwind.edmx. It shows three classes generated by the wizard. The following code segment shows the key parts of the generated classes and attributes without the extensive details that you can see in the file. The main class is NORTHWNDEntities, which is an ObjectContext. It contains an ObjectQuery<T> for each of the mapped tables. It supports only queries. AddToCustomers() and AddToOrders() have separate methods to add new entities for eventual insertion. Delete operations are handled through the base class method DeleteObject().
public partial class NORTHWNDEntities :
global::System.Data.Objects.ObjectContext
{
public global::System.Data.Objects.ObjectQuery<Customers> Customers
{...}
public global::System.Data.Objects.ObjectQuery<Orders> Orders
{...}
public void AddToCustomers(Customers customers)
{...}
public void AddToOrders(Orders orders)
{...}
}
The two entity classes—Customers and Orders—inherit from the Entity Framework base class EntityObject. Hence, you cannot use your own base class. They contain attributes that point to model information in the conceptual space, which, in turn, is mapped to the appropriate counterpart in the storage space via mapping. The storage space connects to the database table. Thus, the model is at the center of programming in the Entity Framework.
The entity classes contain members with EDM attributes. The interesting members are the factory method, the key properties, and the navigation properties. The static factory method CreateCustomers() takes the company name and ID as parameters and stores them through nonnullable properties and returns a new valid Customers entity instance. The key property Customers.CustomerID is used to ensure object identity. The navigation property Customers.Orders is of the framework type EntityCollection<T>, which encapsulates framework functionality for EDM association. On the singleton side, such as Orders.Customers, no special framework type is needed. Unlike in LINQ to SQL, the loading of relationships is explicit using the Load() method. There is no implicit deferred loading. Notice that the foreign key member Orders.CustomerID is also absent. That information is abstracted in the EDM association.
[global::System.Data.Objects.DataClasses.EdmEntityTypeAttribute
(NamespaceName="NORTHWNDModel", Name="Customers")]
public partial class Customers :
global::System.Data.Objects.DataClasses.EntityObject
{
...
public static Customers CreateCustomers
(string companyName, string customerID)
{...}
[global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute
(EntityKeyProperty=true, IsNullable=false)]
public string CustomerID
{...}
[global::System.Data.Objects.DataClasses.
EdmRelationshipNavigationPropertyAttribute
("NORTHWNDModel", "FK_Orders_Customers", "Orders")]
public global::System.Data.Objects.DataClasses.EntityCollection<Orders>
Orders
{...}
}
public partial class Orders :
global::System.Data.Objects.DataClasses.EntityObject
{
public static Orders CreateOrders(int orderID)
{...}
[global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute
(EntityKeyProperty=true, IsNullable=false)]
public int OrderID
{...}
[global::System.Data.Objects.DataClasses.
EdmRelationshipNavigationPropertyAttribute
("NORTHWNDModel", "FK_Orders_Customers", "Customers")]
public Customers Customers
{...}
}
Performing CRUD Operations
In Program.cs, add the following code to perform a simple query. Recall from Chapter 8, “Reading Objects with LINQ to SQL,” that ObjectDumper is a handy utility for printing object graphs that ship with Visual Studio 2008 in source form. We will use it to display query results.
static void Main(string[] args)
{
NORTHWNDEntities db = new NORTHWNDEntities();
var CustInfoQuery = from c in db.Customers
where c.Country == "Spain"
select new
{
Id = c.CustomerID,
Contact = c.ContactName,
City = c.City
};
ObjectDumper.Write(CustInfoQuery);
}
Building and running the project provides the following results for customers from Spain:
Id=BOLID Contact=Martin Sommer City=Madrid
Id=FISSA Contact=Diego Roel City=Madrid
Id=GALED Contact=Eduardo Saavedra City=Barcelona
Id=GODOS Contact=Jose Pedro Freyre City=Sevilla
Id=ROMEY Contact=Alejandra Camino City=Madrid
We’ll look at insert, update, and delete operations next. We will run code very similar to that in Chapter 7. It has small differences:
• LINQ to Entities does not support the LINQ extension method Single(). Hence, First() must be used.
• The method for deletion is on the context, not on ObjectQuery<T>.
As mentioned in Chapter 7, remember to work with a copy of the Northwnd.mdf file (or the database) so that insert/update/delete operations do not affect the main copy of Northwind.
Here is code that you can enter into Program.cs to get some experience with deleting an existing customer and creating a new order:
NORTHWNDEntities db = new NORTHWNDEntities();
// Query for a specific customer
string id = "ALFKI";
var cust = db.Customers.First(c => c.CustomerID == id);
// Change the name of the contact
cust.ContactName = "New Contact";
// Delete an existing Customer: one without any Orders
string id2 = "FISSA";
var cust2 = db.Customers.First(c => c.CustomerID == id2);
db.DeleteObject(cust2);
// Create a new Order and set its Customers property
// EF discovers the new object and infers an insert
Orders ord = new Orders { OrderDate = DateTime.Now };
ord.Customers = cust;
// Save all the changes in one shot
db.SaveChanges();
Using Stored Procedures
EF supports stored procedures for CRUD operations. We will use two stored procedures—OrdersByCustomer, which returns entities, and UpdateOrder, which updates an entity. These stored procedures are not a part of the Northwind sample database, but the code and instructions for creating them are provided in Chapter 10, “Using Stored Procedures and Database Functions with LINQ to SQL.”
Unlike in LINQ to SQL designer, EF designer uses the wizard instead of drag-and-drop from Server Explorer. To access the wizard, switch to the Northwind.edmx file handled by the designer. Right-click the designer surface to bring up the menu shown in Figure 12.12.
Figure 12.12. Adding to an existing entity data model.
Select Update Model from Database to bring up the Update Wizard. Using the wizard as described in earlier sections, add the two stored procedures to the model. Then right-click the designer surface to bring up the menu shown in Figure 12.12. Select Model Browser, open the Stored Procedures folder, right-click OrdersByCustomer, and select Create Function Import, as shown in Figure 12.13.
Figure 12.13. Adding a F procedure.
The Add Function Import dialog appears, as shown in Figure 12.14. Select a Return Type of Entities, and pick Orders from the drop-down.
Figure 12.14. Mapping a stored procedure to a method.
Click OK to add a function with the same name. Save Northwind.edmx, and browse the regenerated Northwind.designer.cs file. It shows a new method, OrdersByCustomer, in the NORTHWNDEntities class. You can use it by adding the following code to Program.cs:
NORTHWNDEntities db = new NORTHWNDEntities();
var ords = db.OrdersByCustomer("BOLID");
ObjectDumper.Write(ords);
As expected, it brings back the orders for the specified customer. Additional members of Orders entities are omitted in the following result:
EmployeeID=4 Freight=77.9200 OrderDate=10/10/1996 OrderID=10326
...
EmployeeID=4 Freight=97.0900 OrderDate=12/29/1997 OrderID=10801
...
EmployeeID=9 Freight=16.1600 OrderDate=3/24/1998 OrderID=10970
...
For the update procedure, we need to switch back to the designer—the Northwind.edmx file. In the designer, click the Orders entity, and then click the second icon on the left side (the ToolTip says Map Entity to Functions). Click Select Update Function and choose UpdateOrder from the drop-down, as shown in Figure 12.15.
Figure 12.15. Mapping insert, update, and delete operations to stored procedures.
The Mapping Details window shows the mapped stored procedure. You can change the parameter to entity member mapping or pick the original value instead of the current value for some of the parameters. However, UpdateOrder does not use original values. Hence, we will use the defaults shown in Figure 12.16 based on name matching. One parameter that the designer cannot map is CustomerID. Map it manually to Customers.CustomerID by clicking the corresponding cell in the Property column and selecting the navigation property displayed.
Figure 12.16. Mapping stored procedure parameters.
Unfortunately, due to a restriction in the first release of the schema, you cannot map just an update function; you need to map the insert and delete functions as well. So create stored procedures for inserting and deleting orders, and map them using the steps outlined before. For this discussion, simple stored procedures that just raise errors are enough to show how an update stored procedure is mapped. After they are mapped, the stored procedures are automatically used in lieu of dynamic SQL.
Making Sense of LINQ to Relational Choices
LINQ has proven to be a popular technology. Soon after the release of the first set of LINQ-enabled components in .NET Framework 3.5, a number of implementations came out. In this book we look at two implementation in the .NET Framework—LINQ to SQL and LINQ to Entities. Additional implementations exist, such as that in LLBLGen. At the time of this writing, there is also a rudimentary nhibernate LINQ project. Given the very strong positive response to LINQ, the number and quality of implementations are likely to go up. This is a very positive sign for the developer community, because they will have a good range of choices, and competition will ensure continuing improvements.
Given the choice, a common question is which LINQ to Relational technology is the most appropriate for current and future needs. A number of blogs and online papers provide guidance on this matter. Although you could get some information from these, it is best to experiment with the candidate technologies and decide which one suits your needs best. The design approach, complexity, scope, limitations, performance, cost, and community support levels are quite different across various implementations of LINQ.
In the .NET Framework, as of the first release, LINQ to SQL is very lightweight and intentionally keeps the mapping simple. It lets you write your own classes and map them directly if you want to. It also lets you use a graphical or command-line tool to generate classes and mapping from a database. On the other hand, EF has a much richer and more complex concept of a model between classes and tables. It follows a more prescriptive pattern, with a required base class and an emphasis on separation of the classes, the model, and the database. LINQ to SQL is designed to help you build robust applications quickly while helping you keep a simple, easy-to-maintain architecture. Use it if you can, and move on to LINQ to Entities if you see that your application has a clear need for model-driven development, complex mappings, or supported providers.
Developers interested in strong testability, a wide selection of providers, and a strong community may find nhibernate attractive as it improves its LINQ support.
Summary
The Entity Framework (EF) provides an implementation of LINQ for relational databases. It is based on the Entity Data Model (EDM) and emphasizes model-driven development. Entities and associations are first-class concepts in EDM and are at the heart of the design and runtime experience. EF is implemented as two layers—object services for a typical C#/VB.NET, and LINQ user and EntityClient for the implementation of the services in terms of EDM-aware connections, commands, and DataReaders.
EF designer provides a wizard for generating classes and model files from the database. The generated classes inherit from EntityObject and follow a strong prescribed pattern. The EF designer also enables the use of stored procedures.
EF is one of several LINQ to Relational solutions. It is likely to receive additional investments for more providers and a broader set of scenarios. However, other choices are available for the LINQ user with different characteristics.