ASP.NET Core MVC REST API with SQL Server & Entity Framework

In this tutorial, we’ll build a complete REST API using ASP.NET Core MVC with SQL Server and Entity Framework Core. We’ll cover setting up the project, creating a model, controller, and connecting to the database.

🧰 Step 1: Create ASP.NET Core MVC Project

dotnet new webapi -n DotNetRestApi

This creates a Web API project with default REST setup.

📦 Step 2: Add Entity Framework SQL Server

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

🧾 Step 3: Create Model Class

Inside the Models folder, create User.cs:

public class User
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}

🗃️ Step 4: Create Database Context

Inside Data/ApplicationDbContext.cs:

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

    public DbSet<User> Users { get; set; }
}

⚙️ Step 5: Configure Database Connection

In appsettings.json:

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

Update Program.cs:

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// ✅ Add services to the container
builder.Services.AddControllers(); // Use controllers for REST API
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// ✅ Register EF Core DbContext
builder.Services.AddDbContext(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// ✅ Register CORS if needed
builder.Services.AddCors(options =>
{
    options.AddPolicy("AllowAll",
        policy => policy.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod());
});

var app = builder.Build();

// ✅ Use middleware
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseCors("AllowAll");
app.UseAuthorization();
app.MapControllers();

app.Run();

🛠 Data/ApplicationDbContextFactory.cs (For EF CLI Support)

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;
using System.IO;

public class ApplicationDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
{
    public ApplicationDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();

        // Get config from appsettings.json
        IConfigurationRoot configuration = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory()) // Points to project root
            .AddJsonFile("appsettings.json")
            .Build();

        var connectionString = configuration.GetConnectionString("DefaultConnection");

        optionsBuilder.UseSqlServer(connectionString);

        return new ApplicationDbContext(optionsBuilder.Options);
    }
}

🛠 Step 6: Create Controller

Run this command once globally from any terminal (Command Prompt, PowerShell, or VS Code Terminal):

dotnet tool install --global dotnet-ef

After installation, you can verify by checking the version:

dotnet ef --version

💡 Note: This is required for running migrations and database update commands using Entity Framework Core CLI.

🧾 UsersController.cs – REST API Controller

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly ApplicationDbContext _context;

    public UsersController(ApplicationDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<IEnumerable<User>> GetUsers()
    {
        return await _context.Users.ToListAsync();
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<User>> GetUser(int id)
    {
        var user = await _context.Users.FindAsync(id);
        return user == null ? NotFound() : Ok(user);
    }

    [HttpPost]
    public async Task<ActionResult<User>> PostUser(User user)
    {
        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return CreatedAtAction(nameof(GetUser), new { id = user.Id }, user);
    }

    [HttpPut("{id}")]
    public async Task<IActionResult> PutUser(int id, User user)
    {
        if (id != user.Id) return BadRequest();
        _context.Entry(user).State = EntityState.Modified;
        await _context.SaveChangesAsync();
        return NoContent();
    }

    [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteUser(int id)
    {
        var user = await _context.Users.FindAsync(id);
        if (user == null) return NotFound();
        _context.Users.Remove(user);
        await _context.SaveChangesAsync();
        return NoContent();
    }
}

🧱 Step 7: Run Migrations

dotnet ef migrations add InitialCreate
dotnet ef database update

📡 Step 8: Test API Endpoints

• GET /api/users – Get all users
• GET /api/users/{id} – Get user by ID
• POST /api/users – Create user
• PUT /api/users/{id} – Update user
• DELETE /api/users/{id} – Delete user

🚀 Step 9: Enable CORS

In Program.cs:

builder.Services.AddCors(options =>
{
    options.AddPolicy("AllowAll",
        policy => policy.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod());
});

app.UseCors("AllowAll");

🎯 Done! Your API is Ready

You’ve now created a full ASP.NET Core Web API with Entity Framework and SQL Server. You can test your API with Postman or connect it to Angular, React, or any frontend.

Download Code