10-10. 為TPH繼承的插入、更新、刪除操作映射到存儲過程問題TPH繼承模型，想把它的插入、修改、刪除操作映射到存儲過程Solution假設資料庫有一個描述不同種類的產品表（Product ）(見Figure 10-13). 而且為這個表的每種產品創建了創建了派生模型，這個模型如Figure 1...
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
