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 两个表, 同时我们也可以看到，存储过程在关系模型中插入和删除操作时被使用。