Working with Stored Procedures in EF Core

Introduction:

Entity Framework (EF) Core is a popular Object-Relational Mapping (ORM) framework used for accessing databases in .NET applications. EF Core provides a flexible and easy-to-use API for performing database operations such as querying, updating, and deleting data.

Stored procedures are precompiled database objects that can be used to encapsulate business logic and improve database performance. In this article, we will explore how to work with stored procedures in EF Core.

Step 1: Creating a Stored Procedure

The first step is to create a stored procedure in your database. You can create a stored procedure using SQL Server Management Studio or any other database management tool.

Here's an example of a stored procedure that selects all customers from the "Customers" table:

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
   SELECT * FROM Customers
END

Step 2: Mapping the Stored Procedure to EF Core

Once you have created the stored procedure, you need to map it to EF Core. To do this, you can use the "FromSqlRaw" method provided by EF Core.

Here's an example of mapping the "GetAllCustomers" stored procedure to EF Core:

public class MyDbContext : DbContext
{
   public MyDbContext(DbContextOptions<MyDbContext> options)
      : base(options)
   { }

   public DbSet<Customer> Customers { get; set; }

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      modelBuilder.Entity<Customer>().HasNoKey();
   }

   public IList<Customer> GetAllCustomers()
   {
      return Customers.FromSqlRaw("EXEC GetAllCustomers").ToList();
   }
}

In the example above, we have added a new method called "GetAllCustomers" to the "MyDbContext" class. This method uses the "FromSqlRaw" method to execute the "GetAllCustomers" stored procedure and returns the result as a list of "Customer" objects.

Note that we have also added a "HasNoKey" method to the "Customer" entity to indicate that it does not have a primary key. This is because the "GetAllCustomers" stored procedure does not return a primary key value for each record.

Step 3: Using the Stored Procedure in Code

Now that we have mapped the stored procedure to EF Core, we can use it in our code. Here's an example of how to use the "GetAllCustomers" method to retrieve all customers from the database:

using (var context = new MyDbContext(options))
{
   var customers = context.GetAllCustomers();

   foreach (var customer in customers)
   {
      Console.WriteLine($"{customer.FirstName} {customer.LastName}");
   }
}

In the example above, we create a new instance of the "MyDbContext" class and call the "GetAllCustomers" method to retrieve all customers from the database. We then loop through the list of customers and print their first and last names to the console.

Conclusion:

Stored procedures can be a powerful tool for encapsulating business logic and improving database performance. With EF Core, you can easily map stored procedures to your code and use them to retrieve data from the database. By following the steps outlined in this article, you should now have a good understanding of how to work with stored procedures in EF Core.

Related posts

Add comment

Loading