Entity Framework Core Database First

In this post, I will create simple example EF Core project wit Database First approach. If you don’t know what is Entity Framework or Database First approach you can read my Entity Framework story.

Prerequisites

• Visual Studio
• Microsoft SQL Server Management Studio

The sections of this post will be as follows:

• What is ASP.NET Core MVC ?
• Creating MVC project in Visual Studio
• Database and Northwind
• Scaffold
• Db Context
• CRUD Operations

What is ASP.NET Core MVC ?

ASP.NET Core MVC is a rich framework for building web apps and APIs using the Model-View-Controller design pattern.

The Model-View-Controller (MVC) architectural pattern separates an application into three main groups of components: Models, Views, and Controllers. Every component has different responsibilities. We don’t let one component do all work.

Creating MVC Project:

Developer can use different IDE for creating MVC project. We will use Visual Studio for creating MVC project. In Visual Studio you will see a section name “Get Started”. We can create a new project, open existing project or clone project from GitHub. After click “Create a new project” we can select a Web Application as show below.

After selected this template, we can give any project name we want. Let’s say “ExampleProject”. There is one step left before we create a MVC project.

Choose API template project because in CRUD operations section I will show how to use API. Now we finished last step of creating MVC project.

Database and Northwind

Firstly, before begin this section make sure you installed Microsoft SQL Server Management Studio. For Database First Approach, we should design a database, tables and relations. It can be take long time designing new tables and relations. Most developers already heard about Northwind. The Northwind database is a sample database used by Microsoft to demonstrate the features of some of its products, including SQL Server. The database contains the sales data for Northwind Traders. This component loads the complete Northwind database in your environment. You can use this to understand how to bootstrap entities from excel and also practice writing queries (aggregates & advanced SQL) on this database.

You can read how to import Northwind to your local SQL Express Server by clicking here.

Scaffold

Now we have our database table and relations in SQL server. Database First approach now start here. First we finish our database design and now we want to implement this tables to our project as Entity. Before make Scaffold operations we have to install some NuGet from NuGet Package or Package Manager Console.

• Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 5.0.5
• Install-Package Microsoft.EntityFrameworkCore -Version 6.0.0-preview.3.21201.2

Here latest version of our requirements for connecting our local database

Now we are ready for creating Entities for our tables.

"Server={YourDomain}\SQLExpress;Database=Northwind;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context "AutoTempContext" -DataAnnotations

Db Context

Context class is the most important class for connection with database. We creating a session connection between application layer and database with this context class. The context class in Entity Framework is a class which derives from “System.Data.Entity.DbContextDbContext” in EF 6 and EF Core both. An instance of the context class represents Unit Of Work and Repository patterns wherein it can combine multiple changes under a single database transaction.

This is how our DbContext class must show. DbSet<TableName> represent every table as a Entity(Model) in our application.

All tables and relations in “Models” folder. Now we got a one class for every table in database. We mentioned about DbContext create session between application and database but how can we create this connection ? Is it done now ? No. When you run application, you must create this connection from Startup.cs. Open Startup.cs file and you will see ConfigureServices method.

We can add our Db Context inside of this method with connection string (change server name). Everytime this project run, we take an instance from database with using DbContext.

CRUD Operations

CRUD is an acronym worth knowing. It stands for Create, Read, Update, and Delete; CRUD. It is one of the things students of software learn very early and it is also fundamentally important for non-techies to know as well.

It sounds complex but let’s distill something about HTTP and relate it back to CRUD. When a request is made to a website the HTTP protocol is used and there are a number of request types. The most used ones are GET, PUT, POST, and DELETE. Now, guess what, these are the standard CRUD operations!

For using this CRUD operations, we already created an API project. Now we can create our controller class. In our solution, right-click on Controllers folder and select new item and new class.

You can give any name you want to this class. I say Crud.cs.

Now we have an empty public class. Sometimes developers have to create more than 100 controller class. This means a lot of code writen writen and wirten again. For prevent repeating this codes again and again, Asp.Net Core provides us an interface named “ControllerBase”. We can inherit this class from our custom class.

Also, while we creating APIs for our class. Do you think how can we reach this APIs. There should be an address for APIs. We can define a route for our custom APIs.

We defined our router for APIs in this class as a “api/v1/crud”.

Now, we know address but, we didn’t call our famous DbContext in this class. I already told you that DbContext is our session between database, so we need call DbContext whenever we want to reach database.

We define private property as a “_context” and we fill it whenever Crud class call. Let’s try it with Get method.

Remember our “AutoTempContext” class. Scaffold automatically defined DbSet in this class. Customer is one of these DbSet. We took instance of DbContext and DbContext has all this DbSets. After you took an instance from DbContext you can reach all DbSets and you can make CRUD operations. Here in this method, by using “_context” which carry DbContext inside, we reached Customers DbSet and keep all customers in a list.

After you run project, a browser will open and url will be start like “localhost…”. This means you can only reach this address in your computer.

We want all customers list and we already define our API route. If we changed url as “localhost…./api/v1/crud” we can see all Customers in a list.

If we want to create an entity and insert into our table here an example method:

Now you see how DbContext is important for CRUD operations. DbContext contains “Add,Delete,Update,Read” functions. This functions changes entry’s state. If we use “_context.Shippers.Add(entry)”, we change entry’s state to “Added”. When we use “SaveChangesAsync” method, Mssql check our entry’s state and decide what it gonna be. Is it update ? Is it insert ? State is Added, so that means it is an insert operation. Entry inserted into our table and now we have an id for this entry. If we try to find this entry, we can use Find(method) with id.