Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Use Stored Procedure With Entity Framework Code First

DZone's Guide to

How to Use Stored Procedure With Entity Framework Code First

In this post we're going to learn about how we can use stored procedure with Entity framework code first.

· Database Zone
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

I'm getting lots of request from the readers of my blog about writing a post on how to use stored procedure with Entity Framework Code First. So, in this post we're going to do just that.

To demonstrate, we are going to create a table called Employee as following.

table-entity-framework-code-first-stored-procedure

Here is the create table script for the same.

CREATETABLE[dbo].[Employee]([EmployeeId] [int] NOTNULL,[FirstName] [nvarchar](50) NULL,[LastName] [nvarchar](50) NULL,[Designation] [nvarchar](50) NULL,CONSTRAINT[PK_Employee] PRIMARYKEYCLUSTERED ([EmployeeId] ASC)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]) ON[PRIMARY]

Following is the data I have inserted there.

entity-framework-table-data

And here is the store procedure we are using for that.

CREATEPROCEDUREusp_GetAllEmployeesASSELECTEmployeeId,FirstName,LastName,Designation FROMEmployee

Now that we are already done with our database side, it's time to write some C# code. I'm going to use the simple console application for the same.

console-application-ef-code-first-stored-procedure

Now, it's time to add Entity Framework via nuget package .

nuget-package-entity-framework-code-first

Here is the model class I have created.

namespaceCodeFirstStoredProcedure{publicclassEmployee{publicintEmployeeId { get; set; }publicstringFirstName { get; set; }publicstringLastName { get; set; }publicstringDesignation { get; set; }}}

And then, I created Entity Framework dbcontext as following.

usingSystem.Data.Entity;   namespaceCodeFirstStoredProcedure{publicclassEmployeeDbContext : DbContext{publicEmployeeDbContext(): base("EmployeeConnectionString"){   }publicDbSet<Employee> Employees { get; set; }}}

The following is a code for using stored procedure to get data from the database via Entity Framework Code First.

usingSystem;usingSystem.Linq;   namespaceCodeFirstStoredProcedure{classProgram{staticvoidMain(string[] args){using(EmployeeDbContext dbContext = newEmployeeDbContext()){stringcommandText = "[dbo].[usp_GetAllEmployees]";var employees = dbContext.Database.SqlQuery<Employee>(commandText).ToList();   Console.WriteLine("Printing Employee");foreach(var employee inemployees){Console.WriteLine(employee.EmployeeId);Console.WriteLine(employee.FirstName);Console.WriteLine(employee.LastName);Console.WriteLine(employee.Designation);Console.WriteLine("-----------------");}}Console.ReadKey();}}}

In the above code, if you look carefully, I have used SQL Query function to execute stored procedure which will return a list of employees. Then, I have printed this list with console.writeline.  Now, when you run this application, the output will be as expected.

entity-framework-code-first-stored-procedure

That's it. It's very easy to use stored procedure with Entity Framework Code First. Hope you like it. Stay tuned for more!

You can find complete source code of this sample application at the following location: https://github.com/dotnetjalps/EFCodeFirstStoredProcedure

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
entity framework code first ,stored procedure ,database ,c#

Published at DZone with permission of Jalpesh Vadgama, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}