Introduction to Entity Framework Core

Database access code appears throughout web applications. Whether you're building an e-commerce platform, a blog, or the next big thing, you'll likely need to interact with a database.

Unfortunately, interacting with databases from application code is often cumbersome. Many different approaches exist for this task. For instance, something as simple as reading data from a database requires handling network connections, writing SQL statements, and processing variable results. The .NET ecosystem offers a complete suite of libraries for this purpose, ranging from low-level ADO.NET libraries to high-level abstractions like EF Core.

In this section, I'll describe what EF Core is and the problems it aims to solve. I'll cover the motivations behind using abstractions like EF Core and how they help bridge the gap between application code and databases. As part of this, I'll introduce some tradeoffs you'll make when using it in your applications, which will help you determine whether it's the right fit for your needs. Finally, we'll examine a sample EF Core mapping from application code to database to understand the main concepts of EF Core.

What Is EF Core?

EF Core is a library that provides an object-oriented way to interact with databases. It serves as an object-relational mapper (ORM) that handles communication with the database and maps database responses to .NET classes and objects, as illustrated in the figure below.

Figure 12.1 EF Core maps .NET classes and objects to database concepts like tables and rows.

Definition Using an object-relational mapper (ORM), you can manipulate databases using object-oriented concepts such as classes and objects by mapping them to database concepts like tables and columns.

EF Core is based on but differs from the existing Entity Framework library (currently at version 6.x). It was built as part of the push for cross-platform work with .NET Core but was designed with other goals in mind. Specifically, the EF Core team wanted to create a high-performance library that could work with various databases.

Many different types of databases exist, but perhaps the most commonly used family is relational databases, accessed using Structured Query Language (SQL). This is the foundation of EF Core; it can map to Microsoft SQL Server, MySQL, PostgreSQL, and many other relational databases. It even has a handy in-memory feature you can use during testing to create temporary databases. EF Core uses a provider model so that support for other relational databases can be plugged in as they become available.

This covers what EF Core is but doesn't delve into why you would use it. Why not use traditional ADO.NET libraries to access databases directly? Most arguments for using EF Core generally apply to ORMs, so what are the advantages of ORMs?

Why Use an Object-Relational Mapper?

One of the biggest advantages ORMs bring is the speed of developing applications. You can stay in the familiar object-oriented .NET world, usually without directly manipulating databases or writing custom SQL.

Suppose you have an e-commerce site and want to load product details from the database. Using low-level database access code, you would need to open a connection to the database, write the necessary SQL with correct table and column names, read data through the connection, create POCOs to hold the data, and manually set object properties, converting data to the correct format as you go. Sounds painful, right?

An ORM like EF Core handles most of this work for you. It manages database connections, generates SQL, and maps data back to your POCO objects. You only need to provide a LINQ query describing the data you want to retrieve.

ORMs act as a high-level abstraction for databases, so they can significantly reduce the amount of plumbing code needed to interact with databases. At the most basic level, they handle mapping SQL statements to objects and vice versa, but most ORMs go further and provide additional features.

An ORM like EF Core tracks which properties of objects retrieved from the database have changed. This enables you to load objects from the database by mapping from database tables, modify them in .NET code, and then ask the ORM to update the corresponding records in the database. The ORM figures out which properties have changed and issues update statements for the corresponding columns, saving you considerable work.

As often happens in software development, using an ORM has its drawbacks. One of the biggest advantages of ORMs is also their fatal weakness—they hide the database from you. Sometimes this high-level abstraction can lead to problematic database query patterns in your application. A classic example is the N+1 problem, where what should be a single database request becomes separate requests for each row in a table.

Another common drawback is performance. ORMs abstract over multiple concepts, so they inherently do more work compared to hand-crafting each piece of data access in your application. Most ORMs, including EF Core, trade some performance for developer simplicity.

That said, if you're aware of the ORM's shortcomings, you can often greatly simplify the code required to interact with databases. As with anything, use an abstraction if it works for you; otherwise, don't. If you have minimal database access requirements or need the absolute best performance, an ORM like EF Core might not be suitable.

Another approach is the best of both worlds: use an ORM for rapid development of most of your application, then fall back to lower-level APIs like ADO.NET for areas that prove to be bottlenecks. This way, you get good enough performance with EF Core, trade development time for performance, and only optimize the areas that need it.

Even if you decide to use an ORM in your application, many different ORMs are available for .NET, and EF Core is one of them. Whether EF Core is right for you depends on the features you need and the tradeoffs you're willing to make. The next section compares EF Core with Microsoft's other product, Entity Framework, but you could consider many other alternatives such as Dapper and NHibernate, each with its own tradeoffs.

When Should You Choose EF Core?

Microsoft designed EF Core as a reimagining of the mature Entity Framework 6.x (EF 6.x) ORM released in 2008. After a decade of development, EF 6.x is a stable and feature-rich ORM.

In comparison, EF Core is a relatively new project. EF Core's API is designed to be close to EF 6.x's API (though they're not identical), but the core components have been completely rewritten. You should think of EF Core as a different version from EF 6.x; upgrading directly from EF 6.x to EF Core isn't trivial.

Microsoft supports both EF Core and EF 6.x, and both will see continued improvements, so which should you choose? You need to consider many things:

• Cross-platform - EF Core 5.0 targets .NET Standard, so it can be used for cross-platform applications targeting .NET Core 3.0 or higher. Starting from version 6.3, EF 6.x is also cross-platform, with some limitations when running on .NET 5.0, such as no designer support.
• Database providers - Both EF 6.x and EF Core allow you to use pluggable providers to connect to various database types. EF Core has an ever-growing set of providers, but EF 6.x has fewer providers, especially if you want to run EF 6.x on .NET 5.0. If the database you're using doesn't have a provider, that's a bit of a problem!
• Performance - EF 6.x's performance has been a black mark on its record, so EF Core was designed to correct this. EF Core aims to be fast and lightweight, significantly outperforming EF 6.x. But it's unlikely to reach the performance of more lightweight ORMs like Dapper or hand-written SQL statements.
• Features - Features are where you'll find the biggest differences between EF 6.x and EF Core, though this difference is smaller with EF Core 5.0 than ever before. EF Core now has many features that EF 6.x doesn't have (batch statements, client-side key generation, in-memory database for testing). EF Core still lacks some features compared to EF 6.x, such as stored procedure mapping and Table per Concrete Type (TPC), but these are pending implementation due to EF Core's active development. In contrast, EF 6.x will likely only see incremental improvements and bug fixes rather than major feature additions.

Whether these tradeoffs and limitations are a problem for you largely depends on your specific application. Given these limitations, starting new with an application is much easier than trying to fix them later.

If you're developing a new ASP.NET Core application, want to use an ORM for rapid development, and don't need any unavailable features, then EF Core is a strong contender. Various other subsystems of ASP.NET Core also support it out of the box. For example, in a later chapter, you'll learn how to use EF Core with the ASP.NET Core authentication system to manage users in your application.

Before diving into the details of using EF Core in your application, I'll describe the application we'll use as a case study for this chapter. We'll cover the application and database details and how we'll use EF Core to communicate between the two.

Mapping the Database to Your Application Code

EF Core focuses on communication between the application and the database, so to showcase it, we need an application. This chapter uses a simple cooking application that lists recipes and lets you view the ingredients for a recipe. Users can browse recipes, add new recipes, edit recipes, and delete old recipes.

This is obviously a simple application, but it contains all the database interactions you need, with two entities: Recipe and Ingredient.

Figure 12.2 The cooking application lists recipes. You can view, update, and delete recipes, or create new ones.

Definition Entities are .NET classes mapped by EF Core to the database. These are classes you define, often as POCO classes, that can be used to persist and load data by mapping them to database tables via EF Core.

When interacting with EF Core, you'll primarily work with POCO entities and a database context that inherits from the EF Core DbContext class. Entity classes are the object-oriented representation of tables in the database; they represent the data you want to store in the database. You can use DbContext in your application to configure EF Core and access the database at runtime.

Note Your application might have multiple DbContext instances, and you can even configure them to integrate with different databases.

When your application first uses EF Core, EF Core creates an internal representation of the database based on the DbSet<T> properties on your application's DbContext and the entity classes themselves, as shown in the figure below.

Figure 12.3 EF Core builds an internal model of your application's data model by exploring the types in your code. It adds all types referenced in the DbSet<> properties on your application's DbContext, plus any linked types.

For your recipe application, EF Core will build a model for the Recipe class since it's exposed as a DbSet<Recipe> on AppDbContext. Additionally, EF Core will iterate through all properties on Recipe, looking for types it doesn't know about and adding them to its internal model. In your application, the ingredients collection on Recipe exposes ingredient entities as ICollection<Ingrediant>, so EF Core models the entity appropriately.

Each entity maps to a table in the database, but EF Core also maps relationships between entities. Each recipe can have many ingredients, but each ingredient (with a name, quantity, and unit) belongs to a recipe, so this is a many-to-one relationship. EF Core uses this knowledge to correctly model the equivalent many-to-one database structure.

Note Two different recipes, say fish pie and lemon chicken, might use the same ingredient, like the juice of one lemon, with the same name and quantity, but they're fundamentally two different instances. If you updated the lemon chicken recipe to use two lemons, you wouldn't want this change to automatically update the fish pie to use two lemons!

EF Core uses the internal model it builds when interacting with the database. This ensures it constructs the correct SQL to create, read, update, and delete entities.

Yes, it's time to write some code! In the next section, you'll start building the recipe application. You'll learn how to add EF Core to an ASP.NET Core application, configure the database provider, and design the application's data model.

Adding EF Core to Your Application

In this section, we'll focus on installing and configuring EF Core in your ASP.NET Core recipe application. You'll learn how to install the required NuGet packages and how to build the data model for your application. When we discuss EF Core throughout this chapter, I won't be going through how to create the application in general—I created a simple Razor Pages application as a base, nothing special.

The interaction with EF Core in the sample application happens in a service layer that encapsulates all data access outside the Razor Pages framework. This separates your concerns and makes your services testable.

Adding EF Core to an application is a multi-step process:

1 Choose a database provider; for example, PostgreSQL, SQLite, or MS SQL Server.
2 Install EF Core NuGet packages.
3 Design your application's DbContext and the entities that make up the data model.
4 Register your application's DbContext with the ASP.NET Core DI container.

5 Use EF Core to generate migrations describing your data model.
6 Apply migrations to the database to update the database schema.

Figure 12.4 Requests are processed by loading data from the database using EF Core. Interaction with EF Core is limited to RecipeService - Razor pages don't directly access EF Core.

This may seem a bit daunting already, but we'll cover steps 1-4 in this section and steps 5-6 in the next section, so it won't take long. Given this chapter's space constraints, I'll stick with EF Core's default conventions in the code I show. EF Core is much more customizable than it first appears, but I encourage you to stick with defaults when possible. In the long run, this will make your life easier.

The first step in setting up EF Core is deciding which database you want to interact with. Your client or your company's policies may dictate this, but it's still worth thinking about the choices.

Choosing a Database Provider and Installing EF Core

EF Core supports a range of databases using a provider model. EF Core's modular nature means you can program against different underlying databases using the same high-level API, and EF Core knows how to generate the necessary implementation-specific code and SQL statements.

When you start your application, you probably already have a database in mind, and you'll be happy to know EF Core covers most popular databases. Adding support for a given database involves adding the correct NuGet package to your .csproj file. For example:

• PostgreSQL - Npgsql.EntityFrameworkCore.PostgreSQL
• Microsoft SQL Server - Microsoft.EntityFrameworkCore.SqlServer
• MySQL - MySql.Data.EntityFrameworkCore
• SQLite - Microsoft.EntityFrameworkCore.Sqlite

Some database provider packages are maintained by Microsoft, some by the open-source community, and some may require paid licenses (for example, the Oracle provider), so be sure to check your requirements.

Install a database provider into your application the same way you would any other library: by adding the NuGet package to your project's .csproj file and running dotnet restore from the command line (or letting Visual Studio restore it for you automatically).

EF Core is inherently modular, so you need to install multiple packages. I'll use the SQL Server database provider with LocalDB for the recipe application, so I'll use the SQL Server package:

• Microsoft.EntityFrameworkCore.SqlServer - This is the main database provider package for using EF Core at runtime. It also contains references to the main EF Core NuGet packages.
• Microsoft.EntityFrameworkCore.Design - This contains the shared design-time components for EF Core.

The following listing shows the recipe application's .csproj file after adding EF Core packages. Remember that you add NuGet packages as PackageReference elements.

Listing 12.1 Installing EF Core in an ASP.NET Core Application

<Project Sdk="Microsoft.NET.Sdk.Web">
    <PropertyGroup>
        <TargetFramework>net5.0</TargetFramework>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference
                          Include="Microsoft.EntityFrameworkCore.SqlServer"
                          Version="5.0.0" />
        <PackageReference
                          Include="Microsoft.EntityFrameworkCore.Design"
                          Version="5.0.0" />
    </ItemGroup>
</Project>

After installing and restoring these packages, you have everything needed to start building your application's data model. In the next section, we'll create the entity classes and DbContext for your recipe application.

Building the Data Model

In the earlier section, I outlined how EF Core builds an internal model of the database from DbContext and entity models. Beyond this discovery mechanism, EF Core is very flexible in letting you define entities however you want, such as POCO classes.

Some ORMs require your entities to inherit from a specific base class, or you decorate your models with attributes to describe how to map them. As you can see in this listing, EF Core strongly prefers conventions over configuration approaches, which shows the entity classes for your application.

Listing 12.2 Defining EF Core Entity Classes

public class Recipe
{
    public int RecipeId { get; set; }
    public string Name { get; set; }
    public TimeSpan TimeToCook { get; set; }
    public bool IsDeleted { get; set; }
    public string Method { get; set; }
    public ICollection<Ingredient> Ingredients { get; set; }
}

public class Ingredient
{
    public int IngredientId { get; set; }
    public int RecipeId { get; set; }
    public string Name { get; set; }
    public decimal Quantity { get; set; }
    public string Unit { get; set; }
}

These classes conform to certain default conventions that EF Core uses to build its database mapping. For example, the Recipe class has a RecipeId property, and the Ingredient class has an IngredientId property. EF Core identifies this Id suffix pattern as indicating the primary key of the table.

Definition A primary key of a table is a value that uniquely identifies that row among all other rows in the table. It's typically an int or Guid.

Another convention visible here is the Ingredient class's RecipeId property. EF Core interprets this as a foreign key pointing to the Recipe class. When considered together with the ICollection<Ingredient> on the Recipe class, this represents a many-to-one relationship, where each recipe has many ingredients, but each ingredient belongs to only one recipe, as shown in the figure.

Figure 12.5 The many-to-one relationship in code is translated to a foreign key relationship between tables.

Definition A foreign key on a table points to a primary key of a different table, forming a link between two rows.

Many other conventions exist here as well, such as the names EF Core assumes for database tables and columns, or the database column types it will use for each property, but I won't go into them all here. The EF Core documentation contains details on all conventions and how to customize them for your application: https://docs.microsoft.com/ef/core/modeling/.

Tip You can also decorate your entity classes with DataAnnotations attributes to control things like column naming or string length. EF Core will use these attributes to override default conventions.

Beyond entities, you also define a DbContext for your application. This is the core of EF Core in your application and is used for all database calls. Create a custom DbContext, called AppDbContext in this case, by deriving from the DbContext base class as follows. This exposes a DbSet<Recipe> so EF Core can discover and map the Recipe entity. You can expose multiple DbSet<> instances this way for each top-level entity in your application.

Listing 12.3 Defining the Application DbContext

public class AppDbContext : DbContext
{
    // The options constructor parameter contains connection string and other details.
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options) { }
    // You'll use the Recipes property to query the database.
    public DbSet<Recipe> Recipes { get; set; }
}

Your application's AppDbContext is simple, containing a list of root entities, but you can do much more with it in more complex applications. You can fully customize how EF Core maps entities to the database if needed, but for this application, you'll use the defaults.

Note You don't list Ingredient on AppDbContext, but it will be modeled by EF Core because it's exposed in Recipe. You can still access Ingredient objects from the database, but you must navigate through the Ingredients property of Recipe entities to do so, as you'll see later in this chapter.

For this simple example, your data model consists of these three classes: AppDbContext, Recipe, and Ingredient. These two entities will map to tables, their columns to properties, and you'll use AppDbContext to access them.

The data model is done, but you're not ready to use it yet. Your ASP.NET Core application doesn't know how to create your AppDbContext, and your AppDbContext needs a connection string so it can communicate with the database. In the next section, we'll address both of these issues and complete setting up EF Core in your ASP.NET Core application.

Registering the Data Context

Like any other service in ASP.NET Core, you should register AppDbContext with the DI container. When registering the context, you also configure the database provider and set the connection string so EF Core knows how to communicate with the database.

You register AppDbContext in the ConfigureServices method of Startup.cs. EF Core provides a generic AddDbContext<T> extension method for this, which takes a configuration action that provides a DbContextOptionsBuilder instance. This builder can be used to set a large number of EF Core's internal properties and allows you to fully replace EF Core's internal services if needed.

Again, your application's configuration is neat and simple, as shown in the following listing. You set the database provider using the UseSqlServer extension method provided by the Microsoft.EntityFrameworkCore.SqlServer package, passing the connection string to it.

Listing 12.4 Registering a DbContext with the DI Container

public void ConfigureServices(IServiceCollection services)
{
    // Connection string is loaded from configuration, from the ConnectionStrings section.
    var connString = Configuration
        .GetConnectionString("DefaultConnection");
    // Register your application's DbContext as generic.
    services.AddDbContext<AppDbContext>(
        // Specify the database provider in the DbContext's options.
        options => options.UseSqlServer(connString));
    // Add other services.
}

Note If you're using a different database provider, such as the SQLite provider, you need to call the appropriate Use* method on the options object when registering AppDbContext.

As I discussed in a previous chapter, connection strings are a typical secret, so it makes sense to load them from configuration. At runtime, the correct configuration string for the current environment will be used, so you can use different databases in local development and production environments.

Tip You can configure your AppDbContext and provide the connection string in other ways, such as using the OnConfiguring method, but I recommend using the approach shown here for ASP.NET Core websites.

You now have a DbContext, AppDbContext, registered with the DI container, and a data model that corresponds to your database. In terms of code, you're ready to start using EF Core, but you don't have a database yet! In the next section, you'll learn how to use the .NET CLI to easily keep your database in sync with your EF Core data model.

Managing Changes with Migrations

In this section, you'll learn how to use migrations to generate SQL statements to keep the database schema in sync with your application's data model. You'll learn how to create an initial migration and use it to create the database. Then, you'll update your data model, create a second migration, and use it to update the database schema.

Managing database schema changes (such as when you need to add a new table or column) is very difficult. Your application code is explicitly bound to a specific version of the database, and you need to ensure the two always stay in sync.

Definition Schema refers to how data is organized in a database, including tables, columns, and their relationships.

When deploying an application, you can usually delete old code/executables and replace them with new code—the job's done. If you need to roll back changes, delete the new code and deploy the old version of the application.

The difficulty with databases is that they contain data! This means it's not possible to blow it away and create a new database every time you deploy.

A common best practice is to explicitly version control both your database schema and your application code. You can do this in several ways, but it typically involves storing the difference between the database's previous schema and the new schema, usually as SQL scripts. You can then use libraries like DbUp and FluentMigrator to track which scripts have been applied and ensure your database schema is up to date. Alternatively, you can use external tools to manage this for you.

EF Core provides its own version of schema management called Migrations. Migrations provide a way to manage changes to the database schema when your EF Core data model changes. Migrations are C# code files in your application that define how the data model changes—what columns were added, new entities, and so on. Migrations record how your database schema has evolved over time as part of your application, so the schema is always in sync with your application's data model.

You can use command-line tools to create a new database from migrations, or update an existing database by applying new migrations to it. You can even roll back migrations, which will update the database to a previous schema.

Warning Applying migrations modifies the database, so you must always be careful about data loss. If you use a migration to remove a table from the database and then roll back the migration, the table will be recreated, but the data it previously contained will be gone forever!

In this section, you'll see how to create the first migration and use it to create the database. Then you'll update your data model, create a second migration, and use it to update the database schema.

Creating Your First Migration

Before creating migrations, you need to install the necessary tools. There are two main ways to do this:

• Package Manager Console - You can use PowerShell cmdlets in Visual Studio's Package Manager Console (PMC). You can install them directly from the PMC or by adding the Microsoft.EntityFrameworkCore.Tools package to your project.
• .NET Tools - Cross-platform tools that can be run from the command line and extend the .NET SDK. You can install these globally for your machine by running dotnet tool install --global dotnet-ef.

In this book, I'll use the cross-platform .NET tools, but if you're familiar with EF 6.x or prefer using Visual Studio's PMC, equivalent commands exist for all the steps you'll perform. You can verify the .NET tools are installed correctly by running dotnet ef. This should produce a help screen as shown in the figure.

Figure 12.6 Running the dotnet ef command to verify .NET EF Core tools are installed correctly

Tip If you receive a "No executable found matching command 'dotnet-ef'" message when running the above command, make sure you've installed the global tool using dotnet tool install --global dotnet-ef. Typically, you need to run dotnet ef tools from the project folder where AppDbContext is registered, rather than at the solution folder level.

After installing the tools and configuring the database context, you can create the first migration by running the following command in the web project folder, providing a migration name—in this case, InitialSchema:

dotnet ef migrations add InitialSchema

This command creates three files in the Migrations folder of your project:

• Migration file - A file in Timestamp_MigrationName.cs format. This describes operations to perform on the database, such as creating tables or adding columns. Note that the commands generated here are specific to the database provider, based on the database provider configured in your project.
• Migration designer.cs file - This file describes the internal model of your data model that EF Core had when generating the migration.
• AppDbContextModelSnapshot.cs - This describes EF Core's current internal model. When you add another migration, this will be updated, so it should always match the latest migration.

EF Core can use AppDbContextModelSnapshot.cs to determine the previous state of the database when creating new migrations without directly interacting with the database.

These three files encapsulate the migration process, but adding a migration doesn't update anything in the database itself. For that, you must run a different command to apply the migration to the database.

Tip You should and can view the migration files generated by EF Core before running the following command to check what operations it will perform on your database. Better safe than sorry!

You can apply migrations in one of three ways:

• Using .NET tools
• Using Visual Studio PowerShell cmdlets
• In code, by getting an instance of AppDbContext from the DI container and calling context.Database.Migrate()

Which is best for you depends on how you've designed your application, how you update production databases, and your personal preference. I'll use .NET tools now, but I'll discuss some considerations in a later section.

You can apply migrations to the database by running

dotnet ef database update

from your application's project folder. I won't go into detail about how this works, but this command executes four steps:

1 Builds your application.
2 Loads the services configured in your application's Startup class, including AppDbContext.
3 Checks whether the database in your AppDbContext connection string exists. If not, it creates it.
4 Updates the database by applying any unapplied migrations.

If everything is configured correctly, as I showed in the previous section, running this command will set you up with a shiny new database, as shown in the figure.

Note If you receive a "Could not find project" error message when running these commands, check that you're running them in your application's project folder, not in the top-level solution folder.

Figure 12.7 Applying migrations to a database creates the database if it doesn't exist and updates the database to match EF Core's internal data model. The list of applied migrations is stored in the __EFMigrationsHistory table.

When you apply migrations to a database, EF Core creates the necessary tables in the database and adds appropriate columns and keys. You might also notice the __EFMigrationsHistory table. EF Core uses this to store the names of migrations it has applied to the database. The next time you run dotnet ef database update, EF Core can compare this table with the list of migrations in your application and only apply new migrations to your database.

In the next section, we'll see how this makes changing the data model and updating the database schema easy, without having to recreate the database from scratch.

Adding a Second Migration

Most applications inevitably evolve, whether due to expanding scope or simple maintenance. Adding properties to entities, adding entirely new entities, and removing outdated classes—all of these are possibilities.

EF Core migrations make this simple. Imagine you decide to highlight vegetarian and vegan dishes in the recipe application by exposing IsVegetarian and IsVegan properties on the Recipe entity. Change your entities to the state you want, generate a migration, and apply it to the database, as shown in the figure.

Figure 12.8 Creating a second migration and applying it to the database using command-line tools.

Listing 12.5 Adding Properties to the Recipe Entity

public class Recipe
{
    public int RecipeId { get; set; }
    public string Name { get; set; }
    public TimeSpan TimeToCook { get; set; }
    public bool IsDeleted { get; set; }
    public string Method { get; set; }
    public bool IsVegetarian { get; set; }
    public bool IsVegan { get; set; }
    public ICollection<Ingredient> Ingredients { get; set; }
}

After changing the entity, you need to update EF Core's internal representation of the data model. You do this by calling dotnet ef migrations add with a name for the migration, the same way you did for the first migration:

dotnet ef migrations add ExtraRecipeFields

This creates a second migration in your project by adding migration files and their .designer.cs snapshot files and updating AppDbContextModelSnapshot.cs, as shown in the figure.

Figure 12.9 Adding a second migration adds a new migration file and a migration designer.cs file. It also updates AppDbContextModelSnapshot to match the new migration's designer.cs file.

As before, this creates migration files but doesn't modify the database. You can apply the migration and update the database by running

dotnet ef database update

This compares the migrations in your application with the __EFMigrationsHistory table in the database to see which migrations haven't been completed, then runs them. EF Core will run the 20200511204457_ExtraRecipeFields migration, adding the IsVegetarian and IsVegan fields to the database, as shown in the figure.

Figure 12.10 Applying the ExtraRecipeFields migration to the database adds the IsVegetarian and IsVegan fields to the Recipes table.

Using migrations is a great way to ensure your database is version-controlled alongside your application code in source control. You can easily look at your application's source code to understand what it was like at a historical point in time and recreate the database schema the application used at that time.

Migrations are easy to use when you're working alone or deploying to a single web server, but even in those cases, there are important things to consider when deciding how to manage your database. Even more considerations apply for applications with multiple web servers using a shared database, or for containerized applications.

This book is about ASP.NET Core, not EF Core, so I don't want to go too deep into database management, but the next section points out some things to keep in mind when using migrations in production.

In the next section, we'll get back to the substance—defining our business logic and performing CRUD operations on the database.

Querying and Saving Data to the Database

Let's review where you are in creating the recipe application:

• You created a simple data model for the application, consisting of recipes and ingredients.
• You generated a migration for the data model to update EF Core's entity internal model.
• You applied the migration to the database, so its schema matches EF Core's model.

In this section, you'll build the business logic for your application by creating a RecipeService. This will handle querying recipes from the database, creating new recipes, and modifying existing ones. Since this application has only a simple domain, I'll use RecipeService to handle all requirements, but in your own applications, you might have multiple services working together to provide business logic.

Note For simple applications, you might be tempted to move this logic into your Razor pages. I encourage you to resist this temptation; extracting business logic into separate services separates the HTTP-centric nature of Razor pages and web APIs from the underlying business logic. This usually makes your business logic easier to test and more reusable.

We don't have any data in our database yet, so let's get you to create a recipe first.

Creating Records

In this section, you'll build the functionality for users to create recipes in the application. This will primarily consist of a form users can use to input all the details of a recipe using Razor Tag Helpers, which you learned about in earlier chapters. The form posts to a Create.cshtml Razor page, which uses model binding and validation attributes to confirm the request is valid, as you saw in a previous chapter.

If the request is valid, the page handler calls the RecipeService to create a new Recipe object in the database. Since EF Core is the focus of this chapter, I'll focus only on this service, but if you want to see how everything fits together, you can always check the source code for this book.

The business logic for creating a recipe in this application is simple—no logic! Map the command binding model from Create.cshtml Razor page to a Recipe entity and its ingredients, add the Recipe object to the AppDbContext, and persist it to the database, as shown in the figure.

Figure 12.11 Calling Create.cshtml Razor page and creating a new entity. Recipe is created from CreateRecipeCommand binding model and added to DbContext. EF Core generates SQL to add a new row to the Recipes table in the database.

Creating an entity in EF Core involves adding a new row to a mapped table. For your application, whenever you create a new recipe, you also add linked ingredient entities. EF Core handles correctly linking all of these by creating the correct RecipeId for each ingredient in the database.

Most of the code in this example involves converting from CreateRecipeCommand to a Recipe entity—the interaction with AppDbContext only involves two methods: Add() and SaveChangesAsync().

Listing 12.6 Creating a Recipe Entity in the Database

readonly AppDbContext _context; // Inject an instance of AppDbContext into the class constructor using DI.
// CreateRecipeCommand is passed from the Razor Page handler.
public async Task<int> CreateRecipe(CreateRecipeCommand cmd)
{
    // Create a recipe by mapping from the command object to the recipe entity.
    var recipe = new Recipe
    {
        Name = cmd.Name,
        TimeToCook = new TimeSpan(
            cmd.TimeToCookHrs, cmd.TimeToCookMins, 0),
        Method = cmd.Method,
        IsVegetarian = cmd.IsVegetarian,
        IsVegan = cmd.IsVegan,
        Ingredients = cmd.Ingredients?.Select(i =>
                                           // Map each CreateIngredientCommand to an ingredient entity.
                                              new Ingredient
                                              {
                                                  Name = i.Name,
                                                  Quantity = i.Quantity,
                                                  Unit = i.Unit,
                                              }).ToList()
    };
    // Tell EF Core to track the new entity.
    _context.Add(recipe);
    // Tell EF Core to write the entity to the database. This uses the async version of the command.
    await _context.SaveChangesAsync();
    // When saving, EF Core populates the RecipeId field on the new recipe.
    return recipe.RecipeId;
}

All interaction with EF Core and the database starts from an AppDbContext instance, which is typically injected via the constructor. Creating a new entity involves three steps:

1 Create Recipe and Ingredient entities.
2 Add the entity to EF Core's list of tracked entities using _context.Add(entity).
3 Execute the SQL INSERT statement against the database by calling _context.SaveChangesAsync(), adding necessary rows to the Recipe and Ingredient tables.

Tip Most EF Core commands that involve database interaction have both synchronous and asynchronous versions, such as SaveChanges() and SaveChangesAsync(). Generally, the async versions will allow your application to handle more concurrent connections, so I tend to favor them when I can use them.

If something goes wrong when EF Core tries to interact with your database (for example, you haven't run migrations to update the database schema), it will throw an exception. I haven't shown it here, but handling these in your application is important so you don't present users with an ugly error page when something goes wrong.

Assuming everything goes smoothly, EF Core will update all auto-generated IDs on your entities (RecipeId on the recipe, and RecipeId and IngredientId on ingredients). The recipe ID is returned to the Razor page so it can use it; for example, to redirect to viewing the recipe page.

With that, you've created your first entity using EF Core. In the next section, we'll look at loading these entities from the database so you can view them in a list.

Loading Record Lists

For most intents and purposes, loading a single record is the same as loading a list of records. They share the same structure you see in the figure, but when loading a single record, you usually use a Where clause and a command that limits the data to a single entity.

The following code shows loading a recipe by ID, following the same basic pattern as before. It uses a Where() LINQ expression to limit the query to a single recipe where RecipeId == id, and uses a Select clause to map to RecipeDetailViewModel. The SingleOrDefaultAsync() clause causes EF Core to generate a SQL query, execute it on the database, and build the view model.

Note If the preceding Where clauce returns multiple records, SingleOrDefaultAsync() will throw an exception.

Listing 12.8 Loading a Single Item Using EF Core in RecipeService

public async Task<RecipeDetailViewModel> GetRecipeDetail(int id) // The ID of the recipe to load is passed as a parameter.
{
    return await _context.Recipes // As before, the query starts from the DbSet property.
        .Where(x => x.RecipeId == id) // Limit the query to recipes with the provided ID.
        .Select(x => new RecipeDetailViewModel  // Map Recipe to RecipeDetailViewModel.
                {
                    Id = x.RecipeId,
                    Name = x.Name,
                    Method = x.Method,
                    Ingredients = x.Ingredients
                        .Select(item => new RecipeDetailViewModel.Item
                                {
                                    Name = item.Name,
                                    Quantity = $"{item.Quantity} {item.Unit}"
                                })
                })
        .SingleOrDefaultAsync(); // Execute the query and map the data to the view model.
}

Notice that in addition to mapping Recipe to RecipeDetailViewModel, you also map the recipe's related ingredients, as if you're working with objects directly in memory. This is one of the advantages of using an ORM—you can easily map child objects and let EF Core decide how best to construct the underlying query to fetch the data.

Note EF Core logs all SQL statements it runs as LogLevel.Information events by default, so you can easily see what queries are run against the database.

Your application is definitely taking shape; you can create new recipes, view them in a list, and drill into individual recipes with their ingredients and methods. However, soon someone will make a typo and want to change their data. For this, you must implement the U in CRUD: update.

Updating Models with Changes

Updating entities when they change is usually the hardest part of CRUD operations because there are many variables. The figure outlines how this works for your recipe application.

I won't deal with the relational side in this book as it's usually a complex matter and how you handle it depends on your data model's specifics. Instead, I'll focus on updating the properties of the Recipe entity itself.

Figure 12.14 Updating an entity involves three steps: reading the entity using EF Core, updating the entity's properties, and calling SaveChangesAsync() on the DbContext to generate SQL to update the correct row in the database.

For web applications, when you update an entity, you typically follow the steps outlined in the figure:

1 Read the entity from the database.
2 Modify the entity's properties.
3 Save the changes to the database.

You'll encapsulate these three steps in a RecipeService method called UpdateRecipe. This method takes an UpdateRecipeCommand parameter and contains the code to change the recipe entity.

Note As with the create command, you don't directly modify entities in Razor pages, ensuring you keep UI concerns separated from business logic.

The following listing shows the RecipeService.UpdateRecipe method, which updates the Recipe entity. It performs the three steps we defined earlier: read, modify, and save. I've extracted the code to update the new values into a helper method.

Listing 12.9 Updating an Existing Entity Using EF Core in RecipeService

public async Task UpdateRecipe(UpdateRecipeCommand cmd)
{
    // Find is exposed directly by Recipes and simplifies the process of reading an entity by ID.
    var recipe = await _context.Recipes.FindAsync(cmd.Id);
    // If the provided ID is invalid, recipe will be null.
    if(recipe == null) {
        throw new Exception("Unable to find the recipe");
    }
    // Set new values on the recipe entity.
    UpdateRecipe(recipe, cmd);
    // Execute SQL to save changes to the database.
    await _context.SaveChangesAsync();
}
// Helper method to set new properties on a recipe entity
static void UpdateRecipe(Recipe recipe, UpdateRecipeCommand cmd)
{
    recipe.Name = cmd.Name;
    recipe.TimeToCook =
        new TimeSpan(cmd.TimeToCookHrs, cmd.TimeToCookMins, 0);
    recipe.Method = cmd.Method;
    recipe.IsVegetarian = cmd.IsVegetarian;
    recipe.IsVegan = cmd.IsVegan;
}

In this example, I read the Recipe entity using the FindAsync(id) method exposed by the DbSet. This is a simple helper method for loading entities by ID—in this case, RecipeId. I could have written a similar query using LINQ:

_context.Recipes.Where(r=>r.RecipeId == cmd.Id).FirstOrDefault();

Using FindAsync() or Find() is more declarative and concise.

Tip Find is actually a bit clever. Find first checks whether the entity is already being tracked in EF Core's DbContext. If it is (because the entity was loaded earlier in this request), the entity is returned immediately without calling the DB. This is obviously faster if the entity is being tracked, but it can also be slower if you know the entity isn't being tracked.

You might wonder how EF Core knows which columns to update when you call SaveChangesAsync(). The simplest approach would be to update every column—if a field hasn't changed, writing the same value again doesn't matter. But EF Core is smarter than this.

EF Core internally tracks the state of any entities it loads from the database. It creates a snapshot of all entity property values so it can track which property values have changed. When you call SaveChanges(), EF Core compares the state of any tracked entities (the recipe entity in this case) with the tracked snapshot. Any properties that have changed are included in the UPDATE statement sent to the database, while unchanged properties are ignored.

With the ability to update recipes, your recipe application is almost complete. "But wait," I hear you cry, "we haven't handled the D in CRUD—delete!" That's true, but in practice, I find there are few times when you actually want to delete data.

Let's consider the requirements for removing a recipe from the application, as shown in the figure. You need a (scary-looking) delete button next to the recipe. When the user clicks delete, the recipe is no longer visible in the list and can't be viewed.

Figure 12.15 Required behavior when removing a recipe from the application. Clicking delete should return to the application's main list view with the deleted recipe no longer visible.

You could implement this by deleting the recipe from the database, but the problem with data is that once it's gone, it's gone! What if a user accidentally deletes a record? Also, deleting a row from a relational database usually has implications for other entities. For example, due to foreign keys, you can't delete a row from the recipes table in your application without also deleting all ingredient rows that reference it, due to the constraint on Ingredient.RecipeId.

EF Core can easily handle these true delete scenarios for you using the DbContext.Remove(entity) command, but often when you think you need to delete data, what you mean is "archive" it or hide it from the UI. A common approach for handling this situation is to include some kind of "is this entity deleted" flag on your entities, such as the IsDeleted flag I included in the Recipe entity:

public bool IsDeleted {get; set;}

If you take this approach, deleting data suddenly becomes much simpler because it's nothing more than an update to an entity. No more losing data, and no more referential integrity problems.

Note I find the main exception to this pattern is when you're storing personally identifiable information about users. In these cases, you might be obligated (and might have legal obligations) to clear their information from your database on request.

Using this approach, you can create a delete method on RecipeService that updates the IsDeleted flag, as shown in the following listing. Additionally, you should ensure all your other methods in RecipeService have Where() clauses to ensure you can't show deleted recipes, as shown in the GetRecipes() method in the listing.

Listing 12.10 Marking an Entity as Deleted in EF Core

public async Task DeleteRecipe(int recipeId)
{
    // Get the Recipe entity by ID.
    var recipe = await _context.Recipes.FindAsync(recipeId);
    // If the provided ID is invalid, recipe will be null.
    if(recipe is null) {
        throw new Exception("Unable to find the recipe");
    }
    // Mark the recipe as deleted.
    recipe.IsDeleted = true;
    // Execute SQL to save changes to the database.
    await _context.SaveChangesAsync();
}

This approach fulfills the requirement—it removes the recipe from the application's UI—but it simplifies many things. This soft delete approach isn't suitable for all scenarios, but I find it's a common pattern in projects I work on.

This brings the chapter on EF Core to a close. We've covered the basics of adding EF Core to your project and using it to simplify data access, but as your application becomes more complex, you'll likely need to know more about EF Core. In the final section of this chapter, I want to point out some things to consider before using EF Core in your own applications, so you're familiar with some of the issues you'll face.

The following topics aren't necessary for getting started with EF Core, but you'll encounter them soon if you're building production-ready applications. This section isn't an instructive guide to solving these issues; it's more of a checklist of things to consider before you go to production.

• Column scaffolding - EF Core uses conservative values for things like string columns by allowing large or unlimited length strings. In practice, you may want to limit these and other data types to reasonable values.
• Validation - You can decorate your entities with DataAnnotations validation attributes, but EF Core won't automatically validate values before saving to the database. This differs from EF 6.x's behavior, where validation was automatic.
• Handling concurrency - EF Core provides several ways of handling concurrency, where multiple users attempt to update an entity simultaneously. One partial solution is using a timestamp column on your entities.
• Synchronous vs. async - EF Core provides both synchronous and asynchronous commands for interacting with the database. Generally, async is better for web applications, but there's nuance to this argument, so it's impossible to recommend one over the other in all cases.

EF Core is a great productivity tool when writing data access code, but some aspects of using databases are inevitably awkward. Database management issues are among the trickiest. This book is about ASP.NET Core, not EF Core, so I don't want to go too deep into database management. That said, most web applications use some kind of database, so the following issues might affect you at some point:

• Automated migrations - If you automatically deploy your application as part of some DevOps pipeline to production, you'll inevitably need some way to automatically apply migrations to the database. You can address this in several ways, such as writing .NET tool scripts, applying migrations in your application's startup code, or using custom tools. Each approach has its advantages and disadvantages.
• Multiple web hosts - One concrete consideration is whether you have multiple web servers hosting your application, all pointing to the same database. If so, applying migrations in your application's startup code becomes more difficult because you must ensure only one application can migrate the database at a time.
• Making backward-compatible schema changes - An inevitable consequence of the multiple web hosts approach is that you'll often encounter situations where your application is accessing a database with a newer schema than the application thinks it has. This means you should generally make schema changes as backward compatible as possible whenever you can.
• Storing migrations in a different assembly - In this chapter, I've kept everything in a single project, but in larger applications, data access often resides in a different project from the web application. For applications with this structure, you must use slightly different commands when using the .NET CLI or PowerShell cmdlets.
• Seeding data - When you first create your database, you usually want it to have some initial seed data, such as default users. EF 6.x has a built-in data seeding mechanism, while EF Core requires you to explicitly seed the database yourself.

How you choose to handle these issues will depend on your application's infrastructure and deployment approach. Solving them isn't particularly fun, but unfortunately they're necessary. Cheer up, though—they can all be solved in one way or another!

This brings us to the end of this chapter on EF Core. In the next chapter, we'll look at slightly more advanced topics in MVC and Razor Pages: the filter pipeline and how you can use it to reduce repetition in your code.