Entity Framework 6 Recipes 2nd Edition (10-1) Translation-& gt; returns an object set in non-Code Frist mode, recipesfrist

Entity Framework 6 Recipes 2nd Edition (10-1) Translation-> returns an object set in non-Code Frist mode, recipesfrist

Stored Procedure

Stored Procedures exist in any type of relational database, such as Microsoft SQL Server. stored procedures are some code contained in the database. They usually perform some operations on the data. They can improve the performance of data-intensive computing and execute some business logic. when you use data, you may obtain them through stored procedures.

In this chapter, we will discuss some notes that EF needs to pay attention to when using stored procedures. We also use stored procedures in other chapters of this book, but the context usually performs insert, update, and delete actions.

In this chapter, we will show you how to use stored procedures.

10-1. returns an object set in non-Code Frist mode.

Problem

You want to obtain an object set from the stored procedure using the non-Code Frist method.

Solution

Code second(I translated it into a non-Code Frist) is a method for modeling an existing database by referring to the Code-First technology.

Let's assume there is a POCO model, as shown in Listing 10-1:

Listing 10-1.The Customer POCO Model

Public class Customer

{

Public int CustomerId {get; set ;}

Public string Name {get; set ;}

Public string Company {get; set ;}

Public string ContactTitle {get; set ;}

}

We have set the DbContext subclass and Customer object set, as shown in Listing 10-2:

Listing 10-2.The DbContext Subclass for Customer Entities

Public class EF6RecipesContext: DbContext

{

Public DbSet <Customer> MERs {get; set ;}

Public EF6RecipesContext (): base ("name = EF6CodeFirstRecipesContext ")

{

}

Protected override void OnModelCreating (DbModelBuilder modelBuilder)

{

Base. OnModelCreating (modelBuilder );

ModelBuilder. Types <Customer> ()

. Configure (c =>

{

C. HasKey (cust => cust. CustomerId );

C. Property (cust => cust. CustomerId)

. HasDatabaseGeneratedOption (DatabaseGeneratedOption. Identity );

C. Property (cust => cust. Name)

. HasMaxLength (50 );

C. Property (cust => cust. Company)

. HasMaxLength (50 );

C. Property (cust => cust. ContactTitle)

. HasMaxLength (50 );

C. ToTable ("Customer", "Chapter10 ");

});

}

}

In the database, we have defined a stored procedure, as shown in Listing 10-3. The stored procedure returns qualified customers based on the company name and customer title.

Listing 10-3.GetCustomers Returns All of the Customers with the Given Title in the Given Company.

Create procedure chapter10.getmers MERs

(@ Company varchar (50), @ ContactTitle varchar (50 ))

As

Begin

Select * from

Chapter10.Customer where

(@ Company is null or Company = @ Company) and

(@ ContactTitle is null or ContactTitle = @ ContactTitle)

End

To use the getmers MERs stored procedure in the method, perform the following operations:

1. Create a public method (named GetCustomers) in the DbContext subclass. It accepts two string parameters and returns the Customer set, as shown in Listing 10-4.

Listing 10-4.A New Method to Return a Collection of Customer Objects

Public ICollection <Customer> getmers MERs (string company, string contactTitle)

{

Throw new NotImplementedException ();

}

2. Implement the getmers MERs () method, which calls the SqlQuery method DbContext. Database of DbContext. Database

See Listing 10-5.

Listing 10-5.DbContext Subclass with GetCustomers () Implementation

Public class EF6RecipesContext: DbContext

{

Public DbSet <Customer> MERs {get; set ;}

Public EF6RecipesContext (): base ("name = EF6CodeFirstRecipesContext ")

{

}

Protected override void OnModelCreating (DbModelBuilder modelBuilder)

{

Base. OnModelCreating (modelBuilder );

ModelBuilder. Types <Customer> ()

. Configure (c =>

{

C. HasKey (cust => cust. CustomerId );

C. Property (cust => cust. CustomerId)

. HasDatabaseGeneratedOption (DatabaseGeneratedOption. Identity );

C. Property (cust => cust. Name)

. HasMaxLength (50 );

C. Property (cust => cust. Company)

. HasMaxLength (50 );

C. Property (cust => cust. ContactTitle)

. HasMaxLength (50 );

C. ToTable ("Customer", "Chapter10 ");

});

}

 

Public ICollection <Customer> getmers MERs (string company, string contactTitle)

{

Return Database. SqlQuery <Customer> ("EXEC Chapter10.GetCustomers @ Company,

@ ContactTitle"

, New SqlParameter ("Company", company)

, New SqlParameter ("ContactTitle", contactTitle ))

. ToList ();

}

}

3. the following code snippet Listing 10-6 is to call the GetCustomers stored procedure.

Listing 10-6.Querying the Model with the GetCustomers Stored Procedure via the GetCustomers ()

Method

// Insert some customers to query stored procedures.

Using (var context = new EF6RecipesContext ())

{

Var c1 = new Customer {Name = "Robin Steele", Company = "GoShopNow.com ",

ContactTitle = "CEO "};

Var c2 = new Customer {Name = "Orin Torrey", Company = "GoShopNow.com ",

ContactTitle = "Sales Manager "};

Var c3 = new Customer {Name = "Robert Lancaster", Company = "GoShopNow.com ",

ContactTitle = "Sales Manager "};

Var c4 = new Customer {Name = "Julie Steven s", Company = "GoShopNow.com ",

ContactTitle = "Sales Manager "};

Context. Customers. Add (c1 );

Context. Customers. Add (c2 );

Context. Customers. Add (c3 );

Context. Customers. Add (c4 );

Context. SaveChanges ();

}

Using (var context = new EF6RecipesContext ())

{

Var allCustomers = context. GetCustomers ("GoShopNow.com", "Sales Manager ");

Console. WriteLine ("Customers that are Sales Managers at GoShopNow.com ");

Foreach (var c in allCustomers)

{

Console. WriteLine ("Customer: {0}", c. Name );

}

}

The following Listing 10-6 is the output result of the console:

========================================================== ========================================================== ================

Customers that are Sales Managers at GoShopNow.com

Customer: Orin Torrey

Customer: Robert Lancaster

Customer: Julie Steven s

========================================================== ==================================

How does it work?

To receive the object set returned by the stored procedure in the database, we implement the getmers MERs () method in the DbContext subclass. This method uses DbContext. database. sqlQuery <T> () to execute the Stored Procedure getmers MERs (for its definition, see Listing 10-3 ). the SqlQuery () method can be used to execute DML statements that return a result set. this method receives the string of an SQL statement. The SqlQuery <T> () generic method returns a strongly typed entity set specified by a developer.

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