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.
Join the DZone community and get the full member experience.
Join For FreeI'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.
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.
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.
Now, it's time to add Entity Framework via nuget package .
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.
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
Published at DZone with permission of Jalpesh Vadgama, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments