Entity Framework Core 6 With Database First

Entity Framework Core 6 with Database First in .NET 6

This article will explain how to connect your .NET 6 application to an existing database using Entity Framework Core. Alongside the theory will be given a practical example where we create a new Console Application using .NET 6, this console application then will be connected to a pre-existing sample of the NorthWind DB with Entity Framework Core and the Database First approach.

If we compare Database First approach in Entity Framework Core using .NET 6 to previous .NET framework versions we can notice that some important changes were made. Before we had our database based on the .EDMX file, and had to update this .EDMX file every time we had any updates on the database. Now with the scaffold command, we create the same entities as if we created the database using the code first approach.

What is Entity Framework Core?

Entity Framework Core is Microsoft's most powerful ORM - Object Relational Mapper, that helps software applications map, connect, and manage entities to a wide range of databases. Entity Framework Core is also open source and cross-platform, being the top 1 ORM used by software engineers when working with Microsoft technologies.

At the moment of writing this article, Entity Framework Core offers two options to connect your entities to the database:

Code First, write your project's entities first and then reflect those objects in the database. This option creates the database based on the code.
Database First, needing to have your database, previously created, connect to a new application. This option created the class code based on the database model.

What is Database First?

Database first is the approach by Entity Framework used when we need to reverse engineer our database model into classes to be consumed by our application. It gives a great possibility to connect new projects with existing databases, a very important feature when software is being migrated to new technology.

The Database First approach is capable of translating existing objects in your database into classes in your project. Database First is supported by all the Entity Framework Core providers and is able to perfectly reflect data fields, data types, and table relationships from a wide range of data sources into your project language.

When you need to connect an existing database to a brand new project you should definitely consider the Database First approach to spare time reflecting those classes into your project.

Benefits of using Database First

The main benefit of the Database First approach is that we can connect new projects to existing databases, no matter if this pre-existing database was being used by different technologies. Also, we could start a project and split the responsibilities to model the database and design the project architecture to be executed in parallel by different teams, for later we connect the project with the created database using the database first approach.

If after successfully have created the entities in your project using the Database first approach and you realize that need to update any object on your project and/or database then we have the following options:

If you change your code and want it to reflect on the database must use the Migration Commands.
If you change your database and want it to reflect on your code must scaffold the database again.

Implementation Step by Step

Create the console application

Create a new Console Application targeting NET 6 framework.

Install required Nuget packages

Below you can find the Nuget packages required to run this application. Those Nuget packages are needed to scaffold and connect to our existing database.

EntityFramework
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools

Create the Northwind database

If you do not have the Northwind database running on your SQL Server then I recommend executing the script below to create it.

Northwind database creator script on GitHub.

Scaffold the Existing Database

This step is needed to create the entities classes and the DbContext class in your Console Application. Execute the following script on your Package Manager Console:

Scaffold-DbContext "Data Source=DESKTOP-H20O12E;Initial Catalog=Northwind;Integrated Security=True" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Expected output

Classes created,

Made code changes and need to update the database?

If you need to change the database based on code changes then you can use the Migration Commands as if we were using the Code First approach.

Connect to the database

Now we can connect and manipulate our existing database.

using EF6DatabaseFirst.Models;
Console.WriteLine("Hello, World!");
using(var db = new NorthwindContext()) {
    Console.WriteLine("Database Connected");
    Console.WriteLine();
    Console.WriteLine("Listing Category Sales For 1997s");
    db.CategorySalesFor1997s.ToList().ForEach(x => Console.WriteLine(x.CategoryName));
    Console.WriteLine();
    Console.WriteLine("Listing Products Above Average Prices");
    db.ProductsAboveAveragePrices.ToList().ForEach(x => Console.WriteLine(x.ProductName));
    Console.WriteLine();
    Console.WriteLine("Listing Territories");
    db.Territories.ToList().ForEach(x => Console.WriteLine(x.TerritoryDescription));
}

Congratulations, you have successfully connected to an existing database using .NET 6 and Entity Framework.

Project on GitHub.

External References

Entity Framework Tutorial