DO.NET provides many rich features that you can use to retrieve data from data sources in a number of ways, but with that flexibility developers sometimes end up tightly coupling their client applications to data sources such as relational databases. This can happen even when developers try to architect their program with a separate data access layer.

In tightly coupled applications the client application has intimate knowledge of the database schema implementation?thereby making it extremely difficult to apply the code and concepts to a different area or a different data store, or simply making it difficult or impossible to alter the data store without also altering the application code. The tight coupling isn’t always one way; altering the application may require changes to the database as well.

As an example, consider the following data access code, which retrieves the Product data from the AdventureWorks database.

   string connString = ?..  ;   SqlConnection conn = new SqlConnection(connString);   conn.Open();   SqlCommand command = conn.CreateCommand();   command.CommandText =      "Select ProductID, Name from Production.Product";   SqlDataReader reader = sqlCmd.ExecuteReader();   return reader;

Suppose that?sometime after you’ve deployed the preceding code into production?a database administrator changes the Name column from the Product table to ProductName. The only way you can prevent the application from breaking is by revisiting the source, making changes to any code that refers to the Name column, and recompiling, retesting, and redeploying the application. For example, you’d have to change the SQL for the command.CommandText property from the preceding code to:

   command.CommandText =      "Select ProductID, ProductName from Production.Product";

As there might be many places in the application code that rely on the existing naming scheme, this is a very inflexible implementation, making it difficult to upgrade the application.

To solve this problem, ADO.NET vNext introduces a new layer of abstraction named the Entity Data Model (EDM). The EDM provides a richer and more intuitive view of the data model from the application’s perspective as opposed to the more normalized view of the data as based on optimal storage. Because of this, client applications and the database schema can evolve independently?without breaking each other. This article introduces EDM by discussing its design philosophy and showing examples of how you create one. In addition, this article introduces the new mapping provider and Entity SQL features introduced with ADO.NET vNext.

What is ADO.NET vNext?
The EDM and other features are wrapped up into the ADO.NET Entity Framework (called ADO.NET vNext), which includes:

• The Entity Data Model (EDM), which allows developers to model data at higher abstraction levels.
• A powerful client-views/mapping engine to map to and from store schemas.
• Full query support over EDM schemas using a new query language called Entity SQL and LINQ.
• An object services layer that allows you to choose to present query results as either tabular data (rows and columns) or as objects. When using .NET objects, the system transparently performs identity resolution, change tracking, and update processing for you.
• An extensible and open provider model that allows other stores to plug into the ADO.NET Entity Framework.

	?
Figure 1. ADO.NET Entity Data Model Design: The EDM allows each client application to have its own view of the database, choosing a view that’s relevant to the problem domain specific to that application.

This version of ADO.NET also includes two Language Integrated Query (LINQ) flavors that you may not yet be familiar with:

• LINQ to Entities. Enables you to execute LINQ queries against EDM schemas.
• LINQ to DataSet. Allows you to execute LINQ queries against one or more DataTable objects. The LINQ to DataSet implementation even optimizes certain query patterns for better execution performance.

The core of the ADO.NET Entity Framework is the Entity Data Model, which is essentially a conceptual view of the database schema created by the application developers. Under the covers, this view is described as an XML mapping file in your application, which maps entity properties and relationships to database tables and foreign key (FK) relationships. It’s this mapping that abstracts applications from changes to the relational database schema. Rather than changing your application when a change occurs to the database schema, you need only change the XML mapping file to reflect the schema change?without changing any source code. I’ll show you an example later in this article.

As you can see from Figure 1, you can create any number of application-level database views through the EDM.

Figure 1 illustrates how several applications can create different views (such as a Sales View and a SalesOrders View) of a database through their own entity data models. The specific models enable application developers to work with the database within that application in a way that makes most sense for the business functionality they are trying to provide.

Creating an Entity Data Model
There are two ways to create an entity data schema.

1. By manually hand-coding XML mapping files and using them directly from a client application.
2. Through the EDM Designer Prototype tool. Although this is a prototype, it can give you an idea of the types of tools Microsoft seeks to provide in the final release version. Currently, this tool has limited functionality, but it should be a much better product by release time.

This article discusses how to use the EDM Designer Prototype tool to create EDM schemas. Then, after creating the EDM schemas with the tool, I’ll provide a brief look at the XML mapping files it creates.

To follow along you’ll need to have Visual Studio Professional, and you’ll need to download and install these three items:

• LINQ May 2006 CTP Edition
• ADO.NET vNext August CTP
• ADO.NET vNext Entity Data Model Designer Prototype, CTP

The installations are straightforward, so I won’t discuss them here. After you’ve installed the products, create a new project named EntityDataModelSample (see Figure 2).

? Figure 2. Creating a New Project: By installing the ADO.NET vNext CTP, you can use the new project template wizard to create a new ADO.NET vNext project.

? Figure 3. Adding a new Entity Data Model: By choosing the Entity Data Model item template, you also get a basic designer that allows you to create EDM schemas.

After creating the project, add a new Entity Data Model named AdventureWorksModel to the project using the Project->Add New Item menu as shown in Figure 3.

Generate or Import the Model Contents
Note that the system saves entity data models with an .edm extension. After creating the model file, you need to choose one of two options to begin generating the model contents. The options are:

1. Generate from Database
2. Import Model File

Both options are available when you right click on the model designer (see Figure 4).

? Figure 4. Entity Data Model Wizard Step 1: The wizard’s first step lets you specify whether you want to generate the model from an empty model or from a database.

? Figure 5. Entity Data Model Wizard Step 2: In this step, you specify the connection string information for the database from which you want to generate the models.

? Figure 6. Entity Data Model Wizard Step 3: In this step, you specify the tables in the database from which you want to generate the models.

For this example, select the “Generate from database” option, and click “Next.” You’ll see the screen shown in Figure 5.

From the screen in Figure 5, select the server name and the database to use to generate the model. When you click “Next”, you’ll see the screen in Figure 6, from which you select the tables in the database that you want to include in the entity data model.

In this example, select the ProductCategory, ProductSubcategory, and Product tables from the list of tables in the AdventureWorks sample database, and then click “Finish.” At this point, save the entire project, then open up the Solution Explorer which should look something like Figure 7.

? Figure 7. Files Generated by the EDM Wizard: After generating the model, you’ll have three XML files and a class file that provides an object representation of the EDM schema.

? Figure 8. AdventureWorks Model: The EDM model contains all the entities and the relationships between them.

If you now open up the file AdventureWorksModel.edm by double clicking on it, you’ll see the EDM model diagram shown in Figure 8.

This should look familiar?much like a standard database diagram, but underneath, Visual Studio uses the three XML files mentioned earlier. These files are used by the EDM->Database mapping engine to figure out the information required to execute queries. The generated XML files are:

1. CSDL file. This is the entity data model that provides a specific view of the application. Right under the CSDL file, you’ll see a .cs (C# source) file that provides the object representation of the entity data model so that you can work with the EDM schema as if you are working with an object model. I’ll cover this feature more fully in future installments of this article series.
2. SSDL file. This file holds schema information from the three database tables (selected in the EDM Wizard) from the AdventureWorks database expressed in EDM terms.
3. MSL file. This file holds mapping information that describes how the various entity/association sets and tables connect with each other.

Now that you’ve created the mapping files, you’re ready to program against the EDM schema.

Programming Against the EDM
To be able to communicate with the EDM schema and execute queries against that schema, the ADO.NET team has introduced two new features:

1. Mapping Provider. This is similar to the .NET SQL Client Provider except that it lets you talk to an EDM schema rather than directly to a database. The important classes in this layer are MapConnection and MapCommand. Based on the EDM schema and the mapping information, the mapping provider internally uses the mapping infrastructure to translate between the entity data model and the physical relational data model. By using the EDM model and the mapping provider your application no longer needs to use or understand database-specific constructs; the entire application operates in terms of the higher-level EDM model.
2. Entity SQL. This is a data query/command language similar to SQL, except that it’s oriented towards executing queries against the EDM schema rather than directly against a database schema. Entity SQL is designed to fully leverage the expressivity of EDM, letting you formulate queries statically formulated at design time or construct them dynamically at runtime. Because Entity SQL queries the EDM schema which is closely aligned with the objects and concepts in your application, queries often become much easier to write and understand as opposed to queries against a normalized database schema, which quickly become complicated in standard SQL?particularly when you have to write queries with multiple joins.

If you’re familiar with traditional ADO.NET 2.0 data access code, you can easily migrate those applications to use the new mapping provider with couple of minor changes. Here’s a skeleton implementation that shows the steps involved in communicating with the database using the familiar ADO.NET 2.0. The original code might look like this:

   using (SqlConnection connection = new      SqlConnection(...))   {     using (SqlCommand command =        connection.CreateCommand())     {       command.CommandText = "...";       using (IDataReader reader =          command.ExecuteReader())       {         // ... Process the records       }     }   }

To modify the preceding code to use the new mapping provider, all you need to do is to make two changes: Change the SqlConnection to a MapConnection object and change the SqlCommand objects to MapCommand objects. Here’s the altered code:

   using (MapConnection connection = new      MapConnection(...))   {     using (MapCommand command =        connection.CreateCommand())     {       command.CommandText = "...";       using (IDataReader reader =          command.ExecuteReader())       {         // ... Process the records       }     }   }

Now that you have a basic understanding of the mapping provider and entity SQL, here’s a simple example that leverages both against the AdventureWorksModel EDM created earlier.

Executing Entity SQL Through the Mapping Provider
This first example executes a simple Entity SQL query against the AdventureWorksModel schema to retrieve the ProductSubcategory data. Add a new Button control named btnGetCategories and a ListBox (named lstCategories) to your project’s default form, and modify the Click event code of the button as follows:

   private void btnGetCategories_Click    (object sender, EventArgs e)   {        using (MapConnection connection = new           MapConnection("name=AdventureWorksConnectionString"))     {       connection.Open();       MapCommand command =          connection.CreateCommand();                       command.CommandText =          "SELECT VALUE p FROM " +              "AdventureWorksModel." +         "AdventureWorks" +         ".ProductSubcategory AS p ";       DbDataReader reader =          command.ExecuteReader(         CommandBehavior.SequentialAccess);                        lstCategories.Items.Clear();       while (reader.Read())       {                             lstCategories.Items.Add           (reader["ProductSubcategoryID"]           .ToString() + " - " +            reader["Name"]);       }     }   }

The above code is simple and straightforward if you are familiar with ADO.NET programming model. First, you open the connection to the database, set the properties of the command object and finally invoke the ExecuteReader() method of the command object to return the results. Notice that the code uses the CommandBehavior.SequentialAccess enumeration value when it invokes the ExecuteReader() method. The enumeration value is required for executing queries through the mapping provider. Passing the CommandBehavior.SequentialAccess enumeration value tells the ADO.NET runtime to load the data sequentially as it is received in the form of a stream. The preceding code retrieves the connection string from the app.config file, where it is stored as follows:

Now look at the Entity SQL used to query the EDM. The VALUE keyword allows you to retrieve all the values in a specific entity. In this example, it retrieves all the elements contained in the ProductSubcategory entity.

	?
Figure 9. Form Output: Clicking the “Get Categories” button, retrieves all the categories and displays them in the list box.

   command.CommandText =      "SELECT VALUE p FROM " +          "AdventureWorksModel.AdventureWorks.ProductSubcategory" +      "AS p ";

Note that the FROM clause of the query is made up of three components arranged in the following sequence:

   ..

After adding the code, run the form by pressing F5 and click on the “Get Categories” button and you should see output similar to Figure 9.

Passing Parameters to Entity SQL
Here’s a slightly more complex example that demonstrates how to pass a parameter to an Entity SQL query to obtain the details of a specific product. To build this example, create a new form and place controls similar to those shown in Figure 10: a single-line Text control, a Button, and a ListBox. The button is named btnGetProductDetails; here’s the code for its Click event:

	?
Figure 10. Parameterized Query Output Results: When you specify a product ID and click on the “Get Product Details” button, you retrieve the details of the specific product and display them in the list box.

   private void btnGetProductDetails_Click   (object sender, EventArgs e)   {     using (MapConnection connection = new       MapConnection("name=AdventureWorksConnectionString"))     {       connection.Open();       MapCommand command =          connection.CreateCommand();        command.CommandText =          "SELECT p.ProductID, p.Name, " +         "p.ProductNumber FROM " +                  "AdventureWorksModel." +         "AdventureWorks.Product AS p" +         "WHERE p.ProductID = @ProductID";                       command.Parameters.AddWithValue         ("ProductID",Convert.ToInt32         (txtProductID.Text));       DbDataReader reader =          command.ExecuteReader(         CommandBehavior.SequentialAccess);       lstProductDetails.Items.Clear();       while (reader.Read())       {                                 lstProductDetails.Items.Add           ("ID = " +                    reader["ProductID"].           ToString());         lstProductDetails.Items.Add           ("Name = " +  reader["Name"]);                     lstProductDetails.Items.Add           ("Product Number = " +             reader["ProductNumber"]);       }     }   }       

The preceding example passes the parameter to the Entity SQL by invoking the AddWithValue() method of the Parameters collection.

   command.Parameters.AddWithValue      ("ProductID",Convert.ToInt32      (txtProductID.Text));

Figure 10 shows the results.

A Schema Abstraction Example
Now that you have had a chance to see how to use the EDM model, let’s revisit the name-change scenario discussed at the beginning of this article, using an example that illustrates how useful EDM is in abstracting the database schema from your applications. Begin by making a small change to the database to recreate the scenario; rename the Name column in the ProductSubcategory table to ProductSubcategoryName in the AdventureWorks database. After making the change, when you run the application, you’ll get an expected runtime exception:

   "{"Invalid column name 'Name'."}"

Normally, fixing such a breaking change would require you to find every place in your code that accessed that database field, changing code to match the new name. But using the EDM model, all you need to do is to fix this error is open the XML mapping files and make the corresponding changes?there’s no need to touch the application code. Specifically, you need to make two changes:

First, open the AdventureWorksModel.Target.ssdl file and find the node line under the node that has a Name=”ProductSubcategory” attribute:

Change it to reference the new field name as shown below:

Second, in the AdventureWorksModel.cs.msl file, find the node shown below (you’ll find it under the node |||| that has the TableName=”ProductSubcategory” attribute):

Change it to:

Save your changes. Now if you run the application again, it should work just fine. As you can see, you have accommodated the change in the database schema by changing only the mapping files?without having to touch a single line of code in the client application itself.

So, as you’ve seen, not only does using the Entity Data Model and Entity SQL let you make database queries using an application and object-centric approach, it also isolates your application from database schema changes that would otherwise require code changes.

This article discussed the basics of ADO.NET vNext Framework and showed how to create simple EDM models through the EDM designer. You also saw some examples of using the mapping provider and Entity SQL to execute queries against the EDM model. These tools simplify and isolate your application’s interactions with databases?freeing you to focus on the core business logic of the application. The next installments in this series will build on the basics and discuss the advanced features of ADO.NET vNext.