Getting Started with ASP.NET Core Web API and Entity Framework

This tutorial walks you through building your first ASP.NET Core Web API on .NET 10 with Entity Framework Core. You will connect the API to an existing SQL Server database (AdventureWorks), generate the entity model from the database, and expose product data through a controller — then test everything in the browser with the built-in OpenAPI document and Scalar.

This is the database-first approach: the database already exists and EF Core generates the C# model from it. If you are starting from classes instead, see the code-first workflow with EF Core migrations.

ASP.NET Core Web API with Entity Framework Core architecture

What you need

.NET 10 SDK (the steps work the same on .NET 8/9 — only the target framework differs)
SQL Server, or SQL Server running in Docker
Visual Studio 2022/2026 or VS Code — the commands below use the dotnet CLI, so any editor works

Create the ASP.NET Core Web API project

Create the solution and project from the terminal:

dotnet new webapi --use-controllers -n GeekStore.API -o GeekStore.API
dotnet new sln -n GeekStore
dotnet sln add GeekStore.API

The --use-controllers flag gives you the controller-based template. Without it you get minimal APIs, which are great for small services — but for this tutorial controllers keep the structure familiar if you are coming from MVC.

In Visual Studio the equivalent is File → New → Project → ASP.NET Core Web API, with the Use controllers checkbox ticked.

The template ships with a WeatherForecast sample controller. Delete WeatherForecast.cs and Controllers/WeatherForecastController.cs — we won't need them.

Create the AdventureWorks database

We will read data from the Production.Product table of the AdventureWorks sample database.

If you do not have AdventureWorks, grab the SQL Server AdventureWorks scripts and run them against your server. On a Mac or Linux machine you can run SQL Server in Docker and load the same script with sqlcmd.

Install the NuGet packages

Three packages are needed — the SQL Server provider, the design-time package that powers scaffolding, and Scalar for the interactive API reference:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Scalar.AspNetCore

If you prefer the Package Manager Console in Visual Studio, the same packages install with Install-Package.

One package you no longer need: Swashbuckle. Starting with .NET 9, ASP.NET Core generates the OpenAPI document itself through Microsoft.AspNetCore.OpenApi, which the Web API template already references. Scalar just puts a UI on top of it.

Scaffold the model from the database

The dotnet ef dbcontext scaffold command reads the database schema and generates the DbContext and entity classes for you. Install the EF tool once, then scaffold only the table we need:

dotnet tool install -g dotnet-ef

dotnet ef dbcontext scaffold \
  "Server=localhost;Database=AdventureWorks2017;Trusted_Connection=True;TrustServerCertificate=True" \
  Microsoft.EntityFrameworkCore.SqlServer \
  --table Production.Product \
  --output-dir Models \
  --context AdventureWorksContext

Replace the connection string with one that matches your server — the example above uses Windows authentication; use User Id/Password for SQL authentication.

After it runs you will find two files under Models:

Product.cs — the entity mapped to Production.Product, one property per column
AdventureWorksContext.cs — the DbContext with a DbSet<Product> and the full mapping configuration

A DbContext represents a session with the database: it tracks changes, runs queries, and manages transactions. Every request that touches the database goes through it.

The scaffolder puts your connection string inside OnConfiguring and warns you about it — connection strings do not belong in source code. Delete the OnConfiguring method (and the parameterless constructor) from AdventureWorksContext.cs; we will register the context through dependency injection instead.

Add the connection string

Put the connection string in appsettings.json:

"ConnectionStrings": {
  "GeekStore": "Server=localhost;Database=AdventureWorks2017;Trusted_Connection=True;TrustServerCertificate=True"
}

For real projects keep the development connection string in appsettings.Development.json or user secrets, so credentials never reach the repository.

Register services in Program.cs

Program.cs is where the application is composed. Register the DbContext with the connection string from configuration, and set up a CORS policy so browser-based clients can call the API:

using GeekStore.API.Models;
using Microsoft.EntityFrameworkCore;
using Scalar.AspNetCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddOpenApi();

var connectionString = builder.Configuration.GetConnectionString("GeekStore");
builder.Services.AddDbContext<AdventureWorksContext>(options =>
    options.UseSqlServer(connectionString));

builder.Services.AddCors(options =>
{
    options.AddDefaultPolicy(policy =>
    {
        policy.AllowAnyOrigin()
              .AllowAnyHeader()
              .AllowAnyMethod();
    });
});

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
    app.MapScalarApiReference();
}

app.UseHttpsRedirection();
app.UseCors();
app.MapControllers();

app.Run();

A few things worth noting:

AddDbContext registers AdventureWorksContext with a scoped lifetime — one instance per HTTP request, which is exactly what a DbContext wants. More on lifetimes in dependency injection in .NET Core.
The AllowAnyOrigin policy is fine for local development. For production, lock it down to the origins that actually call your API — see CORS policies in ASP.NET Core Web API.
AddControllers + MapControllers register the controller framework and wire up attribute routing.

Add the Products controller

The controller is the entry point of the API — it receives the HTTP request and returns the response. Add Controllers/ProductsController.cs:

using GeekStore.API.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace GeekStore.API.Controllers;

[Route("api/[controller]")]
[ApiController]
public class ProductsController(AdventureWorksContext context) : ControllerBase
{
    [HttpGet]
    public async Task<List<Product>> GetAllProducts()
    {
        return await context.Products.AsNoTracking().ToListAsync();
    }

    [HttpGet("{id:int}")]
    public async Task<ActionResult<Product>> GetProduct(int id)
    {
        var product = await context.Products.FindAsync(id);
        return product is null ? NotFound() : product;
    }
}

The pieces:

The primary constructor (ProductsController(AdventureWorksContext context)) receives the DbContext from dependency injection — no field and constructor boilerplate needed.
Both actions are async. Database calls are I/O; await-ing them keeps request threads free while SQL Server does its work.
AsNoTracking() tells EF Core not to set up change tracking for the results. For read-only queries this is a cheap and worthwhile speedup.
GetProduct returns 404 Not Found when the id does not exist, instead of an empty 200 — clients can rely on the status code.

Run and test with Scalar

Start the API:

dotnet run --project GeekStore.API

Open /scalar/v1 in the browser. Scalar reads the generated OpenAPI document and gives you an interactive reference for every endpoint — pick GET /api/Products and click Test Request to execute it against your running API.

You can also call the endpoint directly:

curl http://localhost:5210/api/products

[
  {
    "productId": 680,
    "name": "HL Road Frame - Black, 58",
    "productNumber": "FR-R92B-58",
    "color": "Black",
    "standardCost": 1059.31,
    "listPrice": 1431.50,
    ...
  },
  ...
]

And the single-product endpoint:

curl http://localhost:5210/api/products/771
# 200 → "Mountain-100 Silver, 38", listPrice 3399.99

curl http://localhost:5210/api/products/1
# 404 Not Found

Prefer Postman for testing? The same endpoints work there — see testing ASP.NET Core Web API using Postman.

Where to go from here

You have a working Web API reading from SQL Server through EF Core. Natural next steps:

Add POST/PUT/DELETE actions to complete the CRUD surface
Return DTOs instead of entities so the API contract is independent of the database schema — the repository pattern tutorial covers this along with a clean data-access layer
Configure entity relationships explicitly with the Fluent API