Entity Framework Core: Connect a REST API to SQL Server

Build a persistent ASP.NET Core REST API using Entity Framework Core, SQL Server, dependency injection, migrations, LINQ queries and basic validation.

1. Understand the architecture

An HTTP client calls ASP.NET Core. The endpoint validates input and invokes an Entity Framework Core DbContext. EF Core translates suitable LINQ operations into SQL queries, and SQL Server persists the records. A DbContext represents a short unit of work and must not be shared as a singleton across concurrent requests.

2. Prerequisites

Install a supported .NET SDK, a local SQL Server instance or suitable test container and an editor. This guide uses the Microsoft SQL Server EF Core provider.

dotnet new web -n EfProductsApi
cd EfProductsApi
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef

3. Install packages

EF Core needs a database provider. The dotnet-ef tool supports migrations. Use compatible package major versions and an appropriate .NET SDK.

4. Create the model

The Product entity represents one database record. Its Id is generated by the database. Product name and price need business-level validation.

public sealed class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
}

5. Configure DbContext

A DbContext declares database entities and mapping. Set decimal precision explicitly rather than relying on potentially unsuitable defaults.

using Microsoft.EntityFrameworkCore;

public sealed class AppDbContext(DbContextOptions<AppDbContext> options)
    : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Product>()
            .Property(p => p.Price).HasPrecision(18, 2);
        builder.Entity<Product>()
            .Property(p => p.Name).HasMaxLength(120).IsRequired();
    }
}

6. Store connection settings safely

Keep connection strings out of source code and version control. Use development secrets locally, and prefer managed identities and protected configuration when operating in Azure. Never commit real production passwords.

dotnet user-secrets init
dotnet user-secrets set "ConnectionStrings:Sql" "Server=localhost;Database=EfProductsDemo;Trusted_Connection=True;TrustServerCertificate=True;"

7. Create API endpoints

Register DbContext in dependency injection. The sample endpoints retrieve products and create validated records with the appropriate 404 or 201 status. Authentication and more complete validation are separate production requirements.

using Microsoft.EntityFrameworkCore;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Sql")));
var app = builder.Build();

app.MapGet("/api/products", async (AppDbContext db) =>
    await db.Products.AsNoTracking().Take(100).ToListAsync());

app.MapPost("/api/products", async (Product input, AppDbContext db) =>
{
    if (string.IsNullOrWhiteSpace(input.Name) || input.Price < 0)
        return Results.BadRequest("Invalid product");
    var product = new Product { Name = input.Name.Trim(), Price = input.Price };
    db.Products.Add(product);
    await db.SaveChangesAsync();
    return Results.Created($"/api/products/{product.Id}", product);
});
app.Run();

8. Run migrations

Migrations apply model-driven schema updates. Review migration scripts and their target environment. Production migrations should be deliberately deployed rather than initiated unconditionally by every application instance.

dotnet ef migrations add InitialCreate
dotnet ef database update
dotnet run

9. Async queries and AsNoTracking

AsNoTracking avoids tracking overhead for read-only results. Async database access avoids blocking threads while waiting for I/O. Apply limits, filters and pagination for large datasets.

10. Troubleshooting and security

Connection failures may produce 500 responses. Add useful health checks, safe telemetry and bounded retry policies. Use parameterized queries, role-based access, HTTPS and server-side authorization. EF Core does not validate all business rules automatically.

11. Exercises

Add a ProductDescription column and create another migration. Implement GET by ID, reject invalid prices, and confirm data survives application restarts.

Documentation

Microsoft EF Core documentation · EF Core Migrations