Entity Framework 6 Recipes 2nd Edition (10-9)---using stored procedures for insertions and deletions in a many-to-many relationship

10-9. Using stored procedures for insertions and deletions in a many-to-many relationship

Problem

Want to use a stored procedure in a multi-to-many relationship without load (the stored procedure only affects the connection table of the relationship)

Solution Solutions

Suppose there is a many-to-many relationship between the author (Author) table and the Book table. Use the Join table Authorbook to do long-to-many relationships, as Figure 10-11 shows:

Figure 10-11. a payload-free, many-to-many relationship between an Author and A book

When the table is generated, the model is as shown in Figure 10-12:

Figure 10-12. The model created by importing the tables 10-11

Next use the stored procedure to create the INSERT and delete operations:

1. In the database, create the stored procedure as shown in 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. Right-click on the Model Design view, select "Update model from Database", select the stored procedure created by Listing 10-23, and click "Finish" to add the stored procedure to the model.

3. The current version of EF does not have a design view of the insert and delete operations mappings for a relationship, it can only be mapped manually, right-click the. edmx file, select Open With, and select XML (text) editor. Insert the code shown in Listing 10-24 under the <AssociationSetMapping> tab (in the following example: Name the modified code according to your own examples)

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>

The next listing 10-25 code demonstrates the insert and delete operations. You can use SQL Profiler to see

SQL statements generated after Insertauthorbook and Deleteauthorbook stored procedures are called by EF when updating many-to-many relationships

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

}

}

The SQL statements tracked in SQL Profiler are as follows (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 (+) ', @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 (+) ', @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 (+) ', @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 ', @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

How does it work?

To map stored procedures to insert and delete operations in many-to-many relationships, we create stored procedures in the database and then use the stored procedure model. Because the EF Design view does not support relational model-map insertions and deletions, we need to open the. edmx file directly in the Mappings node with an XML editor <ModificationFunctionMapping> tag, we added the code that the INSERT and delete operations map to the stored procedure.

Tracing the generated SQL from listing 10-25, we can see that not only the Author and book two tables are inserted or deleted, but we can also see that the stored procedure is used when inserting and deleting operations in the relational model.

Attached: Creating a script file for the database used by the sample

Entity Framework 6 Recipes 2nd Edition (10-9)---using stored procedures for insertions and deletions in a many-to-many relationship