Entity Framework 6 Recipes 2nd Edition（10-10）译 - > 为TPH继承的插入、更新、删除操作映射到存储过程

10-10. 为TPH继承的插入、更新、删除操作映射到存储过程

问题

TPH继承模型，想把它的插入、修改、删除操作映射到存储过程

Solution

假设数据库有一个描述不同种类的产品表（Product ）(见Figure 10-13). 而且为这个表的每种产品创建了创建了派生模型，这个模型如Figure 10-14.

 

Figure 10-13. 一个含有鉴别列（ProductType）的产品表, 表的每行按该列的值划分不同的产品

 

Figure 10-14. TPH继承形式的模型

接下来把这个模型的插入、更新、删除操作映射到存储过程：

1. 在数据库里,创建  Listing 10-26 所示的存储过程. 这些存储过程为Book 和 DVD 实体处理插入、更新、删除操作。

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.右击模型的设计视图，选择“从数据库更新模型. 选择新建的存储过程, 单击“完成”，完成更新.

3.右击 Book 实体，选择“存储过程映射”.映射 InsertBook,UpdateBook, 和DeleteBook 存储过程到相应的操作。为插入操作绑定ProductId列 (见 Figure 10-15).

 

Figure 10-15. 映射存在过程到Book实体的插入、更新、删除操作. 特别注意要把插入操作绑定结果列绑定到ProductId.

4. 右击 DVD 实体,选择“存储过程映射”, 映射 InsertBook,UpdateBook, 和DeleteBook 存储过程到相应的操作。为插入操作绑定ProductId列(见 Figure 10-16).

 

它是如何工作的？

我们为Book和DVD实体的插入、更新、删除操作创建了存储过程，并且引入到模型. 引入后，我们把它们分别映射到相应的实体的相应操作上,需要注意的是两个实体的结果列绑定都需要绑定ProductId属性,这样就可以确保存储过程返回的产品自动创建的ProductId列的值映射到实体的ProductId属性上。

TPH继承可以通过执行插入的存储过程，把ProductType值插入到表中, EF能根据ProductType值，正确地实体化出派生实体.

接下来的Listing 10-27 代码演示了插入、更新、删除和查询.

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();

 

        }

}

输出结果如下面的 Listing 10-27所示:

---

 

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 

---

 

 

附：创建示例用到的数据库的脚本文件