10-9. 在多對多關係中為插入和刪除使用存儲過程問題想要在一個無載荷的多對多關係中使用存儲過程(存儲過程隻影響關係的連接表)解決方案假設有一個多對多關係的作者（ Author）表和書籍（ Book）表. 用連接表AuthorBook來做多對多關係，如 Figure 10-11.所示:Figure ...
10-9. 在多對多關係中為插入和刪除使用存儲過程
問題
想要在一個無載荷的多對多關係中使用存儲過程(存儲過程隻影響關係的連接表)
解決方案
假設有一個多對多關係的作者（ Author）表和書籍（ Book）表. 用連接表AuthorBook來做多對多關係，如 Figure 10-11.所示:
Figure 10-11. A payload-free, many-to-many relationship between an Author and a Book
當把表生成模型，那麼模型就如Figure 10-12所示：
Figure 10-12. The model created by importing the tables in Figure 10-11
接下來用存儲過程創建插入和刪除操作：
1.在資料庫中，創建如Listing 10-23所示的存儲過程.
Listing 10-23. The stored Procedures for the Insert and Delete Actions
create procedure [chapter10].[InsertAuthorBook]
(@AuthorId int,@BookId int)
as
begin
insert into chapter10.AuthorBook(AuthorId,BookId) values (@AuthorId,@BookId)
end
go
create procedure [chapter10].[DeleteAuthorBook]
(@AuthorId int,@BookId int)
as
begin
delete chapter10.AuthorBook where AuthorId = @AuthorId and BookId = @BookId
end
2. 右擊模型設計視圖，選擇“從資料庫中更新模型”，選擇 Listing 10-23所創建的存儲過程，單擊“完成”，這樣就把存儲過程添加到了模型里。
3. 目前版本的EF沒有為一個關係插入和刪除操作映射的設計視圖，只能用手工去映射, 右擊.edmx 文件，選擇“打開方式”，選擇“XML （文本）編輯器”. 在<AssociationSetMapping> 標簽下插入 Listing 10-24所示的代碼（譯註：根據自己的例子命名修改代碼里的）
Listing 10-24. Mapping the Stored Procedures to the Insert and Delete Actions for the Many-to-Many Association
<ModificationFunctionMapping>
<InsertFunction FunctionName="EFRecipesModel1009.Store.InsertAuthorBook">
<EndProperty Name="Author">
<ScalarProperty Name="AuthorId" ParameterName="AuthorId" />
</EndProperty>
<EndProperty Name="Book">
<ScalarProperty Name="BookId" ParameterName="BookId" />
</EndProperty>
</InsertFunction>
<DeleteFunction FunctionName="EFRecipesModel1009.Store.DeleteAuthorBook">
<EndProperty Name="Author">
<ScalarProperty Name="AuthorId" ParameterName="AuthorId" />
</EndProperty>
<EndProperty Name="Book">
<ScalarProperty Name="BookId" ParameterName="BookId" />
</EndProperty>
</DeleteFunction>
</ModificationFunctionMapping>
接下來Listing 10-25 代碼演示了插入和刪除操作.你可以用Sql Profiler來查看當
InsertAuthorBook 和DeleteAuthorBook 存儲過程被EF在更新多對多關係時調用後生成的SQL語句
Listing 10-25. Inserting into the Model
class Program
{
static void Main(string[] args)
{
using (var context = new EFRecipesEntities1009())
{
context.Database.ExecuteSqlCommand("delete from chapter10.AuthorBook");
context.Database.ExecuteSqlCommand("delete from chapter10.book");
context.Database.ExecuteSqlCommand("delete from chapter10.Author");
var auth1 = new Author { Name = "Jane Austin" };
var book1 = new Book
{
Title = "Pride and Prejudice",
ISBN = "1848373104"
};
var book2 = new Book
{
Title = "Sense and Sensibility",
ISBN = "1440469563"
};
auth1.Books.Add(book1);
auth1.Books.Add(book2);
var auth2 = new Author { Name = "Audrey Niffenegger" };
var book3 = new Book
{
Title = "The Time Traveler's Wife",
ISBN = "015602943X"
};
auth2.Books.Add(book3);
context.Authors.Add(auth1);
context.Authors.Add(auth2);
context.SaveChanges();
context.Books.Remove(book1);
context.SaveChanges();
}
Console.WriteLine("\npress any key to exit...");
Console.ReadKey();
}
}
SQL Profiler里跟蹤到的SQL語句如下（Listing 10-25）所示:
exec sp_executesql N'insert [Chapter10].[Author]([Name])values (@0)
select [AuthorId] from [Chapter10].[Author]
where @@ROWCOUNT > 0 and [AuthorId] = scope_identity()',N'@0 varchar(50)',@0='Jane Austin'
exec sp_executesql N'insert [Chapter10].[Author]([Name])values (@0)
select [AuthorId] from [Chapter10].[Author]
where @@ROWCOUNT > 0 and [AuthorId] = scope_identity()',N'@0 varchar(50)',
@0='Audrey Niffenegger'
exec sp_executesql N'insert [Chapter10].[Book]([Title], [ISBN])values (@0, @1)
select [BookId] from [Chapter10].[Book]
where @@ROWCOUNT > 0 and [BookId] = scope_identity()',N'@0 varchar(50),
@1 varchar(50)',@0='Pride and Prejudice',@1='1848373104'
exec sp_executesql N'insert [Chapter10].[Book]([Title], [ISBN])values (@0, @1)
select [BookId] from [Chapter10].[Book]
where @@ROWCOUNT > 0 and [BookId] = scope_identity()',N'@0 varchar(50),
@1 varchar(50)',@0='Sense and Sensibility',@1='1440469563'
exec sp_executesql N'insert [Chapter10].[Book]([Title], [ISBN])values (@0, @1)
select [BookId] from [Chapter10].[Book]
where @@ROWCOUNT > 0 and [BookId] = scope_identity()',N'@0 varchar(50),
@1 varchar(50)',@0='The Time Traveler''s Wife',@1='015602943X'
exec [Chapter10].[InsertAuthorBook] @AuthorId=1,@BookId=1
exec [Chapter10].[InsertAuthorBook] @AuthorId=1,@BookId=2
exec [Chapter10].[InsertAuthorBook] @AuthorId=2,@BookId=3
exec [Chapter10].[DeleteAuthorBook] @AuthorId=1,@BookId=1
exec sp_executesql N'delete [Chapter10].[Book] where ([BookId] = @0)',N'@0 int',@0=1
它是如何工作的？
為把存儲過程映射到多對多關係中的插入和刪除操作, 我們在資料庫中創建存儲過程，然後用存儲過程模型.由於EF設計視圖不支持關係型的模型映射插入和刪除操作,我們需要用XML編輯器直接打開.edmx 文件， 在Mappings 節點里 <ModificationFunctionMapping> 標簽下，我們添加了插入和刪除操作映射到存儲過程的代碼。
從Listing 10-25跟蹤生成的SQL,我們可以看到，不只是插入或刪除 Author 和 Book 兩個表, 同時我們也可以看到，存儲過程在關係模型中插入和刪除操作時被使用。
