Entity Framework 6 Recipes 2nd Edition (10-9), entityrecipes

Entity Framework 6 Recipes 2nd Edition (10-9), entityrecipes

10-9. Use stored procedures for insertion and deletion in many-to-many relationships

Problem

To use a stored procedure in a load-free multi-to-Multi-Link (the stored procedure only affects the connected tables)

Solution

Assume that there is a multi-to-many relationship between the Author table (Author) and the Book table. Use the connection table AuthorBook to do multiple-to-many relations, as shown in Figure 10-11:

 

Figure 10-11.A payload-free, allow-to-Define relationship between an Author and a Book

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

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

Next, use the stored procedure to create insert and delete operations:

1. Create a stored procedure in the database, 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 the model design view, select "update model from database", select the stored procedure created by Listing 10-23, and click "finish". Then, the stored procedure is added to the model.

3. the current version of EF does not have a design view mapped to a link insert or delete operation. You can only manually map the view and right-click it. edmx file, select open mode, and select XML (text) Editor ". insert the code shown in Listing 10-24 under the <AssociationSetMapping> label)

Listing 10-24.Mapping the Stored Procedures to the Insert and Delete Actions for the operation-to-Operation 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>

</Modificationfunctioning ing>

The Listing 10-25 Code demonstrates the insert and delete operations. You can use SQL Profiler to view

The SQL statement generated after the InsertAuthorBook and DeleteAuthorBook stored procedures are called by EF when many-to-many relationships are updated

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 auth1 = 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 (au22 );

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 (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 = '123'

 

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 = '123'

 

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

How does it work?

To map stored procedures to insert and delete multiple-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 ing insertion and deletion, you need to open it directly in the XML editor. edmx file. Under the <modificationfunctioning ing> label in the ings node, we added the code that the insert and delete operations map to the stored procedure.

From the SQL statement generated by Listing 10-25, we can see that not only tables Author and Book are inserted or deleted, but also, stored procedures are used for insert and delete operations in the relational model.

 

Appendix: script file of the database used in the Creation example