Using ADO .NET Entity Framework 5.0

Many database developers thought that database APIs were mature enough with the release of ADO.NET 2.0 and LINQ, but these data access APIs continued to evolve. Data access APIs are reasonably straightforward to use, and they let you simulate the same kinds of data structures and relationships that exist in relational databases.

However, you don't interact with data in data sets or data tables in the same way you do with data in database tables. The differences between the relational model of data and the object-oriented model of programming are considerable, and ADO.NET 2.0 and LINQ do relatively little to reduce impedance between the two models.

With the release of .NET Framework 4.5 and Visual Studio 2012, a new version of ADO.NET Entity Framework 5.0 was introduced. This article will introduce you to the ADO.NET Entity Framework 5.0 data model, also known as the Entity Data Model (EDM).

EDM is Microsoft's way of implementing object-relational mapping (ORM). ORM is a way of processing and mapping relational data into a collection of objects, called entities. You will learn more about it, including the advantages of this approach, in this article.

In this article, I'll cover the following:

• Understanding ADO.NET Entity Framework 5.0
• Understanding the Entity Data Model
• Working with the Entity Data Model

Understanding ADO.NET Entity Framework 5.0

The vision behind the ADO.NET Entity Framework (EF) 5.0 is to extend the level of abstraction for database programming and completely remove the impedance mismatch between data models and development languages that programmers use to write database-oriented software applications.

ADO.NET EF 5.0 allows developers to focus on data through an object model instead of through the traditional logical/relational data model, helping abstract the logical data schema into a conceptual model, a mapping layer, and a logical layer to allow interaction with that model through a new data provider called EntityClient.

In this article, I will review the purpose of each of these layers.

ADO.NET EF 5.0 allows developers to write less data access code, reduce maintenance, and abstract the structure of the data into a more business-friendly manner. It can also help reduce the number of compile-time errors since it generates strongly typed classes from the conceptual model.

ADO.NET EF 5.0 generates a conceptual model that developers can write code against using a new data provider called EntityClient, as mentioned previously. EntityClient follows a model similar to familiar ADO.NET objects, using EntityConnection and EntityCommand objects to return an EntityDataReader.

Understanding the Entity Data Model

The core of ADO.NET EF 3.5 is in its Entity Data Model. ADO.NET EF 3.5 supports a logical store model that represents the relational schema from a database. A relational database often stores data in a different format from what the application can use. This typically forces developers to retrieve the data in the same structure as that contained in the database. Developers then often feed the data into business entities that are more suited for handling business rules. ADO.NET EF 5.0 bridges this gap between data models using mapping layers. There are three layers active in ADO.NET EF 5.0's model.

• Conceptual layer
• Mapping layer
• Logical layer

These three layers allow data to be mapped from a relational database to a more object-oriented business model. ADO.NET EF 3.5 defines these layers using XML files. These XML files provide a level of abstraction so developers can program against the OO conceptual model instead of the traditional relational data model.

The conceptual model is defined in an XML file using the Conceptual Schema Definition Language (CSDL). CSDL defines the entities and the relationships as the application's business layer knows them. The logical model, that represents the database schema, is defined in an XML file using Store Schema Definition Language (SSDL). The mapping layer, that is defined using Mapping Schema Language (MSL), maps the other two layers. This mapping is what allows developers to code against the conceptual model and have those instructions mapped into the logical model.

Working with the Entity Data Model

Most applications running today cannot exist without having a database at the back end. The application and the database are highly dependent on each other; that is, they are tightly coupled, and so it becomes so obvious that any change made either in the application or in the database will have a huge impact on the other end; tight coupling is always two-way, and altering one side will require changes to be synchronized with the other side. If changes are not reflected properly then the application will not function in the desired manner, and the system will break down.

Let's have a look at a tight coupling example by considering the following code:

// Create connection
qlConnection conn = new SqlConnection(@"
                    server = .\sql2012;
                    integrated security = true;
                    database = AdventureWorks");
            // Create command
string sql = @"select Name,ProductNumber
from Production.Product";
SqlCommand cmd = new SqlCommand(sql, conn);
txtReader.AppendText("Command created and connected.\n\n");
try
{
    // Open connection
   conn.Open();
   // Execute query via ExecuteReader
   SqlDataReader rdr = cmd.ExecuteReader();
}

Assume you have deployed the preceding code into production along with the database, that has the column names as specified in the select query. Later, the Database Administrator (DBA) decides to change the column names in all the tables to implement new database policies: the DBA modifies the Production.Product table and changes the Name column to ProductName and the ProductNumber column to ProductSerialNumber.

After these database changes are made, the only way to prevent the application from breaking is by modifying all the code segments in the source code that refer to the Name and ProductName columns, rebuilding, retesting, and deploying the entire application again. So, the modified code segment in the preceding code will appear as follows:           

// Create command
string sql = @"select ProductName, ProductSerialNumber
from Production.Product";

Though on the surface it seems not so difficult to make such changes, if you factor in the possibility that there might be many database-related code segments that require modification of the column names depending on the new column naming scheme, this can end up being a tedious and difficult approach to upgrade an application so it can work with the modified database.

With ADO.NET EF 5.0's Entity Data Model, Microsoft has made entity-relationship modeling executable. Microsoft did this by a combination of XML schema files and ADO.NET EF 5.0 APIs. The schema files define a conceptual layer to expose the data store's schema (for example, the schema for a SQL Server 2012 database) and to create a map between the two. ADO.NET EF 5.0 allows you to write your programs against classes that are generated from the conceptual schema. The EDM then takes care of all of the translations as you extract data from the database by allowing you to interact with that relational database in an object-oriented manner.

The EDM makes it possible for the client application and the database schema to evolve independently in a loosely coupled fashion without affecting or breaking each other.

The EDM of ADO.NET 5.0 Entity Framework provides a conceptual view of the database schema that is used by the application. This conceptual view is described as an XML mapping file in the application. The XML mapping file maps the entity properties and associated relationships to the database tables.

This mapping is the magic wand that abstracts the application from the changes made to the relational database schema. So, rather than modifying all the database-oriented code segments in an application to accommodate changes made in the database schema, you just need to modify the XML mapping file in such a way that it reflects all the changes made to the database schema. In other words, the solution offered by the ADO.NET 5.0 EDM is to modify the XML mapping file to reflect the schema change without changing any source code.

Try It: Creating an Entity Data Model

In this exercise, you will see how to create an EDM.

1. Create a new Windows Forms Application project named Article19. When Solution Explorer opens, save the solution.

2. Right-click the project and select "Add" -> "New Item"; from the Visual Studio templates, choose "ADO.NET Entity Data Model" and name it AWCurrencyModel.edmx; your screen should look as in Figure 1-1. Click "Add".

Figure 1-1. Adding an ADO.NET Entity Data Model

3. The Entity Data Model Wizard will start, with the Choose Model Contents screen appearing first. Select the "Generate from database" option, as shown in Figure 1-2. Click "Next".

Figure 1-2. Entity Data Model Wizard - Choose Model Contents screen

4. The Choose Your Data Connection screen appears next, as shown in Figure 1-3. Click the "New Connection" button.

Figure 1-3. Entity Data Model Wizard - Choose Your Data Connection screen
 
5. The Choose Data Source dialog box appears. Select "Microsoft SQL Server" from the "Data source" list, as shown in Figure 1-4. Click "Continue".

Figure 1-4. Entity Data Model Wizard - Choose Data Source dialog box

6. Next, the Connection Properties dialog box appears. Enter .\SQL2012 in the "Server name" list box and ensure that the Use Windows Authentication radio button is selected. From the list box provided below the "Select or enter a database name" radio button, select "Northwind". Your dialog box should look as in Figure 1-5. Click the "Test Connection" button.

Figure 1-5. Entity Data Model Wizard-Connection Properties dialog box

7. A message box should flash showing the message "Test connection succeeded." Click "OK". Now click "OK" in the Connection Properties dialog box.

8. The Choose Your Data Connection window appears, again displaying all the settings you've made so far. Ensure the check box "Save entity connection settings in App.config as" is selected and has "AWCurrencyEntities" as a value entered in it, as shown in Figure 1-6. Click "Next".

Figure 1-6. Entity Data Model Wizard - Choose Your Data Connection screen with settings displayed

9. The Choose Your Database Objects screen now appears. Expand the "Tables" node. If any of the table or tables are selected, remove all the check marks except for the ones beside the "Sales.Currency" table. Also, remove the check marks from the as in the following:Views and "Stored Procedures" nodes. The screen will look as in Figure 1-7. Click "Finish".


Figure 1-7. Entity Data Model Wizard: Choose Your Database Objects screen

10. Navigate to Solution Explorer, and you will see that a new "AWCurrencyModel.edmx" object has been added to the project, as shown in Figure 1-8.

Figure 1-8. Solution Explorer displaying the generated Entity Data Model

11. Double-click "AWCurrencyModel.edmx" to view the generated Entity Data Model in the Design view. It should look as in Figure 1-9.

Figure 1-9. Entity Data Model in the Design view

12. The generated Entity Data Model also has an XML mapping associated with it especially for its EntityContainer and EntitySets. To view the XML mapping, navigate to Solution Explorer, right-click "AWCurrencyModel.edmx", and choose the "Open With" option. From the dialog box that appears, select "XML (Text) Editor" and click "OK". Notice the highlighted text in the mapping shown in Figure 1-10.

Figure 1-10. XML mapping associated with the Entity Data Model

13. Switch to Solution Explorer, and rename Form1 to "PublishCurrency.cs".

14. Drag a TextBox control to the form, and position it toward the center of the form. Select this TextBox control, navigate to the Properties window, and set the following properties:

• Set the Name property to txtCurrency.
• For the Location property, set X to 12 and Y to 12.
• Set the Multiline property to True.
• Set the ScrollBars property to Vertical.
• For the Size property, set Width to 518 and Height to 247.
• Leave the Text property blank.

15. Now your PublishCurrency form in the Design view should as in Figure 19-11.

Figure 1-11. The Design view of the PublishCurrency form

16. Double-click the empty surface of the form and it will open the code editor window showing the "PublishCurrency_Load" event. Place the code listed in Listing 1-1 into the load event code template.

Listing 1-1. Using the Entity Data Model               

AWCurrencyEntities currContext = new AWCurrencyEntities();          

foreach (var cr in currContext.Currencies)

{

      txtCurrency .AppendText(cr.ModifiedDate.ToString()); 

      txtCurrency.AppendText("\t\t");

      txtCurrency.AppendText(cr.CurrencyCode.ToString());                   

      txtCurrency.AppendText("\t\t");

      txtCurrency.AppendText(cr.Name.ToString());

      txtCurrency.AppendText("\t");

      txtCurrency.AppendText("\n");

}

17. Build the solution, and run the project. When the PublishCurrency form appears, you will see all the currencies listed in the TextBox. The screen shown in Figure 1-12 should display.

Figure 1-12. Displaying the PublishCurrency form

How It Works

Because you are working with an Entity Data Model, you do not need to deal with SqlConnection, SqlCommand, and so forth. Here you create an object referencing the EntityContainer named AWCurrencyEntities, that refers to the entire connection string that is stored in the App.config file.

AWCurrencyEntities currContext = new AWCurrencyEntities();

After specifying the object to EntityContainer, it's time to loop through the object set that is composed of "EntityContainer.EntitySet", thus including the name of the EntityContainer object, that represents the "EntityContainer", suffixed with "EntitySet".

Note: The EntityContainer element is named after the database schema, and all "entity sets" that should be logically grouped together are contained within an EntityContainer element. An EntitySet represents the corresponding table in the database. You can explore the names of your EntityModel objects under the ConceptualModel element of the .edmx file, as shown in Figure 1-10.               

foreach (var cr in currContext.Currencies)
{
    txtCurrency .AppendText(cr.ModifiedDate.ToString());
    txtCurrency.AppendText("\t\t");
    txtCurrency.AppendText(cr.CurrencyCode.ToString());                   
    txtCurrency.AppendText("\t\t");
    txtCurrency.AppendText(cr.Name.ToString());
    txtCurrency.AppendText("\t");
    txtCurrency.AppendText("\n");
}

As you can see, the EntityContainer object exposes the columns names through IntelliSense. Or, if you use a . (dot) then you will see all the fields of the "Sales.Currency" table. That is simpler than doing the DataReader's rdr[0], rdr[1] technique, as you experimented with in the previous article. In other words, the Entity Framework has "mapped" each record from the Sales.Currency table into an object. The properties have the same names as the columns of the table, but working with an object fits the object-oriented coding style.

Try It: Schema Abstraction Using an Entity Data Model

In the previous exercise, you created an Entity Data Model named "AWCurrencyModel" (because this is the name of your .edmx file); in this exercise, you will see how this Entity Data Model will help developers do schema abstraction and modify the database without touching the data access code throughout the project or in the Data Access Layer (DAL).  That is, the developer can simply remove the table reference from the model and then add it back. The columns will be realigned, and the code could then be updated to reference the corresponding properties.

1. Start SQL Server Management Studio, expand the "Databases" node, expand the "AdventureWorks database" node, and then expand the "Tables" node. In the list of tables, expand the "Sales.Currency" node and then expand the "Columns" folder.

2. Select the "Name" column, right-click, and select the "Rename" option. Rename the Name column to CurrencyName.

3. Select the "ModifiedDate" column, right-click, and select "Rename" optio. Rename the "ModifiedDate" column to "ModifiedCurrencyDate".

4. So, basically, we added the Currency term in these two columns. Now exit from SQL Server Management Studio by selecting "File" -> "Exit".

5. As you can imagine, our PublishCurrency form and the database have a column name mismatch, so we will now view the exception that the application will report because of this recent column name change. To do so, we will add a TRY…CATCH block to report the issue.

Modify the PublishCurrency.cs code to look as in Listing 1-2.

Listing 1-2. Adding TRY…CATCH to "PublishCurrency.cs" to show exception details

try
   {
   AWCurrencyEntities currContext = new AWCurrencyEntities();          
   foreach (var cr in currContext.Currencies)
   {
         txtCurrency .AppendText(cr.ModifiedDate.ToString()); 
         txtCurrency.AppendText("\t\t");
         txtCurrency.AppendText(cr.CurrencyCode.ToString());                   
         txtCurrency.AppendText("\t\t");
         txtCurrency.AppendText(cr.Name.ToString());
         txtCurrency.AppendText("\t");
         txtCurrency.AppendText("\n");
   }
 }             
 Catch(Exception ex)
{
   MessageBox.Show(ex.Message + ex.StackTrace +
   ex.InnerException);
}

Now, build and run PublishCurrency by pressing Ctrl+F5. The PublishCurrency detail form should load and raise an exception window with the following message: "An error occurred while executing the command definition. See the inner exception for details."

6. If you look at InnerException then you will see a message that indicates the cause of this exception; it's because you have just renamed the Name and ModifiedDate columns of the Sales.Currency table. The exception details should look as in Figure 1-13.

Figure 1-13. Exception details to reflect invalid column name(s) after recent renaming

7. Click "OK" to close the exception window and close the opened form that will be empty because the data did not load due to an exception occurring.

8. Now you will see the advantage of entity data modeling. Assume the same issue occurred in the code you wrote in previous articles; the only solution is to modify the column name in each and every SQL statement that maps to the table we modified. In a real-world scenario, this will not be possible, because database schema update changes are invisible and so the Entity Data Model comes to the rescue.

To fix this application, you need to modify the XML mapping file created by the Entity Data Model, namely, the "AWCurrencyModel.edmx" file you created earlier in the article. To view the XML mapping, navigate to Solution Explorer, right-click "AWCurrencyModel.edmx" and choose "Open With". From the dialog box provided, select "XML (Text) Editor" and click "OK". You will see the XML mapping, as shown previously in Figure 1-10.

Note: In the opened XML mapping file, navigate to the "<!-- SSDL content -->" section and modify the name in the:

<Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />
XML tag to "CurrencyName"; the tag should appear as:
<Property Name="CurrencyName" Type="nvarchar" Nullable="false" MaxLength="50" />

after the modification.

Note: The logical model that represents the database schema, is defined in an XML file using SSDL. This is why you need to modify the column names to map with the database schema.

9. Also, modify the:

<Property Name="ModifiedDate" Type="datetime" Nullable="false" />

XML tag to the ModifiedCurrencyDate:

<Property Name="ModifiedCurrencyDate" Type="datetime" Nullable="false" />

XML tag to appear as:

<Property Name="ModifiedCurrencyDate" Type="datetime" Nullable="false" />

The modified SSDL content section with the CurrencyName and ModifiedCurrencyDate values will look as in Figure 1-14.

Figure 1-14. Modifying the SSDL content section

10. Now look for the "<!-- C-S mapping content -->" section and modify the:

<ScalarProperty Name="Name" ColumnName="Name" />

tag to be:

<ScalarProperty Name="Name" ColumnName="CurrencyName" />

Note: The conceptual model is defined in an XML file using CSDL. CSDL defines the entities and the relationships as the application's business layer know them. This is why you need to modify the column names to be readable and easy to find by the entity.

11. Next, modify the:

<ScalarProperty Name="ModifiedDate" ColumnName="ModifiedDate" />

tag to appear as:

<ScalarProperty Name="ModifiedDate" ColumnName="ModifiedCurrencyDate" />

The modified C-S mapping content section with the CurrencyName and ModifiedCurrencyDate values will look as in Figure 1-15.
 

Figure 1-15. Modifying the C-S mapping content section

12. Now save and build the Article19 solution, and run the application. When the PublishCurrency form is open this should populate the TextBox with the ModifiedDate, Name, and CurrencyCode values, as shown earlier in Figure 1-12.

13. Switch back to the "PublishCurrency.cs" code with the foreach loop, as shown in Listing 19-2. You should still see the same column names being shown in the TextBox with the EntityContainer as "cr.ModifiedDate" and "cr.Name", even though you have modified the column names in the AdventureWorks database's "Sales.Currency" table. But by taking advantage of the schema abstraction feature of the Entity Data Model, you only need to specify the updated column names in the XML mapping file under the SSDL content and C-S mapping content sections.