Entity Framework 6 Recipes 2nd Edition (10-10), entityrecipes

Entity Framework 6 Recipes 2nd Edition (10-10), entityrecipes

10-10. The insert, update, and delete operations inherited by TPH are mapped to the stored procedure.

Problem

TPH inherits the model and maps its insert, modify, and delete operations to the stored procedure.

Solution

Assume that the database has a Product table describing different types (see Figure 10-13 ). A derived model is created for each product in the table, as shown in Figure 10-14.

 

Figure 10-13.A column containing the Authentication (ProductType)Product table,Each row of the table is divided by the value of this column into different products.

 

Figure 10-14. TPHInheritance model

Next, we map the insert, update, and delete operations of this model to the stored procedure:

1. Create the stored procedures shown in Listing 10-26 in the database. These stored procedures process the insert, update, and delete operations for the Book and DVD entities.

Listing 10-26.The Stored Procedure We Map to the Insert, Update, and Delete Actions for the Model

Create procedure [chapter10]. [InsertBook] (@ Title varchar (50), @ Publisher varchar (50 ))

As

Begin

Insert into Chapter10.Product (Title, Publisher, ProductType) values (@ Title, @ Publisher, 'book ')

Select SCOPE_IDENTITY () as ProductId

End

Go

 

Create procedure [chapter10]. [UpdateBook] (@ Title varchar (50), @ Publisher varchar (50), @ ProductId int)

As

Begin

Update Chapter10.Product set Title = @ Title, Publisher = @ Publisher where ProductId = @ ProductId

End

Go

 

Create procedure [chapter10]. [DeleteBook] (@ ProductId int)

As

Begin

Delete from Chapter10.Product where ProductId = @ ProductId

End

Go

 

Create procedure [chapter10]. [InsertDVD] (@ Title varchar (50), @ Rating varchar (50 ))

As

Begin

Insert into Chapter10.Product (Title, Rating, ProductType) values (@ Title, @ Rating, 'dvd ')

Select SCOPE_IDENTITY () as ProductId

End

Go

 

Create procedure [chapter10]. [DeleteDVD] (@ ProductId int)

As

Begin

Delete from Chapter10.Product where ProductId = @ ProductId

End

Go

 

Create procedure [chapter10]. [UpdateDVD] (@ Title varchar (50), @ Rating varchar (50), @ ProductId int)

As

Begin

Update Chapter10.Product set Title = @ Title, Rating = @ Rating where ProductId = @ ProductId

End

2. Right-click the model design view and select "update model from database. Select the new stored procedure and click" finish "to complete the update.

3. Right-click the Book object and select "Stored Procedure ing". Map InsertBook, UpdateBook, and DeleteBook stored procedures to the corresponding operations. Bind the ProductId column for The insert operation (see Figure 10-15 ).

 

Figure 10-15.Insert, update, and delete operations from the Book ing process to the Book object.Pay special attention to binding the result column of the insert operation to ProductId.

4. Right-click the DVD object and select "Stored Procedure ing" to map InsertBook, UpdateBook, and DeleteBook stored procedures to the corresponding operations. Bind the ProductId column for The insert operation (see Figure 10-16 ).

 

How does it work?

We created a stored procedure for the insert, update, and delete operations on the Book and DVD entities and introduced it to the model. after the introduction, we map them to the corresponding operations of the corresponding entities. Note that the result columns of the two entities must be bound to the ProductId attribute, this ensures that the value of the ProductId column automatically created by the product returned by the stored procedure is mapped to the ProductId attribute of the object.

TPH inheritance can insert the ProductType value into the table by executing the inserted stored procedure. EF can properly convert the derived entity according to the ProductType value.

The following Listing 10-27 Code demonstrates insertion, update, deletion, and query.

Listing 10-27.Exercising the Insert, Update, and Delete Actions

Class Program

{

Static void Main (string [] args)

{

Using (var context = new EFRecipesEntities1010 ())

{

Var book1 = new Book

{

Title = "A Day in the Life ",

Publisher = "Colorful Press"

};

Var book2 = new Book

{

Title = "Spring in October ",

Publisher = "AnimalCover Press"

};

 

Var dvd1 = new DVD {Title = "Saving Sergeant Pepper", Rating = "G "};

Var dvd2 = new DVD {Title = "Around The Block", Rating = "PG-13 "};

Context. Products. Add (book1 );

Context. Products. Add (book2 );

Context. Products. Add (dvd1 );

Context. Products. Add (dvd2 );

Context. SaveChanges ();

// Update a book and delete a dvd

Book1.Title = "A Day in the Life of Sergeant Pepper ";

Context. Products. Remove (dvd2 );

Context. SaveChanges ();

}

Using (var context = new EFRecipesEntities1010 ())

{

Console. WriteLine ("All Products ");

Console. WriteLine ("============== ");

Foreach (var product in context. Products)

{

If (product is Book)

Console. WriteLine ("'{0}' published by {1 }",

Product. Title, (Book) product). Publisher );

Else if (product is DVD)

Console. WriteLine ("'{0}' is rated {1 }",

Product. Title, (DVD) product). Rating );

}

}

Console. WriteLine ("\ npress any key to exit ...");

Console. ReadKey ();

 

}

}

The output result is as follows:

 

All Products

================

'Spring in october' published by AnimalCover Press

'A Day in the Life of Sergeant Pepper 'published by Colorful Press

'Saving Sergeant Pepper 'is rated G

 

 

Appendix: script file of the database used in the Creation example