barcodelib c# ADVANCED MODELING in Font

Encoder Quick Response Code in Font ADVANCED MODELING

Right-click the design surface and select Add Association. Select a multiplicity of one on the Customer side and a multiplicity of many on the Order side. Rename the Order navigation property to Orders because it refers to a set of orders. Uncheck the Add foreign key properties to the Order entity check box. Right-click the new association and view its properties. Click in the Referential Constraint box. In the dialog box, set the Principal to Customer and the Dependent Property to CustomerId. See Figure 15-22. Select the Order entity and view the Mapping Details window. In the Mapping Details window click the Map Entities to Functions button (second button on the left). Map the Order Insert, Update, and Delete stored procedures to the Insert, Update, and Delete actions. Figures 15-23 and 15-24 show the mappings for the parameters and returned values for the actions.

Listing 15-23. View that combines FirstName and LastName and computes the total orders create view chapter15.vwCustomer as select c.*,c.FirstName + ' ' + c.LastName as FullName, (select COUNT(*) from chapter15.[Order] where CustomerId = c.CustomerId) TotalOrders from chapter15.Customer c go create view chapter15.vwOrder as select o.*,os.Description OrderStatus,s.Description ShippingType from chapter15.[Order] o join chapter15.OrderStatusType os on os.OrderStatusTypeId = o.OrderStatusTypeId join chapter15.ShippingType s on s.ShippingTypeId = o.OrderStatusTypeId Listing 15-24. Stored procedure implementations for the Insert, Update, and Delete actions for the Customer and Order entities create procedure chapter15.InsertCustomer (@FirstName varchar(50), @LastName varchar(50), @FullName varchar(50)) as begin insert into chapter15.Customer(FirstName,LastName) values (@FirstName,@LastName) select SCOPE_IDENTITY() CustomerId end go create procedure chapter15.UpdateCustomer (@FirstName varchar(50), @LastName varchar(50), @FullName varchar(50), @CustomerId int) as

begin update chapter15.Customer set FirstName = @FirstName, LastName = @LastName where CustomerId = @CustomerId end go create procedure chapter15.DeleteCustomer (@CustomerId int) as begin delete chapter15.Customer where CustomerId = @CustomerId end go create procedure chapter15.InsertOrder (@OrderDate date, @CustomerId int, @OrderStatusTypeId int, @ShippingTypeId int, @OrderStatus varchar(50), @ShippingType varchar(50)) as begin insert into chapter15.[Order](OrderDate,CustomerId,OrderStatusTypeId,ShippingTypeId) values (@OrderDate,@CustomerId,@OrderStatusTypeId,@ShippingTypeId) select SCOPE_IDENTITY() OrderId end go create procedure chapter15.UpdateOrder (@OrderId int, @OrderDate date, @CustomerId int, @OrderStatusTypeId int, @ShippingTypeId int, @OrderStatus varchar(50), @ShippingType varchar(50)) as begin update chapter15.[Order] set OrderDate = @OrderDate, CustomerId = @CustomerId, OrderStatusTypeId = @OrderStatusTypeId, ShippingTypeId = @ShippingTypeId where OrderId = @OrderId end

go create procedure chapter15.DeleteOrder (@OrderId int, @CustomerId int) as begin delete chapter15.[Order] where OrderId = @OrderId end go

Figure 15-22. Setting the referential constraint for the one-to-many association between the Customer and Order entities

Figure 15-23. Mappings for the parameters and returned values for the Insert, Update, and Delete actions for the Customer entity

Figure 15-24. Mappings for the parameters and returned values for the Insert, Update, and Delete actions for the Order entity The completed model is shown in Figure 15-25.

To expose the computed column TotalOrders and to combine the shipping type and order status descriptions from the lookup tables we created two views and built our model around these views. When we use views in this way, we are required to provide the Insert, Update, and Delete actions. Entity Framework has no way of automating these CRUD operations on views. The stored procedures in Listing 15-24 provide an implementation of these actions. In the mapping of the parameters and returned values from these stored procedures, it is important to note that the returned value of the procedure mapped to the insert action must be the key for the entity. In our case, these keys were CustomerId and OrderId. Failing to map these returned values will result in a runtime error. When we imported the views, Entity Framework marked each of the properties as part of the key for the entity. Of course, Entity Framework has no way of knowing which, if any, of the columns from the view are part of the key. As it does with tables that have no primary key constraint, it simply combines all the columns into the entity key. We removed all but the Id properties from the keys both on the design surface and in the .edmx file. The code in Listing 15-25 demonstrates inserting into and retrieving from our model. We use code not shown in this Listing to populate the lookup tables. Listing 15-25. Inserting into and retrieving from our model using (var context = new EFRecipesEntities()) { // insert our lookup values context.ExecuteStoreCommand(@"insert into chapter15 .orderstatustype(OrderStatusTypeId, Description) values (1,'Processing')"); context.ExecuteStoreCommand(@"insert into chapter15 .orderstatustype(OrderStatusTypeId, Description) values (2,'Shipped')"); context.ExecuteStoreCommand(@"insert into chapter15 .shippingtype(ShippingTypeId, Description) values (1,'UPS')"); context.ExecuteStoreCommand(@"insert into chapter15 .shippingtype(ShippingTypeId, Description) values (2,'FedEx')"); } using (var context = new EFRecipesEntities()) { var c1 = new Customer { FirstName = "Robert", LastName = "Jones" }; var o1 = new Order { OrderDate = DateTime.Parse("11/19/2009"), OrderStatusTypeId = 2, ShippingTypeId = 1, Customer = c1 }; var o2 = new Order { OrderDate = DateTime.Parse("12/13/09"), OrderStatusTypeId = 1, ShippingTypeId = 1, Customer = c1 }; var c2 = new Customer { FirstName = "Julia", LastName = "Stevens" }; var o3 = new Order { OrderDate = DateTime.Parse("10/19/09"), OrderStatusTypeId = 2, ShippingTypeId = 2, Customer = c2 }; context.Customers.AddObject(c1); context.Customers.AddObject(c2);