Entity Framework - Stored Procedures

---

The Entity Framework allows you to use stored procedures in the Entity Data Model instead of, or in combination with, its automatic command generation.

• You can use stored procedures to perform predefined logic on database tables, and many organizations have policies in place that require the use of these stored procedures.

• It can also specify that EF should use your stored procedures for inserting, updating, or deleting entities.

• Although the dynamically built commands are secure, efficient, and generally as good as or better than those you may write yourself, there are many cases where stored procedures already exist and your company practices may restrict direct use of the tables.

• Alternatively, you may just want to have explicit control over what is executed on the store and prefer to create stored procedures.

The following example creates a new project from File → New → Project.

Step 1 − Select the Console Application from the middle pane and enter StoredProceduresDemo in the name field.

Step 2 − In Server explorer right-click on your database.

Step 3 − Select New Query and enter the following code in T-SQL editor to add a new table in your database.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = 
   OBJECT_ID(N'[dbo].[StudentGrade]') AND type in (N'U'))

BEGIN

   CREATE TABLE [dbo].[StudentGrade](

      [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
      [CourseID] [int] NOT NULL,
      [StudentID] [int] NOT NULL,
      [Grade] [decimal](3, 2) NULL,

      CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED (
         [EnrollmentID] ASC
      )

      WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

   ) ON [PRIMARY]

END
GO

Step 4 − Right-click on the editor and select Execute.

Step 5 − Right-click on your database and click refresh. You will see the newly added table in your database.

Step 6 − In Server explorer, right-click on your database again.

Step 7 − Select New Query and enter the following code in T-SQL editor to add a stored procedure in your database, which will return the Student grades.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = 
   OBJECT_ID(N'[dbo].[GetStudentGrades]') AND type in (N'P', N'PC'))

BEGIN

   EXEC dbo.sp_executesql @statement = N'
   CREATE PROCEDURE [dbo].[GetStudentGrades]
   @StudentID int
   AS
   SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade 
   WHERE StudentID = @StudentID
   '
END
GO

Step 8 − Right-click on the editor and select Execute.

Step 9 − Right-click on your database and click refresh. You will see that a stored procedure is created in your database.

Step 10 − Right-click on the project name in Solution Explorer and select Add → New Item.

Step 11 − Then select ADO.NET Entity Data Model in the Templates pane.

Step 12 − Enter SPModel as name, and then click Add.

Step 13 − In the Choose Model Contents dialog box, select EF designer from database, and then click Next.

Step 14 − Select your database and click Next.

Step 15 − In the Choose Your Database Objects dialog box click on tables, views.

Step 16 − Select the GetStudentGradesForCourse function located under the Stored Procedures and Functions node and click Finish.

Step 17 − Select View → Other Windows → Entity Data Model Browser and right-click GetStudentGrades under Function Imports and select Edit.

It will produce the following dialog.

Step 18 − Click on Entities radio button and select StudentGrade from the combobox as return type of this stored procedure and click Ok.

Let’s take a look at the following C# code in which all the grades will be retrieved by passing the student ID as parameter in GetStudentGrades stored procedure.

class Program {

   static void Main(string[] args) {

      using (var context = new UniContextEntities()) {

         int studentID = 22;
         var studentGrades = context.GetStudentGrades(studentID);

         foreach (var student in studentGrades) {
            Console.WriteLine("Course ID: {0}, Title: {1}, Grade: {2} ", 
               student.CourseID, student.Course.Title, student.Grade);
         }

         Console.ReadKey();

      }
   }
}

When the above code is compiled and executed you will receive the following output −

Course ID: 4022, Title: Microeconomics, Grade: 3.00
Course ID: 4041, Title: Macroeconomics, Grade: 3.50

We recommend that you execute the above example in a step-by-step manner for better understanding.