Entity Framework Core Database First Tutorial

Create Northwind Database

Northwind is a sample relational database provided by Microsoft, we will be using it for this tutorial to create it's Entity Framework Core Models.

You can download Northwind database schema scripts here and execute it in SQL Server Management Studio(SSMS). This will create a new database with name the Northwind.

Create New .Net Core Class Library

For this step, you will use .Net Core class Library as a separate project to handle Models and DBContext. Open your Visual Studio and follow below steps to create .Net Core Class Library. You can also have repository pattern with Web API

1. Open Visual Studio
2. On the File menu point to New and then click Project. The New Project dialog box will open.
3. On the left side of the dialog box select Installed -> Visual C# -> .NET Core.
4. On the right side of the dialog box select Class Library (.Net Core).
5. Select your path to store project and enter project name as EfCoreDBFirst
6. Click OK. A new project will be created.

Install Nuget Packages

In this step, you will install the required NuGet packages to create models from existing SQL Server database. Click on Tools menu from Visual Studio -> NuGet Package Manager -> Package Manager Console. And execute below commands to install packages.

1. Install-package Microsoft.EntityFrameworkCore
2. Install-package Microsoft.EntityFrameworkCore.SqlServer
3. Install-package Microsoft.EntityFrameworkCore.Tools

This will install latest stable nuget packages to EfCoreDBFirst .Net Core class library.

Create Models using Scaffold-DbContext

Entity FrameworkCore does not support visual designer or edmx. So Scaffold-DbContext is the command which will create entity and DBContext classes from an existing database. Scaffold-DbContext accepts different parameters along with the connection string of the database.

Create a new folder to place Entity and DBContext folder with name as Models -> DB.

Open Package Manager Console from Tools-> Nuget Package Manager -> Package Manager Console and execute below command.

Scaffold-DbContext "Data Source=(localdb)\ProjectsV13;Initial Catalog=Northwind;
Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;" 
-Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models/DB

This creates entity classes for each table from the Northwind database and NorthwindContext class having virtual properties of tables to hold the table's data. Tables having the primary key will be generated.

Configure Data Context

Scaffold-DbContext generates NorthwindContext class which inherits from DbContext. This has an instance of DbContextOptions which carries information of configuration.

NorthwindContext.cs class has the OnConfiguring method to configure EF with Northwind database. and OnModelCreating having table relationships and constraints configured with Fluent API.

NorthwindContext has generated code as shown

namespace entity_framework_core_database_first_tutorial.Models.DB
{
public partial class NorthwindContext : DbContext
{
public NorthwindContext()
{
}

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

public virtual DbSet<Categories> Categories { get; set; }        
public virtual DbSet<Customers> Customers { get; set; }
//other table's virtual  properties to hold their corresponding data

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
    optionsBuilder.UseSqlServer("Data Source=(localdb)\\ProjectsV13;
    Initial Catalog=Northwind;Integrated Security=True;
    Connect Timeout=30; 
    Encrypt=False;TrustServerCertificate=False;");
}
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasAnnotation("ProductVersion", "2.2.4-servicing-10062");

modelBuilder.Entity<Categories>(entity =>
{
    entity.HasKey(e => e.CategoryId);

    entity.HasIndex(e => e.CategoryName)
        .HasName("CategoryName");

    entity.Property(e => e.CategoryId).HasColumnName("CategoryID");

    entity.Property(e => e.CategoryName)
        .IsRequired()
        .HasMaxLength(15);

    entity.Property(e => e.Description).HasColumnType("ntext");

    entity.Property(e => e.Picture).HasColumnType("image");
});

    //Other tables Fluent API configuration
}
}
}

Registering DBContext with Dependency Injection

Notice OnConfiguring method from above code, the connection string is tightly coupled. you can not change it through config or appsettings file. You can implement this to configure your database connection string in the appsettings.json file.

Scaffold-DbContext options

You can use different parameters with Scaffold-DbContext to customize model creation from an existing database. Scaffold-DbContext accepts the below parameters.

• -Connection <String>: specifies the connection string of database.
• -Provider <String>: Entity Framework Core supports SqlServer, Sqlite, InMemory, Cosmos and many more. You may check for a full list of EF Core Providers. For SQL Server you can use like

  Scaffold-DbContext "<connection string>" 
      -Provider Microsoft.EntityFrameworkCore.SqlServer 
  

• -OutputDir <String>: Specifies directory name to create model files. <string> is relative to project directory.

  Scaffold-DbContext "<connection string>" 
      -Provider Microsoft.EntityFrameworkCore.SqlServer
      -OutputDir Models/DB 
  

• -ContextDir <String>: specifies directory names to the place Context file.

  Scaffold-DbContext "<connection string>"        
      -OutputDir Models/DB
      -ContextDir Models/Context
  

• -Context <String> : specifies DBContext name to be used. For this tutorial, it generated NorthwindContext by default which you can change by using -Context. You can create multiple context using different/same set of tables.

  Scaffold-DbContext "<connection string>"
      -Context NWContext
  

• -Schemas <String[]>: accepts string array having Schema names. You can use specific schema names for which models need to be created.

  Scaffold-DbContext "<connection string>"
      -Schemas dbo, admin
  

• -Tables <String[]>: accepts string array having table names for which model classes to be created. Sometimes you do not need to create models for all tables. SQL table must have a primary key to generate entity class using Scaffold-DbContext.

  Scaffold-DbContext "<connection string>"
      -Tables Categories, Products, Customers
  

• DataAnnotations: if DataAnnotations need to be implemented then you need to mention this option with scaffold-dbcontext. This will create all model classes and it's properties with annotated. If you omit this option classes will be configured using Fluent API.

  Scaffold-DbContext "<connection string>" 
      -Provider Microsoft.EntityFrameworkCore.SqlServer
      -DataAnnotations      
  

• -Force: when you use -Force existing models will get override by the new implementation.

      Scaffold-DbContext "<connection string>" 
          -Provider Microsoft.EntityFrameworkCore.SqlServer
          -Force      
  

Update existing models

For updating models depending on the schema changes in database you can use -Force option to override and recreate models with the latest version of database schema.

Scaffold-DbContext "<connection string>" 
    -Provider Microsoft.EntityFrameworkCore.SqlServer
    -Tables Customers, Orders, Products
    -Force