Entity Framework Core Setup in Web API

Most Web APIs exist solely to perform CRUD (Create, Read, Update, Delete) operations against a database. Entity Framework Core (EF Core) is the official Object-Relational Mapper (ORM) for .NET. It allows you to query the database entirely using C# LINQ, without writing a single line of raw SQL.

1. Required Packages

Unlike monolithic ASP.NET MVC templates that often include EF Core by default, empty Web API projects require you to explicitly install the EF Core packages via the NuGet Package Manager or the .NET CLI.

# The core ORM framework
dotnet add package Microsoft.EntityFrameworkCore

# The specific provider for SQL Server (use Npgsql for PostgreSQL)
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

# CLI tools required for running Migrations (Code-First)
dotnet add package Microsoft.EntityFrameworkCore.Design

2. Connecting to the Database

Hardcoding connection strings into C# files is a massive security risk. We must store the connection string in the appsettings.json file, and then inject it into the EF Core Service during application startup.

1. The appsettings.json file

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=ApiDatabase;User Id=sa;Password=YourPassword123!;TrustServerCertificate=True;"
  }
}

2. Registering the DbContext in Program.cs

We use the built-in extremely optimized AddDbContext extension method. This automatically registers your DbContext into the Dependency Injection container with a Scoped lifetime.

var builder = WebApplication.CreateBuilder(args);

// Register EF Core
builder.Services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
});

var app = builder.Build();

3. Database Context Pooling (.NET 8+)

Creating a brand new DbContext instance for every single HTTP request is relatively fast, but under massive enterprise load (e.g., 5,000 requests per second), the memory allocation overhead becomes a bottleneck.

// Replace AddDbContext with AddDbContextPool for an instant 20% performance boost in high-throughput APIs
builder.Services.AddDbContextPool<ApplicationDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
});

4. Interview Mastery

Q: "Why is it strictly recommended to use the connection string from configuration instead of hardcoding it in the DbContext's `OnConfiguring` method?"

Architect Answer: "Security and deployment agility. If you hardcode a connection string inside `OnConfiguring`, it gets compiled into the physical `.dll` binary. When you deploy the API to a Staging server, and then to a Production Server, the binary still points to your Local Development database. By reading from `appsettings.json` via `IConfiguration` in `Program.cs`, we externalize the connection. We can then easily override it using Azure App Service Environment Variables or Docker injected secrets without ever recompiling our C# code."