Appendix A: ADO.NET Entity Framework

In this appendix, we will evaluate the new data access framework-ADO.NET Entity Framework-provided by Microsoft ADO.NET Orcas. In particular, we will focus our attention on the capability to manage and query entities, which is something we can do with a standard common relational data source, but with a deep abstraction from the physical data layer.

ADO.NET Standard Approach

Consider a common example of an order management application based on the Microsoft SQL Server 2005 Northwind sample database. In Figure A-1, you can see a subset of the database schema for the order management side.

Figure A-1: The Northwind orders database schema

If you want to query the data from a standard .NET application using ADO.NET 2.0, you can choose a connected or disconnected approach, using a SqlCommand with a SqlDataReader in the former case or a SqlDataAdapter and a DataSet instance in the latter. Imagine that you are developing an online e-commerce Web application and you decide to use a connected approach. You will probably need to query the list of orders placed and submit new orders. Each order made is a main order row (in the Orders table) mapped to a set of order items (in the Order Details table).

In the following example, you can see a simplified query to extract all the orders placed by each customer:

 SELECT c.CustomerID, c.CompanyName, c.ContactName,        o.OrderID, o.OrderDate FROM Customers AS c INNER JOIN Orders AS o      ON c.CustomerID = o.CustomerID

Here are two simplified parametric queries to insert orders and order items:

 -- Order insert statement INSERT INTO [Northwind].[dbo].[Orders]   ([CustomerID] ,[EmployeeID], [OrderDate] ,[RequiredDate) VALUES   (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate) -- Order Detail insert statement INSERT INTO [Northwind].[dbo].[Order Details]   ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES   (@OrderID, @ProductID, @UnitPrice, @Quantity, @Discount)

In Listing A-1, you can see how to invoke the first selection query-for instance, to bind the result to an ASP.NET 2.0 GridView.

Listing A-1: A classic ADO.NET 2.0 query using a SqlDataReader

  SqlConnection cn = new SqlConnection(NWindConnectionString); SqlCommand cmd = new SqlCommand("SELECT c.CustomerID, c.CompanyName, " + "c.ContactName, o.OrderID, o.OrderDate FROM Customers AS c INNER JOIN " + "Orders AS o ON c.CustomerID = o.CustomerID", cn ); using (cn) {     cn.Open();     using (SqlDataReader dr = cmd.ExecuteReader(                                   CommandBehavior.CloseConnection)) {         gridCustomersOrders.DataSource = dr;         gridCustomersOrders.DataBind();     } } 

This book is not the right place to discuss whether it is better to use a DbDataReader or a DataSet to achieve the best balance between performance and scalability. For this reason, we will skip these kinds of considerations. However, we do want to highlight some possible issues with this approach to querying.

First, we are querying the database persistence layer directly from our application code (the ASPX page, in this example), and we are tied to a particular kind of database engine (Microsoft SQL Server 2005). You probably know that ADO.NET 2.0 code can leverage DbProviderFactories to be more independent from the physical database. This flexibility is enormously helpful in our efforts to write more flexible code and to reuse our applications against different database persistence layers. On the other hand, even when using DbProviderFactories, we are working with code that mixes typed .NET syntax and untyped SQL code, as shown in Listing A-2.

Listing A-2: A classic ADO.NET 2.0 query using DbProviderFactories

  DbProviderFactory dbFactory =     DbProviderFactories.GetFactory(NWindProviderName); DbConnection cn = dbFactory.CreateConnection(); cn.ConnectionString = NWindConnectionString; DbCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT c.CustomerID, c.CompanyName, c.ContactName, " +     "o.OrderID, o.OrderDate FROM Customers AS c INNER JOIN Orders AS o " +     "ON c.CustomerID = o.CustomerID"; using (cn) {     cn.Open();     using (SqlDataReader dr = cmd.ExecuteReader(                                   CommandBehavior.CloseConnection)) {         gridCustomersOrders.DataSource = dr;         gridCustomersOrders.DataBind();     } } 

The first issue we are facing is the lack of strongly typed code and the need to merge different kinds of code and syntaxes (C#, SQL, and so on). Consider also that each order is made of a main header row and a set of items. With regard to the physical database, these records are retrieved from different database tables (Orders and Order Details). When querying orders, we can just execute a multiple-resultset query. However, if the application requirements involve common needs such as paging, sorting, and custom ordering in querying items, we will probably need to write queries that are not trivial (even if any good database developer should be able to do that). Finally, another problem in this code is that we are too close to the database within our application code. As you probably know, modern applications are often logically and physically divided into layers, as shown in Figure A-2.

Figure A-2: A modern application with a distributed architecture

At a minimum, applications consist of the following layers:

• An abstract data access layer, which can make the real physical persistence layer transparent to the entire application.

• One or more physical data access layers to map the application to a real persistence layer. Consider that modern applications can have a remote data source, achieved through Simple Object Access Protocol (SOAP) services, and not only and always a relational database.

• A business layer in which we can place validation rules, business logic, transaction management (which is eventually distributed), security checks and authorization policies, business processes, and workflows.

• One or more presentation layers that interact only with the underlying business layer and that usually have no intelligence or logic. This arrangement is necessary to make the higher-level application core business engine independent from the presentation layer.

There are more issues and needs to manage within Service Oriented Architectures (SOA). Nevertheless, even with SOA we will probably have at least all of those basic layers plus some others so that we can abstract the service façade from the physical back end.

In such architectures, we usually cannot transfer data between layers using DbDataReader instances because we do not want to keep database connections open for a long time and because the physical layers could be deployed on different machines (servers). Therefore, it is not convenient to marshal or serialize DbDataReader instances over the wire. You could use a DataSet or a typed DataSet to avoid these issues. However, what happens when your physical data layer consists of more than a database or an XML file? And what other issues will you encounter when you have different physical persistence layers that eventually describe the same conceptual information (such as Orders and Order Details) with different database structures? For instance, you could have a SQL Server 2000 database that describes, with a normalized relational data structure, custom options (color, size, and so on) of each order detail, while you might also have an XML field with SQL Server 2005 to be more versatile. Modern architectures, for these and many other reasons, tend to use custom entities to describe at a conceptual level what you need to manage. The physical layer is just another detail from this point of view, and the overall application architecture should be independent from it.

We have discussed some common issues related to data retrieval for orders already placed. Now consider the other goal of our application: placing orders. We need a data structure to store orders while customers are placing them-the common and classic shopping basket. When the customer confirms his shopping basket, we need to save the header row and each child order detail within the persistence layer using an ACID (Atomicity, Consistency, Isolation, Durability) transaction. Once again, we could use a DataSet to solve this problem, updating the database with a DbDataAdapter. However, keep in mind the same considerations we made regarding data retrieval. As an alternative, we could use custom entities to describe our concept of an order. These orders would be made of various pieces of information, some of which might have multiple occurrences.