DbContext and Entities

After this lesson you can

• Write entity classes and a DbContext with a DbSet for each table of an existing database
• Register a DbContext with AddDbContext and UseSqlServer, and ask for it in a controller

Key takeaways

• An entity is a class EF Core maps to a table: one object per row, and one property per column with the same name.
• By convention, a property named Id, or the class name plus Id such as ProductId, is the primary key.
• Property types must match the columns: a nullable column needs a nullable property such as int?, or a null throws an exception.
• A DbContext stands for the database, with a DbSet<T> property for each table, such as DbSet<Product> Products.
• Register the context with AddDbContext and UseSqlServer, and a controller asks for it in its constructor, as it asked for a DAO.

EF Core needs two things from you: classes that describe the tables, and a class that describes the database.

The package

Add the Microsoft.EntityFrameworkCore.SqlServer package (it brings EF Core and the SQL Server provider). As with Microsoft.Data.SqlClient, also add a current System.Security.Cryptography.Pkcs, or the build warns about an older version with a known vulnerability:

<ItemGroup>
  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="10.0.14" />
  <PackageReference Include="System.Security.Cryptography.Pkcs" Version="10.0.14" />
</ItemGroup>

Entities

An entity is a class EF Core maps to a table: one object per row, one property per column. They're the same plain classes you used with Dapper:

using System.ComponentModel.DataAnnotations.Schema;

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public int? CategoryId { get; set; }

    [Column(TypeName = "money")]
    public decimal? UnitPrice { get; set; }

    public short? UnitsInStock { get; set; }
    public bool Discontinued { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; } = "";
    public string? Description { get; set; }

    public List<Product> Products { get; set; } = new List<Product>();
}

EF Core works most things out by convention:

• The key: a property named Id, or the class name plus Id (ProductId), is the primary key.
• The columns: each property maps to the column with the same name. Columns you leave out, such as QuantityPerUnit, are simply ignored.
• The types: C# types match SQL types: int? for a nullable int column, short? for a nullable smallint, bool for bit. Get these right: a null in a column whose property can't hold one throws an exception.
• Relationships: Category.Products and Product.CategoryId tell EF Core that a category has many products, joined on CategoryId. A property like Products is called a navigation property.

Where a convention isn't enough, an attribute says more. [Column(TypeName = "money")] tells EF Core the column is SQL Server's money type, so it stores prices correctly. ([Table("Products")] and [Key] exist too, for names that don't follow the conventions.)

The DbContext

A DbContext stands for the database. Derive your own, with a DbSet<T> property for each table:

using Microsoft.EntityFrameworkCore;

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

    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();
}

• The DbSet<Product> named Products maps to the Products table. Everything you do with products goes through it: queries, adds, removes.
• The constructor takes the options, which say which database to use and how to reach it, and passes them to DbContext.

Registering it in ASP.NET Core

In Program.cs, AddDbContext registers the context, and UseSqlServer gives it the connection string:

string connectionString = builder.Configuration.GetConnectionString("Northwind")!;
builder.Services.AddDbContext<NorthwindContext>(options => options.UseSqlServer(connectionString));

The context is registered scoped, like your DAOs: one per request, with its own connection, thrown away when the request ends.

A controller asks for it in its constructor, as it asked for a DAO:

[ApiController]
[Route("api/products")]
public class ProductsController : ControllerBase
{
    private readonly NorthwindContext db;

    public ProductsController(NorthwindContext db)
    {
        this.db = db;
    }

    [HttpGet("count")]
    public async Task<int> Count()
    {
        return await db.Products.CountAsync();
    }
}

GET /api/products/count answers 77. In fact, the DbContext plays the DAO's part: it's the class that talks to the database.

Trying it in a console app

Outside ASP.NET Core, build the options yourself and create the context with new. It's handy for experiments:

using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
DbContextOptions<NorthwindContext> options = new DbContextOptionsBuilder<NorthwindContext>()
    .UseSqlServer(connectionString)
    .Options;

using NorthwindContext db = new NorthwindContext(options);

Console.WriteLine(quot;{await db.Products.CountAsync()} products");
Console.WriteLine(quot;{await db.Categories.CountAsync()} categories");

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public int? CategoryId { get; set; }

    [Column(TypeName = "money")]
    public decimal? UnitPrice { get; set; }

    public short? UnitsInStock { get; set; }
    public bool Discontinued { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; } = "";
    public string? Description { get; set; }

    public List<Product> Products { get; set; } = new List<Product>();
}

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

    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();
}

77 products
8 categories

using disposes the context when the program ends, closing its connection, just like a SqlConnection.

Check yourself

2 questions to try on the lesson page.

So what

Entities describe the rows, the DbContext describes the database, and AddDbContext hands it to whoever asks. With those three pieces in place, every table is a DbSet you can query with LINQ.

Key terms

• Entity
• DbContext
• DbSet
• Primary Key Convention
• Navigation Property

Cheat sheets

• DbContext and Entities