Entity Framework Core & Stored Procedure using Fluent API

In this tutorial, we are going to cover crud operations (create, read, update, and delete) using entity framework core & stored procedure with Fluent API. So, first-of-all, we will add a model class in our project and create a database using migrations and then we will create stored procedures for insert, update and delete using Fluent API. Then we will perform insert, update and delete operations using entity framework core.

Entity framework core & stored procedure using fluent API

Let’s see how to perform crud operations using entity framework core & stored procedure with Fluent API.

There are two methods in Entity Framework Core to execute a stored procedure

• FormSql()
• ExecuteSqlCommand()

Note: – In entity framework core, there are some limitations to execute a stored procedure using FormSql() or ExecuteSqlCommand()

• It must return all the properties of the corresponding table of an entity.
• It cannot contain related data. It Can’t perform joins to formulate the result.
• Stored Procedures cannot be mapped with the entity. It means we cannot call SaveChanges method to call Insert, Update, and Delete operations.

Step # 1: Create a database

In this step, we will create a model class with the name of employee and then create a database using migrations. So, add a new class in your Models folder and then add the properties as you do see in the below code file.

Now, add DbSet<Employee> property in the DbContext class and then run these below commands in the Package Manager Console to create a database.

=> add-migration InitialCommit

=> update-database

These above commands will create a new database as you do see in the below screenshot.

Step # 2: How to Create stored Stored Procedures using Fluent API?

In this step, we will see how to create stored procedures for insert, update and delete using fluent API. So, go to DbContext class and then add the model builder within the OnModelCreating function as you do see below in the code file.

Note: – don’t forget to import libraries.

Now go to package manager console and then run this below command.

=> add-migration createSP

This above command will show you a partial createSP class. And this class have some code to create stored procedures for insert, update and delete as you do see in the below file code.

Now, run below command to update database.

=> update-database

Now, go to database and then you will see the output as you do see in the below screenshot.

Step # 3: How to execute stored procedure in Entity Framework Core?

Now, in this step we will see how to execute a stored procedure using entity framework core. As you know, we have three stored procedures like Employee_Insert , Employee_Update and Employee_Delete. 

Insert Record: So, we can insert the record in database using stored procedure in entity framework core by following the below code.

This above code will insert the record in database

Update Record: We can update the record in database using stored procedure in entity framework core by following the below code.

Delete Record: We can delete the record in the database using stored procedure in entity framework core by following the below code.

Thank you for reading. Please like and share within your community!