Data Access in ASP.NET Core (Part 1: Entity Framework Core Fundamentals)

In Chapter 2, we introduced a simple ASP.NET Core application to manage blog posts, which uses a static field to store the data in memory. In many real-world applications, the data is persisted in databases – such as SQL Server, MySQL, SQLite, PostgreSQL, and so on – so we will need to access the database to implement the CRUD operations.

In this chapter, we will learn about data access in ASP.NET Core. There are many ways to access the database in ASP.NET Core, such as through ADO.NET, Entity Framework Core, and Dapper, among others. In this chapter, we will focus on Entity Framework Core, which is the most popular object-relational mapping (ORM) framework in .NET Core.

Entity Framework Core, or EF Core for short, is an open-source ORM framework that allows us to create and manage mapping configurations between the database schema and the object models. It provides a set of APIs to perform...

Technical requirements

The code example in this chapter can be found at https://github.com/PacktPublishing/Web-API-Development-with-ASP.NET-Core-8/tree/main/samples/chapter5/. You can use VS 2022 or VS Code to open the solution.

You are expected to have basic knowledge of SQL queries and LINQ. If you are not familiar with them, you can refer to the following resources:

• SQL queries: https://www.w3schools.com/sql/
• LINQ: https://learn.microsoft.com/en-us/dotnet/csharp/linq/

Why use ORM?

To operate the data in relational databases, we need to write SQL statements. However, SQL statements are not easy to maintain and are not type-safe. Every time you update the database schema, you need to update the SQL statements as well, which is error-prone. In many traditional applications, the logic is tightly coupled with the database. For example, the logic could be defined in a SQL database directly, such as stored procedures, triggers, and so on. This makes the application hard to maintain and extend.

ORM helps us to map the database schema to the object model, so we can operate the data in the database just like we operate the objects in memory. ORM can translate the CRUD operations to SQL statements, which means it is like an abstract layer between the application and the database. The data access logic is decoupled from the database, so we can easily change the database without changing the code. Also, it provides strong type safety, so we can avoid runtime...

Configuring the DbContext class

To represent the database, EF Core uses the DbContext class, which allows us to query and save data. An instance of the DbContext class maintains the database connection and maps the database schema to the object model. It also tracks the changes in objects and manages the transactions. If you are familiar with OOP, you can think of the DbContext class as a bridge between the database and the object model, just like an interface. When you query or save data, you operate the objects through the DbContext class, and EF Core will translate the operations to the corresponding SQL statements.

In this chapter, we will develop a simple application to manage invoices. This application will be used to demonstrate how to use EF Core to access the database, including how to define the database schema, how to perform CRUD operations, and how to use migrations to update the database schema.

You can follow Chapter 1 to define the API contract first. The API...

Implementing CRUD controllers

In this section, we will implement the controllers to handle the HTTP requests, which are the GET, POST, PUT, and DELETE operations that are used to retrieve, create, update, and delete data, respectively.

Creating the controller

If you have installed the dotnet aspnet-codegenerator tool following Chapter 2, you can use the following command to create a controller with the specific DbContext. Do not forget to install the Microsoft.VisualStudio.Web.CodeGeneration.Design NuGet package, which is required by the dotnet aspnet-codegenerator tool:

# Install the tool if you have not installed it yet.#dotnet tool install -g dotnet-aspnet-codegenerator
dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design
dotnet-aspnet-codegenerator controller -name InvoicesController -api -outDir Controllers ––model Invoice ––dataContext InvoiceDbContext -async -actions

The preceding command has some parameters as shown here...

Basic LINQ queries

This book is not intended to be a LINQ handbook. However, we will show you some basic LINQ queries in this section:

• Querying the data
• Filtering the data
• Sorting the data
• Paging the data
• Creating the data
• Updating the data
• Deleting the data

Querying the data

The DbSet<Invoice> Invoices property in the InvoiceDbContext class represents a collection of the Invoice entity. We can use LINQ methods to query the data. For example, we can use the ToListAsync() method to retrieve all the invoices from the database:

var invoices = await _context.Invoices.ToListAsync();

That is how the GetInvoices action method works.

To find a specific invoice, we can use the FindAsync() method, as shown in the GetInvoice() action method:

var invoice = await _context.Invoices.FindAsync(id);

The FindAsync() method accepts the primary key value as the parameter. EF Core will translate the FindAsync() method to the SQL SELECT statement...

Configuring the mapping between models and database

ORM, as the name suggests, is used to map the objects to the relational database. EF Core uses the mapping configuration to map the models to the database. In the previous section, we saw that we did not configure any mappings; however, EF Core could still map the models to the database automatically. This is because EF Core has a set of built-in conventions to configure the mappings. We can also explicitly customize the configuration to meet our needs. In this section, we will discuss the configuration in EF Core, including the following:

• Mapping conventions
• Data annotations
• Fluent API

Mapping conventions

There are some conventions in EF Core for mapping the models to the database:

• The database uses the dbo schema by default.
• The table name is the plural form of the model name. For example, we have a DbSet<Invoice> Invoices property in the InvoiceDbContext class, so the table name is Invoices...

Summary

In this chapter, we learned how to access the database using EF Core. We implemented CRUD operations using the DbContext class. We introduced some basic LINQ queries, such as query, filter, sort, create, update, and delete. We also learned how to configure the mapping using data annotations and Fluent API. With the knowledge gained in this chapter, you can build a simple application to access the database.

However, the application we built in this chapter is quite basic and only has one entity. In a real-world project, there are usually multiple entities and relationships between them.

In the next chapter, we will learn how to configure relationshiseps between entities using EF Core.