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 popcies 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 exppcit 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 Apppcation from the middle pane and enter StoredProceduresDemo in the name field.

Step 2 − In Server explorer right-cpck 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-cpck on the editor and select Execute.

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

Step 6 − In Server explorer, right-cpck 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-cpck on the editor and select Execute.

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

Step 10 − Right-cpck 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 cpck Add.

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

Step 14 − Select your database and cpck Next.

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

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

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

It will produce the following dialog.

Step 18 − Cpck on Entities radio button and select StudentGrade from the combobox as return type of this stored procedure and cpck 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.