Entity Framework Core with StoredProcedure Example in Code FIrst And Data First Approach

 

Entity Framework Core with Stored Procedures (Code First Approach) in .NET 8/9/10

In a Code First approach, Entity Framework Core creates the database from your C# models. You can still use stored procedures by creating them through a migration and then calling them from EF Core.

---

Project Structure

MyProject
│
├── Models
│      Employee.cs
│
├── Data
│      AppDbContext.cs
│
├── Repository
│      EmployeeRepository.cs
│
├── Controllers
│      EmployeeController.cs
│
└── Program.cs

---

Step 1: Install Packages

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

---

Step 2: Employee Model

public class Employee
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public string Email { get; set; } = string.Empty;

    public decimal Salary { get; set; }

    public string Department { get; set; } = string.Empty;
}

---

Step 3: DbContext

using Microsoft.EntityFrameworkCore;

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

    }

    public DbSet<Employee> Employees => Set<Employee>();
}

---

Step 4: Configure Connection String

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=EFCoreSPDemo;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

---

Step 5: Register DbContext

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
});

---

Step 6: Create Migration

Add-Migration InitialCreate

or

dotnet ef migrations add InitialCreate

---

Step 7: Update Database

Update-Database

EF Core creates

Employees Table

---

Step 8: Create Stored Procedure using Migration

Create another migration.

Add-Migration AddStoredProcedures

Open the migration.

public partial class AddStoredProcedures : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM Employees
END

");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP PROCEDURE IF EXISTS GetEmployees");
    }
}

Now run

Update-Database

Stored procedure is created automatically.

---

Step 9: Execute Stored Procedure

var employees = await _context.Employees
                .FromSqlRaw("EXEC GetEmployees")
                .ToListAsync();

Result

Id Name Salary
1 Shiva 50000
2 Rahul 45000
3 Amit 60000

---

Step 10: Stored Procedure with Parameter

Migration

migrationBuilder.Sql(@"

CREATE PROCEDURE GetEmployeeByDepartment
    @Department NVARCHAR(100)
AS
BEGIN

SELECT *
FROM Employees
WHERE Department=@Department

END

");

Run

Update-Database

Execute

var department = "IT";

var employees = await _context.Employees
        .FromSqlInterpolated(
            $"EXEC GetEmployeeByDepartment {department}")
        .ToListAsync();

---

Step 11: Apply LINQ

Once the result is returned, you can continue using LINQ.

var employees = await _context.Employees
        .FromSqlRaw("EXEC GetEmployees")
        .Where(x => x.Salary > 50000)
        .OrderBy(x => x.Name)
        .ToListAsync();

Output

Amit
Shiva

---

Step 12: INSERT Stored Procedure

Migration

migrationBuilder.Sql(@"

CREATE PROCEDURE AddEmployee

@Name NVARCHAR(100),
@Email NVARCHAR(100),
@Salary DECIMAL(18,2),
@Department NVARCHAR(100)

AS

BEGIN

INSERT INTO Employees
(Name,Email,Salary,Department)

VALUES

(@Name,@Email,@Salary,@Department)

END

");

Execute

await _context.Database.ExecuteSqlInterpolatedAsync($@"
EXEC AddEmployee
     {"Rohit"},
     {"rohit@gmail.com"},
     {40000},
     {"Finance"}");

---

Step 13: UPDATE Stored Procedure

Migration

migrationBuilder.Sql(@"

CREATE PROCEDURE UpdateSalary

@Id INT,
@Salary DECIMAL(18,2)

AS

BEGIN

UPDATE Employees

SET Salary=@Salary

WHERE Id=@Id

END

");

Execute

await _context.Database.ExecuteSqlInterpolatedAsync($@"
EXEC UpdateSalary
     {1},
     {65000}");

---

Step 14: DELETE Stored Procedure

Migration

migrationBuilder.Sql(@"

CREATE PROCEDURE DeleteEmployee

@Id INT

AS

BEGIN

DELETE FROM Employees

WHERE Id=@Id

END

");

Execute

await _context.Database.ExecuteSqlInterpolatedAsync($@"
EXEC DeleteEmployee {5}");

---

Step 15: Stored Procedure Returning Custom Result

Suppose the stored procedure returns:

Department	TotalEmployees	AverageSalary

Create DTO

public class EmployeeSummary
{
    public string Department { get; set; } = "";

    public int TotalEmployees { get; set; }

    public decimal AverageSalary { get; set; }
}

Configure

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

Stored Procedure

CREATE PROCEDURE GetDepartmentSummary
AS
BEGIN

SELECT
Department,
COUNT(*) TotalEmployees,
AVG(Salary) AverageSalary
FROM Employees
GROUP BY Department

END

Execute

var summary = await _context
                .Set<EmployeeSummary>()
                .FromSqlRaw("EXEC GetDepartmentSummary")
                .ToListAsync();

---

Repository Pattern

public class EmployeeRepository
{
    private readonly AppDbContext _context;

    public EmployeeRepository(AppDbContext context)
    {
        _context = context;
    }

    public async Task<List<Employee>> GetEmployeesAsync()
    {
        return await _context.Employees
            .FromSqlRaw("EXEC GetEmployees")
            .ToListAsync();
    }

    public async Task<List<Employee>> GetByDepartmentAsync(string department)
    {
        return await _context.Employees
            .FromSqlInterpolated($"EXEC GetEmployeeByDepartment {department}")
            .ToListAsync();
    }

    public async Task AddEmployeeAsync(Employee employee)
    {
        await _context.Database.ExecuteSqlInterpolatedAsync($@"
            EXEC AddEmployee
                {employee.Name},
                {employee.Email},
                {employee.Salary},
                {employee.Department}");
    }

    public async Task UpdateSalaryAsync(int id, decimal salary)
    {
        await _context.Database.ExecuteSqlInterpolatedAsync($@"
            EXEC UpdateSalary
                {id},
                {salary}");
    }

    public async Task DeleteEmployeeAsync(int id)
    {
        await _context.Database.ExecuteSqlInterpolatedAsync($@"
            EXEC DeleteEmployee
                {id}");
    }
}

---