Entity Framework 6 Recipes 2nd Edition (10-1)----Non-code frist method returns a collection of entities

Stored Procedures

Stored procedures exist in any relational database, such as Microsoft's SQL Server. A stored procedure is some code that is contained in a database, usually performing some operations on the data, which can improve performance for data-intensive computations and perform some business logic. When you use data, sometimes you get them through stored procedures.

In this chapter, we explore some of the areas that EF needs to focus on when using stored procedures. We also use stored procedures in other chapters of this book, but usually the context is to perform insert, update, and delete actions.

In this chapter, we'll show you a variety of ways to use stored procedures.

10-1. Non-code Frist method returns an entity collection

Problem

Want to get an entity collection from a stored procedure in a non-code frist Way

Solution Solutions

Code Second (I translate it into non-Code Frist) is a way to model an existing database by referencing Code-first technology

We assume that 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 up the DbContext subclass and the customer entity set, as shown in Listing 10-2:

Listing 10-2. The DbContext subclass for Customer entities

public class Ef6recipescontext:dbcontext

{

Public dbset<customer> Customers {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 the stored procedure as shown in Listing 10-3, which returns a qualified customer based on the company name and customer title

Listing 10-3. GetCustomers Returns all of the Customers and the Given Title in the Given company.

CREATE PROCEDURE Chapter10.getcustomers

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

As

Begin

SELECT * FROM

Chapter10. Customer where

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

(@ContactTitle is null or ContactTitle = @ContactTitle)

End

In order to use the GetCustomers stored procedure in a method, the operation is as follows:

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

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

Public icollection<customer> GetCustomers (string company, String contacttitle)

{

throw new NotImplementedException ();

}

2. Next implement this GetCustomers () method, which calls the Dbcontext.database SQLQuery method dbcontext.database

As shown in Listing 10-5.

Listing 10-5. DbContext subclass with GetCustomers () implementation

public class Ef6recipescontext:dbcontext

{

Public dbset<customer> Customers {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> GetCustomers (string company, String contacttitle)

{

Return database.sqlquery<customer> ("EXEC chapter10.getcustomers @Company,

@ContactTitle "

, New SqlParameter ("Company", company)

, New SqlParameter ("ContactTitle", ContactTitle))

. ToList ();

}

}

3. The next code snippet, listing 10-6, calls the getcustomers stored procedure.

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

Method

Insert some customer and let the stored procedure query.

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 Stevens", 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 is Sales Managers at goshopnow.com");

foreach (var c in allcustomers)

{

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

}

}

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

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

Customers that is Sales Managers at goshopnow.com

Customer:orin Torrey

Customer:robert Lancaster

Customer:julie Stevens

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

How does it work?

In order to receive the collection of entities returned in the stored procedure in the database, we implemented the GetCustomers () method in the DbContext subclass, which executes the stored procedure with dbcontext.database.sqlquery<t> () GetCustomers (see listing 10-3 for its definition). The SQLQuery () method can be used to execute DML (data manipulation language) statements that return a result set. The method receives a string of SQL statements. The sqlquery<t> () generic method returns a strongly typed entity set specified by the developer.

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

Entity Framework 6 Recipes 2nd Edition (10-1), non-code frist returns an entity collection