Introduction
Connecting a .NET Web API to SQL Server is a common requirement for modern applications. Entity Framework Core (EF Core) makes this process easier by allowing you to interact with the database using C# classes instead of raw SQL commands. With EF Core, you can create tables, update schemas, and run queries using LINQ — all while keeping your code clean and maintainable.
In this article, we will walk through every step required to connect a .NET Web API to SQL Server using EF Core in simple, clear, and beginner‑friendly language.
Step 1: Create a New .NET Web API Project
Run the following command:
dotnet new webapi -n MyApiProject
cd MyApiProject
This creates a basic Web API project with default controller setup.
Step 2: Install Required Entity Framework Core Packages
To use SQL Server with EF Core, install these NuGet packages:
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
Migration tools:
dotnet add package Microsoft.EntityFrameworkCore.Tools
Optional for lazy loading:
dotnet add package Microsoft.EntityFrameworkCore.Proxies
Step 3: Create a Database Model (Entity Class)
Create a folder named Models, then add a class:
namespace MyApiProject.Models
{
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
}
This class represents a table in SQL Server.
Step 4: Create the Database Context Class
Create a folder named Data, then add:
using Microsoft.EntityFrameworkCore;
using MyApiProject.Models;
namespace MyApiProject.Data
{
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
public DbSet<Product> Products { get; set; }
}
}
Step 5: Configure SQL Server Connection in appsettings.json
Open appsettings.json and add:
{
"ConnectionStrings": {
"DefaultConnection": "Server=YOUR_SERVER_NAME;Database=MyDb;Trusted_Connection=True;TrustServerCertificate=True;"
}
}
Replace YOUR_SERVER_NAME with:
Step 6: Register DbContext in Program.cs
Open Program.cs and add:
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
This tells .NET to use SQL Server via Entity Framework Core.
Step 7: Create the Database Using EF Core Migrations
Run migration commands:
dotnet ef migrations add InitialCreate
Then create the database:
dotnet ef database update
SQL Server now contains a new table named Products.
Step 8: Create a CRUD Controller for Products
In the Controllers folder, add:
using Microsoft.AspNetCore.Mvc;
using MyApiProject.Data;
using MyApiProject.Models;
using Microsoft.EntityFrameworkCore;
namespace MyApiProject.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
private readonly AppDbContext _context;
public ProductsController(AppDbContext context)
{
_context = context;
}
[HttpGet]
public async Task<IActionResult> GetProducts()
{
var products = await _context.Products.ToListAsync();
return Ok(products);
}
[HttpGet("{id}")]
public async Task<IActionResult> GetProduct(int id)
{
var product = await _context.Products.FindAsync(id);
if (product == null)
return NotFound();
return Ok(product);
}
[HttpPost]
public async Task<IActionResult> CreateProduct(Product product)
{
_context.Products.Add(product);
await _context.SaveChangesAsync();
return CreatedAtAction(nameof(GetProduct), new { id = product.Id }, product);
}
[HttpPut("{id}")]
public async Task<IActionResult> UpdateProduct(int id, Product updated)
{
var product = await _context.Products.FindAsync(id);
if (product == null)
return NotFound();
product.Name = updated.Name;
product.Price = updated.Price;
await _context.SaveChangesAsync();
return NoContent();
}
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteProduct(int id)
{
var product = await _context.Products.FindAsync(id);
if (product == null)
return NotFound();
_context.Products.Remove(product);
await _context.SaveChangesAsync();
return NoContent();
}
}
}
This controller handles GET, POST, PUT, and DELETE operations for the Product table.
Step 9: Test the API Using Postman or Swagger
Run the API:
dotnet run
Open browser:
https://localhost:5001/swagger
You can now test all API endpoints directly.
Step 10: Optional – Seed Initial Data
Open Program.cs:
using (var scope = app.Services.CreateScope())
{
var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
if (!db.Products.Any())
{
db.Products.AddRange(
new Product { Name = "Laptop", Price = 50000 },
new Product { Name = "Mobile", Price = 20000 }
);
db.SaveChanges();
}
}
This inserts sample data into SQL Server when the API starts.
Common Errors and Fixes
1. Cannot connect to SQL Server
2. Migrations command not found
Install the tools globally:
dotnet tool install --global dotnet-ef
3. Model not found in database
Run:
dotnet ef database update
Conclusion
Connecting a .NET Web API to SQL Server using Entity Framework Core is simple once you understand the key steps: configuring connection strings, setting up DbContext, creating models, and running EF Core migrations. With EF Core, you write clean and maintainable code while letting the framework manage the database structure for you. By following the steps in this guide, you can build powerful and scalable APIs backed by SQL Server.