Core API Entity Framework Core Setup

So far, the BookStore API has stored books in a static in-memory list. That data disappears every time the application restarts. This topic connects the API to a real SQL Server database using Entity Framework Core (EF Core) — an object-relational mapper (ORM) that lets C# code work with database tables using objects and LINQ instead of raw SQL.

What Is an ORM?

An ORM (Object-Relational Mapper) translates between C# objects and database tables. Instead of writing SQL like SELECT * FROM Books WHERE Id = 1, EF Core lets the code read: dbContext.Books.Find(1). EF Core writes the SQL behind the scenes.

C# Code (EF Core)                →    SQL (Generated by EF Core)
───────────────────────────────────────────────────────────────────
dbContext.Books.ToList()         →    SELECT * FROM Books
dbContext.Books.Find(1)          →    SELECT * FROM Books WHERE Id = 1
dbContext.Books.Add(newBook)     →    INSERT INTO Books (Title, ...) VALUES (...)
dbContext.Books.Remove(book)     →    DELETE FROM Books WHERE Id = 1

Step 1 – Install EF Core NuGet Packages

Three packages are needed. Run these commands in the Package Manager Console or terminal inside the project folder:

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

Step 2 – Create the DbContext

The DbContext is the bridge between the C# application and the database. It contains one DbSet<T> property for each database table. Create a Data folder and add BookStoreDbContext.cs:

// Data/BookStoreDbContext.cs
using Microsoft.EntityFrameworkCore;
using BookStoreAPI.Models;

namespace BookStoreAPI.Data
{
    public class BookStoreDbContext : DbContext
    {
        public BookStoreDbContext(DbContextOptions<BookStoreDbContext> options)
            : base(options)
        {
        }

        public DbSet<Book> Books { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure the Books table
            modelBuilder.Entity<Book>(entity =>
            {
                entity.HasKey(b => b.Id);

                entity.Property(b => b.Title)
                      .IsRequired()
                      .HasMaxLength(200);

                entity.Property(b => b.Author)
                      .IsRequired()
                      .HasMaxLength(100);

                entity.Property(b => b.Price)
                      .HasColumnType("decimal(18,2)");

                entity.Property(b => b.Category)
                      .IsRequired()
                      .HasMaxLength(50);
            });

            // Seed initial data
            modelBuilder.Entity<Book>().HasData(
                new Book { Id = 1, Title = "Clean Code", Author = "Robert C. Martin",
                           Price = 29.99m, Category = "Technology",
                           IsAvailable = true, CreatedDate = new DateTime(2024, 1, 1) },
                new Book { Id = 2, Title = "The Pragmatic Programmer", Author = "David Thomas",
                           Price = 34.99m, Category = "Technology",
                           IsAvailable = true, CreatedDate = new DateTime(2024, 1, 1) }
            );
        }
    }
}

Step 3 – Add the Connection String

The connection string tells EF Core which database server to connect to and which database to use. Add it to appsettings.json:

{
  "ConnectionStrings": {
    "BookStoreDB": "Server=(localdb)\\mssqllocaldb;Database=BookStoreDB;Trusted_Connection=True;TrustServerCertificate=True;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Connection String Breakdown

Step 4 – Register DbContext in Program.cs

// Program.cs
using BookStoreAPI.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Register EF Core with SQL Server
builder.Services.AddDbContext<BookStoreDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("BookStoreDB")));

var app = builder.Build();

Step 5 – Create and Apply Migrations

Migrations track database schema changes in code. When a model changes (e.g., a new column is added), a migration records that change and can apply it to the database.

Run these two commands in the Package Manager Console (Tools → NuGet Package Manager → Package Manager Console in Visual Studio):

Add-Migration InitialCreate
Update-Database

Or using the .NET CLI in the terminal:

dotnet ef migrations add InitialCreate
dotnet ef database update

What These Commands Do

Migration Flow Diagram

[ Book.cs Model ] ──── EF Core reads properties ────▶ [ Migration File ]
                                                              |
                                                             ▼
                                                   [ Update-Database ]
                                                              |
                                                             ▼
                                                   [ SQL Server Database ]
                                                    Table: Books
                                                    ├── Id (int, PK)
                                                    ├── Title (nvarchar 200)
                                                    ├── Author (nvarchar 100)
                                                    ├── Price (decimal 18,2)
                                                    ├── Category (nvarchar 50)
                                                    ├── IsAvailable (bit)
                                                    └── CreatedDate (datetime2)

Verifying in SSMS

Open SQL Server Management Studio (SSMS). Connect to (localdb)\mssqllocaldb. The BookStoreDB database should be visible with a Books table containing the two seeded rows.

What Changes in the Project Structure

BookStoreAPI/
├── Controllers/
│   └── BooksController.cs
├── Data/                          ← NEW
│   └── BookStoreDbContext.cs      ← NEW
├── Migrations/                    ← GENERATED by EF Core
│   ├── 20240115_InitialCreate.cs
│   └── BookStoreDbContextModelSnapshot.cs
├── Models/
│   └── Book.cs
├── Services/
│   ├── IBookService.cs
│   └── BookService.cs
├── appsettings.json               ← Connection string added
└── Program.cs                     ← DbContext registered

Key Points

• EF Core is an ORM that translates C# code into SQL queries automatically.
• The DbContext class is the bridge between the app and the database, with DbSet<T> properties representing tables.
• The connection string in appsettings.json tells EF Core which database server and database to use.
• Migrations are code files that track database schema changes and apply them using Update-Database.
• Seed data in OnModelCreating populates the database with initial rows on first migration.