Entity Framework Stored Procedure Discover How to Query Entities

How to work with stored procedure within Entity Framework?

How to call a Stored Procedure from Entity Framework?

StackOverflow Related Questions

• How to call Stored Procedures with EntityFramework?
• Insertion order of multiple records in Entity Framework

Answer

Entity Framework allows you to use stored procedures to perform predefined logic on database tables. It can also specify that EF should use your stored procedures for inserting, updating, or deleting entities.

• You can use stored procedures either to get the data or to add/update/delete the records for one or multiple database tables.

We have a very simple database which contains one table, and you can create this table using the following SQL.

CREATE TABLE [dbo].[Blogs] (
    [BlogId] INT            IDENTITY (1, 1) NOT NULL,
    [Name]   NVARCHAR (MAX) NULL,
    [Url]    NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.Blogs] PRIMARY KEY CLUSTERED ([BlogId] ASC)
);

Select New Query and enter the following code in T-SQL editor to add a stored procedure in your database, which will return the blog based on its Id.

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

BEGIN

   EXEC dbo.sp_executesql @statement = N'
   CREATE PROCEDURE [dbo].[GetBlog]
   @BlogId int
   AS
   SELECT * FROM dbo.Blogs 
   WHERE BlogId = @BlogId
   '
END
GO

The GetBlog stored procedure take BlogId as a parameter and will return the Blog.

Execute the SQL command, and you will see a stored procedure "GetBlog" is added to your database. Execute the query and refresh your database

Let's add create a new project and add new item select ADO.NET Entity Data Model. Use EF Designer from database and EF will generate all the required classes for you. You can simply call the GetBlog stored procedure using context.

using ( var context = new BloggingContextDbEntities())
{
    var blogId = 1;
    var blog = context.GetBlog(blogId).FirstOrDefault();
}

It will return the blog where Id is 1.