2 Querying the database

This chapter is all about using EF Core for reading, called querying, the database. You’ll create a database that contains the three main types of database relationships found in EF Core. Along the way, you’ll learn to create and change a database’s structure via EF Core.

Next you’ll learn how to access a database via EF Core, reading data from the database tables. You’ll explore the basic format of EF Core queries before looking at various approaches to loading related data with the main data; for instance, loading the author with the book from chapter 1.

After learning the ways to load related data, you’ll start to build the more complex queries needed to make a book-selling site work. This covers sorting, filtering, and paging, plus approaches to combine each of these separate query commands to create one composite database query.

2.1 Setting the scene—our book-selling site

In this chapter, you’ll start building the example book-selling site, referred to as the book app from now on. This example application provides a good vehicle for looking at relationships in queries. This section introduces the database, the various classes, and EF Core parts that the book app needs to access the database.

2.1.1 The book app’s relational database

2.1.2 Other relationship types not covered in this chapter

2.1.3 The final database showing all the tables

2.1.4 The classes that EF Core maps to the database

2.2 Creating the application’s DbContext

2.2.1 Defining the application’s DbContext: EfCoreContext

2.2.2 Creating an instance of the application’s DbContext

2.2.3 Creating a database for your own application

2.3 Understanding database queries

2.3.1 Application’s DbContext property access

2.3.2 A series of LINQ/EF Core commands

2.3.3 The execute command

2.4 Loading related data

2.4.1 Eager loading: loading relationships with the primary entity class

2.4.2 Explicit loading: loading relationships after the primary entity class

2.4.3 Select loading: loading specific parts of primary entity class and any relationships

2.5 Using client vs. server evaluation: moving part of your query into software

2.5.1 Creating the display string of a book’s authors

2.5.2 Understanding the limitations of client vs. server evaluation

2.6 Building complex queries

2.6.1 Building the book list query by using select loading

2.6.2 Introducing the architecture of the book app

2.7 Adding sorting, filtering, and paging

2.7.1 Sorting books by price, publication date, and customer ratings