Mapping Objects to Entities

Once you have completed the Entity Data Model wizard, you have a basic Entity Framework model that enables you to query your database. However, you have definitely not seen all the benefits of using the Entity Framework. Exploring these benefits involves improving the conceptual model to better map to the desired structure.

Simple Mapping

The mapping created above left you with a very thin layer over the database. Each of the generated properties were identical to the field names, and the field names in the AdventureWorks database are not exactly “friendly.” Changing these to create more “Visual Basic–like” property names is a simple matter.

Select the sales order detail table in the model and open the Mapping Details pane of Visual Studio. If it is closed, you can open it by selecting View ⇒ Other Windows ⇒ Entity Data Model Mapping Details.

As shown in the Mapping Details pane in Figure 10.10, the SalesOrderDetail object maps to the SalesOrderDetail table, and each property maps to the field with the same name. By changing the Name property for each field in the Properties pane, you can create a mapping that better explains what some of the fields represent (for example SalesOrder.SalesOrderId to SalesOrder.ID).

Figure 10.10 Entity Mapping

In addition, once you've changed the mapping, the code used to access the types reflects the new mapping (see Figure 10.11):

Figure 10.11 Updated Entity Model

  Sub DisplaySalesOrders()
    Dim ctx As New AWEntities

    Dim orders = From
                   so In ctx.SalesOrders
                 Where
                   so.Status = 5
                 Select
                   so

    For Each order In orders.ToList
      Console.WriteLine("#{0} Ordered On: {1}",
                        order.SalesOrderNumber,
                        order.OrderDate.ToString)
    Next

    Console.WriteLine("{0} orders", orders.Count.ToString)
  End Sub

Recall that the Entity Framework model included a number of navigation properties that represented the relationships between the defined classes, such as the SalesOrder property on the Customer and CreditCard classes. These navigation properties — as their name implies — enable you to navigate between the classes in your queries. For example, you can query and return the sales orders that have a status of 5 with the following query:

  Sub DisplaySalesOrdersInfo()
    Dim ctx As New AWEntities

    Dim orders = From
                   so In ctx.SalesOrders
                 Where
                   so.Status = 5
                 Select New With
                   {
                     .SalesOrderNumber = so.SalesOrderNumber,
                     .CreditCardNumber = so.CreditCard.CardNumber,
                     .AccountNumber = so.Customer.AccountNumber
                   }

    For Each order In orders.ToList
      Console.WriteLine("#{0} CreditCard: {1} Account:{2}",
                        order.SalesOrderNumber,
                        order.CreditCardNumber,
                        order.AccountNumber)
    Next

    Console.WriteLine("{0} orders", orders.Count.ToString)
  End Sub

This query demonstrates the use of projections in a LINQ query. Rather than return the data that is queried, a new object is created using the Select New With {} syntax. This enables you to define a new object to be returned as a result of the query. Each of the properties in the new object are defined by including them in the braces, starting with a dot. In the preceding query, the new object will have three properties: SalesOrderNumber, CreditCardNumber, and AccountNumber, and the values of these properties come from the results of the query. This returned object is an anonymous object. That is, it does not have a usable name within the system (if you look at it in the debugger, it will have a name that has been generated by the compiler). Still, the returned object can be used normally. Because it is a collection, you can iterate over the collection using a For Each loop to display the list (see Figure 10.12).

Figure 10.12 Credit Card Information

Using a Single Table for Multiple Objects

Within your application design, you may have one or more classes that inherit from another. For example, you might have a SalesOrder base class, with online orders that inherit from them. The SalesOrder base class has the standard SalesOrderNumber, OrderDate, and other properties. The OnlineSalesOrder child class might add a property for ShipDate. These types of designs are traditionally very difficult to map to a database. If you were to save this structure to a database, you would have a couple of options. One, you might include all the properties, and add a property to identify the type of the resulting object, as shown in Figure 10.13.

Figure 10.13 OnlineOrderFlag Properties

In this table, the OnlineOrderFlag field is true if the order was made online, and false if not. The identifier will indicate if the order was made online or not. Figure 10.14 shows the desired conceptual model (see the AWModel in the EFSimpleExample project).

Figure 10.14 Entity Model Inheritance

In this model, OnlineSalesOrder inherits from SalesOrder. Notice that the OnlineOrderFlag field is not on the model, and the OnlineSalesOrder has unique properties.

To create this structure, you use the Mapping Details pane of Visual Studio. After you have generated a model based on the SalesOrderHeader table (refer to Figure 10.14), add a new entity that will represent an OnlineSalesOrder. Remove the Id property that is created by default and create a new property called ShipDate. Set the Type property to DateTime. Select the Inheritance item from the Toolbox, and drag an inheritance from OnlineSalesOrder to SalesOrder.

Once the basic model is done, you're ready to add the mapping. Select the SalesOrder entity and delete the ShipDate property by right-clicking on them and selecting Delete. This will remove the mapping of the property to the SalesOrder object.

Select the OnlineSalesOrder entity. Select the SalesOrderHeader table under the Tables collection. Map the ShipDate field to the ShipDate property. Notice that above the field mapping is a Condition mapping. This is how you will distinguish sales orders from online sales orders. Select the OnlineOrderFlag field, and set the Condition's value to true (see Figure 10.15).

Figure 10.15 SalesOrderHeader Condition for OnlineOrderFlag

Do a similar mapping for the SalesOrder entity, but set the Condition to select the OnlineOrderFlag=false records. You should now be able to validate the model by right-clicking the designer and selecting Validate.

You can now work with the table as you would expect from the model. For example, you can create a new OnlineSalesOrder with the following code:

  Sub CreateOnlineSalesOrder()
    Dim ctx As New AWEntities

    Dim order As New OnlineSalesOrder
    With order
      .RevisionNumber = 1
      .OrderDate = DateTime.Now
      .DueDate = DateTime.Now
      .Status = 5
      .SalesOrderNumber = "1234567890"
      .CustomerID = 1
      .ContactID = 1
      .BillToAddressID = 1
      .ShipDate=DateTime.Now.Add(New TimeSpan(2,0,0,0)
      .ShipToAddressID = 1
      .SubTotal = 100
      .ShipMethodID = 1
      .TaxAmt = 7.25
      .Freight = 0
      .TotalDue = 107.25
      .ModifiedDate = DateTime.Now
    End With

    ctx.SalesOrders.AddObject(order)

    ctx.SaveChanges(Objects.SaveOptions.AcceptAllChangesAfterSave)

    Console.WriteLine("Online sales order created.")

  End Sub

Here, you create a new sales order, assign some values to the properties, and save. Notice that you do not directly set the OnlineSalesFlag field. Instead, it is set based on whether you create either a SalesOrder or an OnlineSalesOrder. Figure 10.16 shows the newly added record in the SalesOrder table.

Figure 10.16 OnlineSalesOrder Data

Selecting records when using these types of models can be slightly confusing the first few times, as you will find that the context does not have an OnlineSalesOrder collection on them. If you look at the properties for these entities, it makes more sense; you will see that the EntitySetName property is SalesOrder (how they were defined in the EntitySetName for the SalesOrder collection). Therefore, you still query them as sales order, but you add an additional qualifier to the query to select for the desired child class:

  Sub DisplayOnlineSalesOrders()
    Dim ctx As New AWEntities

    Dim orders = From
                   so In ctx.SalesOrders.OfType(Of OnlineSalesOrder)()
                 Where
                   so.Status = 5
                 Order By
                   so.OrderDate
                 Select New With
                   {
                     .SalesOrderNumber = so.SalesOrderNumber,
                     .CreditCardNumber = so.CreditCard.CardNumber,
                     .AccountNumber = so.Customer.AccountNumber
                   }

    For Each order In orders.ToList
      Console.WriteLine("#{0} CreditCard: {1} Account:{2}",
                        order.SalesOrderNumber,
                        order.CreditCardNumber,
                        order.AccountNumber)
    Next

    Console.WriteLine("{0} orders", orders.Count.ToString)
  End Sub

In this code, the OfType(Of OnlineSalesOrder) clause defines the type you are retrieving. The OfType clause limits the returned records to just those with the correct value in the Condition mappings you created.

Updating the Model

Eventually, you will have to make changes to your model, due to changes to the database or new requirements for your model. You can add new tables, change the data types of columns easily with the Entity Framework designer. You can update your model by right-clicking on the designer and selecting Update Model from Database. This will start up the Update Wizard (see Figure 10.17).

Figure 10.17 Data Model Update Wizard

This dialog has three tabs, depending on what you'd like to update from your database. The Add and Delete tabs enable you to identify database items you'd like to add or delete from your model (tables, views or stored procedures, and function). The Refresh tab enables you to identify structures that may have changed in the database. The wizard will update the corresponding entities in your model.