.NET 6 - Operate database by EFCore

Table of Contents

The Entity Framework (ADO.NET Entity Framework) is a set of technologies in ADO.NET. In the following article, I will introduce two methods, Database first and Code first, to Connect SQL Server DB in ASP.NET6 Web API by Entity Framework Core. Either one can achieve our goal. Let’s start!

DB First

  1. Add Model

    • Package required Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Tools package

    • Open Package Manager Console Open package manager console

    • Use Scaffold-DbContext to generate code for a DbContext and entity types from database.

      Scaffold-DbContext "Server=localhost\SQLEXPRESS;Database=AccountingCF;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

      This will create a class which extends DbContext

      public partial class AccountingCFContext : DbContext
{
    public NorthwindContext() {}

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

    public virtual DbSet<Record> Records { get; set; } = null!;

    modelBuilder.Entity<Record>(entity => {...}); 
}

      Also, Entity type Record, which is a table in database.

      public partial class Record
{
    public int RecordId { get; set; }
    public string Title { get; set; } = null!;
    public DateTime Date { get; set; }
    public int Genre { get; set; }
    public string? Description { get; set; }
    public int Amount { get; set; }
}

  2. Add ConnectionStrings

    • Package required Microsoft.EntityFrameworkCore package
    • Add ConnectionStrings in appsettings.json Set the database’s server location and the database’s name.
      "ConnectionStrings": {
    "DefaultConnection": "Server=localhost\\SQLEXPRESS;Database=AccountingCF;Trusted_Connection=True;"
}
    • Add db connection in Program.cs
      builder.Services.AddDbContext<AccountingCFContext>(options =>
       options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

  3. Add Controller

[Route("api/[controller]")]
[ApiController]
public class RecordController : ControllerBase
{
    private readonly AccountingCFContext _dataContext;
    public RecordController(AccountingCFContext context)
    {
        _dataContext = context;
    }

    [HttpGet]
    public IEnumerable<Record> GetRecords()
    {
        return _dataContext.Record.ToList();
    }
}

Code First

  1. Add Models Folder create DbContext and Model class

    • Record.cs
      public class Record
{
    public int RecordId { get; set; }
    public string Title { get; set; } = string.Empty;
    public DateTime Date { get; set; } = DateTime.Now;

    public RecordGenre Genre { get; set; }
    public string? Description { get; set; }
    public int Amount { get; set; }
}

public enum RecordGenre
{
    Income,
    Expense
}
    • Accounting.cs
      using Microsoft.EntityFrameworkCore;

public class AccountingContext : DbContext
{
    public AccountingContext(DbContextOptions<AccountingContext> options) : base(options) {}
    public DbSet<Record> Record { get; set; }
}

  2. ConnectionStrings

    • package required Microsoft.EntityFrameworkCore package Microsoft.EntityFrameworkCore.SqlServer package

    • Add ConnectionStrings in appsettings.json Set the database’s server location and the database’s name.

      "ConnectionStrings": {
    "DefaultConnection": "Server=localhost\\SQLEXPRESS;Database=AccountingCF;Trusted_Connection=True;"
}

    • Add db connection in Program.cs

      builder.Services.AddDbContext<NorthwindContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

  3. Add Migration

    • Package required package required
    • Add-Migration InitialCreate Enter command Add-Migration InitialCreate After the command is excuted, a new folder Migrations will be created. migration folder
    • Look into class InitialCreate in InitialCreate.cs There is an Up method to create table and a Down method to drop table.
      public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Record",
            columns: table => new
            {
                RecordId = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Title = table.Column<string>(type: "nvarchar(max)", nullable: false),
                Date = table.Column<DateTime>(type: "datetime2", nullable: false),
                Genre = table.Column<int>(type: "int", nullable: false),
                Description = table.Column<string>(type: "nvarchar(max)", nullable: true),
                Amount = table.Column<int>(type: "int", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Record", x => x.RecordId);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Record");
    }
}

  4. Execute Update-Database.

    • The command will create the database if it is not exsit. Update-Database command
    • Open the SQL Server management to see the created database. database in sqlserver

Reference