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.
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).
In addition, once you've changed the mapping, the code used to access the types reflects the new mapping (see Figure 10.11):
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).
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.
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).
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).
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.
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.
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).
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.