You’ll start with an existing solution that already defines the main projects of the PMN application. If you installed the companion content at the default location, you’ll find the solution at this location: %userprofile%DocumentsMicrosoft PressMoving to Visual Studio 2010Chapter 8CodeExistingDatabase. Double-click the PlanMyNight.sln file.

This solution includes all the projects in the following list, as shown in Figure 8-5:

Figure 8-5. PlanMyNight solution

The EF allows you to easily import an existing database. Let’s walk through this process.

The first step is to add an EDM to the PlanMyNight.Data project. Right-click the PlanMyNight.Data project, select Add, and then choose New Item. Select the ADO.NET Entity Data Model item, and change its name to PlanMyNight.edmx, as shown in Figure 8-6.

Figure 8-6. Add New Item dialog with ADO.NET Entity Data Model selected

The first dialog of the Entity Data Model Wizard allows you to choose the model content. You’ll generate the model from an existing database. Select Generate From Database and then click Next.

You need to connect to an existing database file. Click New Connection. Select Microsoft SQL Server Database File from the Choose Data Source dialog, and click Continue. Select the %userprofile%DocumentsMicrosoft PressMoving to Visual Studio 2010Chapter 8ExistingDatabasePlanMyNight.WebApp_DataPlanMyNight.mdf file. (See Figure 8-7.)

Figure 8-7. EDM Wizard database connection

Leave the other fields in the form as is for now and click Next.

From the Choose Your Database Objects dialog, select the Itinerary, ItineraryActivities, ItineraryComment, ItineraryRating, and ZipCode tables and the UserProfile view. Select the RetrieveItinerariesWithinArea stored procedure. Change the Model Namespace value to Entities as shown in Figure 8-8.

Figure 8-8. EDM Wizard: Choose Your Database Objects page

Click Finish to generate your EDM.

Fixing the Generated Data Model

You now have a model representing a set of entities matching your database. The wizard has generated all the navigation properties associated with the foreign keys from the database.

The PMN application requires only the navigation property ItineraryActivities from the Itinerary table, so you can go ahead and delete all the other navigation properties. You’ll also need to rename the ItineraryActivities navigation property to Activities. Refer to Figure 8-9 for the updated model.

Figure 8-9. Model imported from the PlanMyNight database

Notice that one of the properties of the ZipCode entity has been generated with the name ZipCode1 because the table itself is already named ZipCode and the name has to be unique. Let’s fix the property name by double-clicking it. Change the name to Code, as shown in Figure 8-10.

Figure 8-10. ZipCode entity

Build the solution by pressing Ctrl+Shift+B. When looking at the output window, you’ll notice two messages from the generated EDM. You can discard the first one because the Location column is not required in PMN. The second message reads as follows:

The table/view ‘dbo.UserProfile’ does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it.

When looking at the UserProfile view, you’ll notice it does not explicitly define a primary key even though the UserName column is unique.

You need to modify the EDM manually to fix the UserProfile view mapping so that you can access the UserProfile data from the application.

From the project explorer, right-click the PlanMyNight.edmx file and then select Open With Choose XML (Text) Editor from the Open With dialog as shown in Figure 8-11. Click OK to open the XML file associated with your model.

Figure 8-11. Open PlanMyNight.edmx in the XML Editor

Note

You’ll get a warning stating that the PlanMyNight.edmx file is already open. Click Yes to close it.

The generated code was commented out by the code-generation tool because there was no primary key defined. To be able to use the UserProfile view from the designer, you need to uncomment the UserProfile entity type and add the Key tag to it. Search for UserProfile in the file. Uncomment the entity type, add a Key tag and set its name to UserName and make the UserName property not nullable. Refer to Example 8-1 to see the updated entity type.

Example 8-1. UserProfile Entity Type XML Definition

<EntityType Name="UserProfile">
  <Key>
    <PropertyRef Name="UserName"/>
  </Key>
  <Property Name="UserName" Type="uniqueidentifier" Nullable="false" />
  <Property Name="FullName" Type="varchar" MaxLength="500" />
  <Property Name="City" Type="varchar" MaxLength="500" />
  <Property Name="State" Type="varchar" MaxLength="500" />
  <Property Name="PreferredActivityTypeId" Type="int" />
</EntityType>

If you close the XML file and try to open the EDM Designer, you’ll get the following error message in the designer: “The Entity Data Model Designer is unable to display the file you requested. You can edit the model using the XML Editor.”

There is a warning in the Error List pane that can give you a little more insight into what this error is all about:

Error 11002: Entity type ‘UserProfile’ has no entity set.

You need to define an entity set for the UserProfile type so that it can map the entity type to the store schema. Open the PlanMyNight.edmx file in the XML editor so that you can define an entity set for UserProfile. At the top of the file, just above the Itinerary entity set, add the XML code shown in Example 8-2.

Example 8-2. UserProfile EntitySet XML Definition

  <EntitySet Name="UserProfile" EntityType="Entities.Store.UserProfile"
    store:Type="Views" store:Schema="dbo" store:Name="UserProfile">
  <DefiningQuery>
      SELECT
     [UserProfile].[UserName] AS [UserName],
     [UserProfile].[FullName] AS [FullName],
            [UserProfile].[City] AS [City],
         [UserProfile].[State] AS [State],
         [UserProfile].[PreferredActivityTypeId] as [PreferredActivityTypeId]
         FROM [dbo].[UserProfile] AS [UserProfile]
  </DefiningQuery>
</EntitySet>

Save the EDM XML file, and reopen the EDM Designer. Figure 8-12 shows the UserProfile view in the Entities.Store section of the Model Browser.

Tip

You can open the Model Browser from the View menu by clicking Other Windows and selecting the Entity Data Model Browser item.

Figure 8-12. Model Browser with the UserProfile view

Now that the view is available in the store metadata, you add the UserProfile entity and map it to the UserProfile view. Right-click in the background of the EDM Designer, select Add, and then choose Entity. You’ll see the dialog shown in Figure 8-13.

Figure 8-13. Add Entity dialog

Complete the dialog as shown in Figure 8-13, and click OK to generate the entity.

You need to add the remaining properties: City, State, and PreferredActivityTypeId. To do so, right-click the UserProfile entity, select Add, and then select Scalar Property. After the property is added, set the Type, Max Length, and Unicode field values. Table 8-1 shows the expected values for each of the fields.

Table 8-1. UserProfile Entity Properties

Name	Type	Max Length	Unicode
FullName	String	500	False
City	String	500	False
State	String	500	False
PreferredActivityTypeId	Int32	NA	NA

Now that you have created the UserProfile entity, you need to map it to the UserProfile view. Right-click the UserProfile entity, and select Table Mapping as shown in Figure 8-14.

Figure 8-14. Table Mapping menu item

Then select the UserProfile view from the drop-down box as shown in Figure 8-15. Ensure that all the columns are correctly mapped to the entity properties. The UserProfile view of our store is now accessible from the code through the UserProfile entity.

Figure 8-15. UserProfile mapping details

Stored Procedure and Function Imports

The Entity Data Model Wizard has created an entry in the storage model for the RetrieveItinerariesWithinArea stored procedure you selected in the last step of the wizard. You need to create a corresponding entry to the conceptual model by adding a Function Import entry.

From the Model Browser, open the Stored Procedures folder in the Entities.Store section. Right-click RetrieveItineraryWithinArea, and then select Add Function Import. The Add Function Import dialog appears as shown in Figure 8-16. Specify the return type by selecting Entities and then select the Itinerary item from the drop-down box. Click OK.

Figure 8-16. Add Function Import dialog

The RetrieveItinerariesWithinArea function import was added to the Model Browser as shown in Figure 8-17.

Figure 8-17. Function Imports in the Model Browser

You can now validate the EDM by right-clicking on the design surface and selecting Validate. There should be no error or warning.

In the prior section, you saw how to use the EF designer to generate the model by importing an existing database. The EF designer in Visual Studio 2010 also supports the ability to generate the Data Definition Language (DDL) file that will allow you to create a database based on your entity model. In this section, you’ll use a new solution to learn how to generate a database script from a model.

You can start from an empty model by selecting the Empty model option from the Entity Data Model Wizard. (See Figure 8-18.)

Figure 8-18. EDM Wizard: Empty model

Open the PMN solution at %userprofile%DocumentsMicrosoft PressMoving to Visual Studio 2010Chapter 8CodeModelFirst by double-clicking the PlanMyNight.sln file.

The PlanMyNight.Data project from this solution already contains an EDM file named PlanMyNight.edmx with some entities already created. These entities match the data schema you saw in Figure 8-2.

The Entity Model designer lets you easily add an entity to your data model. Let’s add the missing ZipCode entity to the model. From the toolbox, drag an Entity item into the designer, as shown in Figure 8-19. Rename the entity as ZipCode. Rename the Id property as Code, and change its type to String.

Figure 8-19. Entity Model designer

You need to add the City and State properties to the entity. Right-click the ZipCode entity, select Add, and then choose Scalar Property. Ensure that each property has the values shown in Table 8-2.

Add the relations between the ItineraryComment and Itinerary entities. Right-click the designer background, select Add, and then choose Association. (See Figure 8-20.)

Figure 8-20. Add Association dialog for FK_ItineraryCommentItinerary

Set the association name to FK_ItineraryCommentItinerary, and then select the entity and the multiplicity for each end, as shown in Figure 8-20. After the association is created, double-click the association line to set the Referential Constraint as shown in Figure 8-21.

Figure 8-21. Association Referential Constraint dialog

Add the association between the ItineraryRating and Itinerary entities. Right-click the designer background, select Add, and then choose Association. Set the association name to FK_ItineraryItineraryRating and then select the entity and the multiplicity for each end as in the previous step, except set the first end to ItineraryRating. Double-click on the association line, and set the Referential Constraint as shown in Figure 8-21. Note that the Dependent field will read ItineraryRating instead of ItineraryComment. Create a new association between the ItineraryActivity and Itinerary entities. For the FK_ItineraryItineraryActivity association, you want to also create a navigation property and name it Activities, as shown in Figure 8-22. After the association is created, set the Referential Constraint for this association by double-clicking on the association line.

Figure 8-22. Add Association dialog for FK_ItineraryActivityItinerary

Generating the Database Script from the Model

Your data model is now completed but there is no mapping or store associated with it. The EF designer offers the possibility of generating a database script from our model.

Right-click on the designer surface, and choose Generate Database From Model as shown in Figure 8-23.

Figure 8-23. Generate Database From Model menu item

The Generate Database Wizard requires a data connection. The wizard uses the connection information to translate the model types to the database type and to generate a DDL script targeting this database.

Select New Connection, select Microsoft SQL Server Database File from the Choose Data Source dialog, and click Continue. Select the database file located at %userprofile%DocumentsMicrosoft PressMoving to Visual Studio 2010Chapter 8CodeModelFirstDataPlanMyNight.mdf. (See Figure 8-24.)

Figure 8-24. Generate a script database connection

After your connection is configured, click Next to get to the final page of the wizard, as shown in Figure 8-25. When you click Finish, the generated T-SQL PlanMyNight.edmx.sql file is added to your project. The DDL script will generate the primary and foreign key constraints for your model.

Figure 8-25. Generated T-SQL file

The EDM is also updated to ensure your newly created store is mapped to the entities. You can now use the generated DDL script to add the tables to the database. Also, you now have a data layer that exposes strongly typed entities that you can use in your application.

Important

Generating the complete PMN database would require adding the remaining tables, stored procedures, and triggers used by the application. Instead of performing all these operations, we will go back to the solution we had at the end of the EF: Importing an Existing Database section.

The EDM Designer uses T4 templates to generate the code for the entities. So far, we have let the designer create the entities using the default templates. You can take a look at the code generated by opening the PlanMyNight.Designer.cs file associated with PlanMyNight.edmx. The generated entities are based on the EntityObject type and decorated with attributes to allow the EF to manage them at run time.

The EF also supports POCO entity types. POCO classes are simple objects with no attributes or base class related to the framework. (Example 8-3, in the next section, shows the POCO class for the ZipCode entity.) The EF uses the names of the types and the properties of these objects to map them to the model at run time.

ADO.NET POCO Entity Generator

Let’s re-open the %userprofile%DocumentsMicrosoft PressMoving to Visual Studio 2010Chapter 8CodeExistingDatabasePlanMyNight.sln file.

Open the PlanMyNight.edmx file, right-click on the design surface, and choose Add Code Generation Item. This opens a dialog like the one shown in Figure 8-26, where you can select the template you want to use. Select the ADO.NET POCO Entity Generator template, and name it PlanMyNight.tt. Then click the Add button.

Note

You might get a security warning about running this text template. Click OK to close the dialog because the source for this template is trusted.

Figure 8-26. Add New Item dialog

Two files, PlanMyNight.tt and PlanMyNight.Context.tt, have been added to your project, as shown in Figure 8-27. These files replace the default code-generation template, and the code is no longer generated in the PlanMyNight.Designer.cs file.

Figure 8-27. Added templates

The PlanMyNight.tt template produces a class file for each entity in the model. Example 8-3 shows the POCO version of the ZipCode class.

Example 8-3. POCO Version of the ZipCode Class

namespace Microsoft.Samples.PlanMyNight.Data
{
    public partial class ZipCode
    {
        #region Primitive Properties
        public virtual string Code
        {
            get;
            set;
        }
        public virtual string City
        {
            get;
            set;
        }
        public virtual string State
        {
            get;
            set;
        }
        #endregion
    }
}

The other file, PlanMyNight.Context.cs, generates the ObjectContext object for the PlanMyNight.edmx model. This is the object you’ll use to interact with the database.

Tip

The POCO templates will automatically update the generated classes to reflect the changes to your model when you save the .edmx file.

Moving the Entity Classes to the Contracts Project

We have designed the PMN application architecture to ensure that the presentation layer was persistence ignorant by moving the contracts and entity classes to an assembly that has no reference to the storage.

Visual Studio 2008

Even though it was possible to extend the XSD processing with code-generator tools, it was not easy and you had to maintain these tools. The EF uses T4 templates to generate both the database schema and the code. These templates can easily be customized to your needs.

The ADO.NET POCO templates split the generation of the entity classes into a separate template, allowing you to easily move these entities to a different project.

You are going to move the PlanMyNight.tt file to the PlanMyNight.Contracts project. Right-click the PlanMyNight.tt file, and select Cut. Right-click the Entities folder in the PlanMyNight.Contracts project, and select Paste. The result is shown in Figure 8-28.

Figure 8-28. POCO template moved to the Contracts project

The PlanMyNight.tt template relies on the metadata from the EDM model to generate the entity type’s code. You need to fix the relative path used by the template to access the EDMX file.

Open the PlanMyNight.tt template and locate the following line:

string inputFile = @"PlanMyNight.edmx";

Fix the file location so that it points to the PlanMyNight.edmx file in the PlanMyNight.Data project:

string inputFile = @"....PlanMyNight.DataPlanMyNight.edmx";

The entity classes are regenerated when you save the template.

You also need to update the PlanMyNight.Context.tt template in the PlanMyNight.Contracts project because the entity classes are now in the Microsoft.Samples.PlanMyNight.Entities namespace instead of the Microsoft.Samples.PlanMyNight.Data namespace. Open the PlanMyNight.Context.tt file, and update the using section to include the new namespace:

using System;
using System.Data.Objects;
using System.Data.EntityClient;
using Microsoft.Samples.PlanMyNight.Entities;

Build the solution by pressing Ctrl+Shift+B. The project should now compile successfully.

At the beginning of this chapter, we decided to group the operations on the Itinerary entity in the IItinerariesRepository repository interface. Some of these operations are

Let’s take a look at the corresponding methods in the IItinerariesRepository interface:

Open the PMN solution at %userprofile%DocumentsMicrosoft PressMoving to Visual Studio 2010Chapter 8CodeFinal by double-clicking the PlanMyNight.sln file.

Select the PlanMyNight.Data project, and open the ItinerariesRepository.cs file. This is the IItinerariesRepository interface implementation. Using the PlanMyNightEntities Object Context you generated earlier, you can write LINQ queries against your model, and the EF will translate these queries to native T-SQL that will be executed against the database.

Navigate to the SearchByActivity function definition. This method must return a set of itineraries where the IsPublic flag is set to true and where one of their activities has the same activityId that was passed in the argument to the function. You also need to order the result itinerary list by the rating field.

Using standard LINQ operators, you can implement SearchByActivity as shown in Example 8-4. Add the highlighted code to the SearchByActivity method body.

public PagingResult<Itinerary> SearchByActivity(string activityId, int pageSize, int
pageNumber)
{
  using (var ctx = new PlanMyNightEntities())
  {
    ctx.ContextOptions.ProxyCreationEnabled = false;

    var query = from itinerary in ctx.Itineraries.Include("Activities")
                where itinerary.Activities.Any(t => t.ActivityId == activityId)
                      && itinerary.IsPublic
                orderby itinerary.Rating
                select itinerary;

    return PageResults(query, pageNumber, pageSize);
  }
}

private static PagingResult<Itinerary> PageResults(IQueryable<Itinerary> query, int
page, int pageSize)
{
  int rowCount = rowCount = query.Count();
  if (pageSize > 0)
  {
    query = query.Skip((page - 1) * pageSize)
                             .Take(pageSize);
  }
  var result = new PagingResult<Itinerary>(query.ToArray())
  {
    PageSize = pageSize,
    CurrentPage = page,
    TotalItems = rowCount
  };
  return result;
}

The SearchByZipCode function method is similar to the SearchByActivity method, but it also adds a filter on the Zip Code of the activity. Here again, LINQ support makes it easy to implement, as shown in Example 8-5. Add the highlighted code to the SearchByZipCode method body.

public PagingResult<Itinerary> SearchByZipCode(int activityTypeId, string zip, int
pageSize, int pageNumber)
{
  using (var ctx = new PlanMyNightEntities())
  {
    ctx.ContextOptions.ProxyCreationEnabled = false;

    var query = from itinerary in ctx.Itineraries.Include("Activities")
                where itinerary.Activities.Any(t => t.TypeId == activityTypeId &&
t.Zip == zip)
                      && itinerary.IsPublic
                 orderby itinerary.Rating
                 select itinerary;

     return PageResults(query, pageNumber, pageSize);
  }
}

The SearchByRadius function calls the RetrieveItinerariesWithinArea import function that was mapped to a stored procedure. It then loads the activities for each itinerary found. You can copy the highlighted code in Example 8-6 to the SearchByRadius method body in the ItinerariesRepository.cs file.

public PagingResult<Itinerary> SearchByRadius(int activityTypeId,
    double longitude, double latitude, double radius,
    int pageSize, int pageNumber)
{
  using (var ctx = new PlanMyNightEntities())
  {
    ctx.ContextOptions.ProxyCreationEnabled = false;

    // Stored Procedure with output parameter
    var totalOutput = new ObjectParameter("total", typeof(int));
    var items = ctx.RetrieveItinerariesWithinArea(activityTypeId, latitude, longitude,
radius, pageSize, pageNumber, totalOutput).ToArray();

    foreach (var item in items)
    {
      item.Activities.ToList().AddRange(this.Retrieve(item.Id).Activities);
    }

    int total = totalOutput.Value == DBNull.Value ? 0 : (int)totalOutput.Value;

    return new PagingResult<Itinerary>(items)
    {
      TotalItems = total,
      PageSize = pageSize,
      CurrentPage = pageNumber
    };
  }
}

The Add method allows you to add Itinerary to the data store. Implementing this functionality becomes trivial because your contract and context object use the same entity object. Copy and paste the highlighted code in Example 8-7 to the Add method body.

public void Add(Itinerary itinerary)
{
  using (var ctx = new PlanMyNightEntities())
  {
    ctx.Itineraries.AddObject(itinerary);
    ctx.SaveChanges();
  }
}

There you have it! You have completed the ItinerariesRepository implementation using the context object generated using the EF designer. Run all the tests in the solution by pressing Ctrl+R, A. The tests related to the ItinerariesRepository implementation should all succeed.

With the advances in multicore computing, it is becoming more and more important for developers to be able to write parallel applications. Visual Studio 2010 and the .NET Framework 4.0 provide new ways to express concurrency in applications. The Task Parallel Library (TPL) is now part of the Base Class Library (BCL) for the .NET Framework. This means that every .NET application can now access the TPL without adding any assembly reference.

PMN stores only the Bing Activity ID for each ItineraryActivity to the database. When it’s time to retrieve the entire Bing Activity object, a function that iterates through each of the ItineraryActivity instances for the current Itinerary is used to populate the Bing Activity entity from the Bing Maps Web services.

One way of performing this operation is to sequentially call the service for each activity in the Itinerary as shown in Example 8-8. This function waits for each call to RetrieveActivity to complete before making another call, which has the effect of making its execution time linear.

public void PopulateItineraryActivities(Itinerary itinerary)
{
  foreach (var item in itinerary.Activities.Where(i => i.Activity == null))
  {
    item.Activity = this.RetrieveActivity(item.ActivityId);
  }
}

In the past, if you wanted to parallelize this task, you had to use threads and then hand off work to them. With the TPL, all you have to do now is use a Parallel.ForEach that will take care of the threading for you as seen in Example 8-9.

public void PopulateItineraryActivities(Itinerary itinerary)
{
  Parallel.ForEach(itinerary.Activities.Where(i => i.Activity == null),
    item =>
    {
      item.Activity = this.RetrieveActivity(item.ActivityId);
    });
}

PMN is a data-driven application that gets its data from the application database and the Bing Maps Web services. One of the challenges you might face when building a Web application is managing the needs of a large number of users, including performance and response time. The operations that use the data store and the services used to search for activities can increase the usage of server resources dramatically for items that are shared across many users. For example, many users have access to the public itineraries, so displaying these will generate numerous calls to the database for the same items. Implementing caching at the Web tier will help reduce usage of the resources at the data store and help mitigate latency for recurring searches to the Bing Maps Web services. Figure 8-29 shows the architecture for an application implementing a caching solution at the front-end server.

Figure 8-29. Typical Web application architecture

Using this approach reduces the pressure on the data layer, but the caching is still coupled to a specific server serving the request. Each Web tier server will have its own cache, but you can still end up with an uneven distribution of the processing to these servers.

Windows Server AppFabric caching offers a distributed, in-memory cache platform. The AppFabric client library allows the application to access the cache as a unified view event if the cache is distributed across multiple computers, as shown in Figure 8-30. The API provides simple get and set methods to retrieve and store any serializable common language runtime (CLR) objects easily. The AppFabric cache allows you to add a cache computer on demand, thus making it possible to scale in a manner that is transparent to the client. Another benefit is that the cache can also share copies of the data across the cluster, thereby protecting data against failure.

Figure 8-30. Web application using Windows Server AppFabric caching