PART VII

Entity Framework Core

CHAPTER 21

Introducing Entity Framework Core

The previous chapter examined the fundamentals of ADO.NET. As you saw, ADO.NET enables .NET programmers to work with relational data. While ADO.NET is an effective tool for working with data, it isn’t necessarily an efficient tool. The efficiency that I am referring to is developer efficiency. To help with the developer efficiency, Microsoft introduced a new framework for data access called the Entity Framework (or simply, EF) in .NET 3.5 Service Pack 1.
EF provides the capability to interact with data from relational databases using an object model that maps directly to the business objects (or domain objects) in your application. For example, rather than treating a batch of data as a collection of rows and columns, you can operate on a collection of strongly typed objects termed entities. These entities are held in specialized collection classes that are LINQ aware, enabling data access operations using C# code. The collection classes provide querying against the data store using the same LINQ grammar you learned about in Chapter 13.
In addition to working with your data as the application domain model (instead of a normalized database model), EF provides efficiencies such as state tracking, unit of work operations, and intrinsic transaction support.
Entity Framework Core is a complete rewrite of Entity Framework 6. It is built on top of the .NET 6 Framework, enabling EF Core to run on multiple platforms. Rewriting EF Core has enabled the team to add new features and performance improvements to EF Core that couldn’t be reasonably implemented in EF 6.
Re-creating an entire framework from scratch requires a hard look at which features will be supported in the new framework and which features will be left behind. One of the features of EF 6 that is not in EF Core (and not likely to ever be added) is support for the Entity Designer. EF Core only supports what is called code-first development. The name is really a terrible name since it infers you can’t use EF Core with an existing database. It really means “without a designer,” but that wasn’t the name that was chosen. EF Core can be used with existing databases that can be scaffolded into entity classes and a derived DbContext, or you can use EF Core to create/update your database from your entity classes and derived DbContext. I will cover both of these scenarios shortly.
With each release, EF Core has added more features that existed in EF 6 as well as new features that never existed in EF 6. The 3.1 release significantly shortened the list of essential features that are missing from EF Core (as compared to EF 6), and 5.0 closed the gap even more. The release of EF Core 6.0 has solidified the framework, and now, for most projects, EF Core has everything you need.
This chapter and the next three will introduce you to data access using EF Core. You will learn about the following: creating a domain model, mapping entity classes and properties to the database tables and columns, implementing change tracking, using the EF Core command-line interface (CLI) for scaffolding and migrations, as well as the role of the DbContext class. You will also learn about relating entities with navigation properties, transactions, and concurrency checking, just to name a few of the features explored. The fourth and final chapter on EF Core exercises the data access layer by using a series of integration tests. These tests demonstrate using EF Core for create, read, update, and delete (CRUD) operations.
By the time you complete these chapters, you will have the final version of the data access layer for our
AutoLot database. Before we get into EF Core, let’s talk about object-relational mappers in general.

© Andrew Troelsen, Phil Japikse 2022
A. Troelsen and P. Japikse, Pro C# 10 with .NET 6, https://doi.org/10.1007/978-1-4842-7869-7_21

861

■ Note Four chapters are not nearly enough to cover all of Entity Framework Core, as entire books (some the size of this one) are dedicated to just EF Core. The intent of these chapters is to give you a working knowledge to get you started using EF Core for your line-of-business applications.

Object-Relational Mappers
ADO.NET provides you with a fabric that lets you select, insert, update, and delete data with connections, commands, and data readers. While this is all well and good, these aspects of ADO.NET force you to treat the fetched data in a manner that is tightly coupled to the physical database schema. Recall, for example, when getting records from the database, you open a connection, create and execute a command object, and then use a data reader to iterate over each record using database-specific column names.
When you use ADO.NET, you must always be mindful of the physical structure of the back-end database. You must know the schema of each data table, author potentially complex SQL queries to interact with data table(s), track changes to the retrieved (or added) data, etc. This can force you to author some fairly verbose C# code because C# itself does not speak the language of the database schema directly.
To make matters worse, the way in which a physical database is usually constructed is squarely focused on database constructs such as foreign keys, views, stored procedures, and data normalization, not object- oriented programming.
Another concern for application developers is change tracking. Getting the data from the database is one step of the process, but any changes, additions, and/or deletions must be tracked by the developer so they can be persisted back to the data store.
The availability of object-relational mapping frameworks (commonly referred to as ORMs) in .NET greatly enhanced the data access story by managing the bulk of CRUD data access tasks for the developer. The developer creates a mapping between the .NET objects and the relational database, and the ORM manages connections, query generation, change tracking, and persisting the data. This leaves the developer free to focus on the business needs of the application.

■ Note It is important to remember that ORMs are not magical unicorns riding on rainbows. Every decision involves trade-offs. ORMs reduce the amount of work for developers creating data access layers but can also introduce performance and scaling issues if used improperly. Use ORMs for CRUD operations and use the power of your database for set-based operations.

Even though the different ORMs have slight differences in how they operate and how they are used, they all have essentially the same pieces and parts and strive for the same goal—to make data access operations easier. Entities are classes that are mapped to the database tables. A specialized collection type contains one or more entities. A change tracking mechanism tracks the state of the entities and any changes, additions, and/or deletions made to them, and a central construct controls operations as the ringleader.

Understanding the Role of the Entity Framework Core
Under the covers, EF Core uses the ADO.NET infrastructure you have already examined in the previous chapter. Like any ADO.NET interaction with a data store, EF Core uses an ADO.NET data provider for data store interactions. Before an ADO.NET data provider can be used by EF Core, it must be updated to fully integrate with EF Core. Due to this added functionality, you might have fewer EF Core data providers available than ADO.NET data providers.

The benefit of EF Core using the ADO.NET database provider pattern is that it enables you to combine EF Core and ADO.NET data access paradigms in the same project, augmenting your capabilities. For example, using EF Core to provide the connection, schema, and table name for bulk copy operations leverages the mapping capabilities of EF Core and the BCP functionality built into ADO.NET. This blended approach makes EF Core just another tool in your tool chest.
When you see how much of the basic data access plumbing is handled for you in a convenient and efficient manner, EF Core will most likely become your go-to mechanism for data access.

■ Note Many third-party databases (e.g., Oracle and MySQL) provide EF-aware data providers. If you are not using SQL Server, consult your database vendor for details or navigate to https://docs.microsoft.com/ en-us/ef/core/providers for a list of available EF Core data providers.

EF Core best fits into the development process in forms-over-data (or API-over-data) situations.
Operations on small numbers of entities using the unit of work pattern to ensure consistency is the sweet spot for EF Core. It is not very well suited for large-scale data operations such as extract-transform-load (ETL) data warehouse applications or large reporting situations.

The Building Blocks of the Entity Framework
The main components of EF Core are DbContext, ChangeTracker, the DbSet specialized collection type, the database providers, and the application’s entities. To work through this chapter, create a new
Console Application named AutoLot.Samples and add the Microsoft.EntityFrameworkCore, Microsoft. EntityFrameworkCore.Design, and Microsoft.EntityFrameworkCore.SqlServer packages. Remember to disable nullable reference types in the project file:

Exe
net6.0
enable
disable

The Microsoft.EntityFrameworkCore package provides the common functionality for EF Core. The Microsoft.EntityFrameworkCore.SqlServer package supplies the SQL Server data provider, and the Microsoft.EntityFrameworkCore.Design package is required for the EF Core command-line tools.

■ Note If you prefer to use the nuget package Manager Console to run the EF Core commands, install the Microsoft.EntityFrameworkCore.Tools package. This text does not cover the nuget-style commands since the CLI works across all platforms and doesn’t rely on Visual Studio.

Add a new file named GlobalUsings.cs, clear out the template code, and update the file to match the following:

global using Microsoft.EntityFrameworkCore;
global using Microsoft.EntityFrameworkCore.ChangeTracking; global using Microsoft.EntityFrameworkCore.Design;

global using Microsoft.EntityFrameworkCore.Metadata;
global using Microsoft.EntityFrameworkCore.Metadata.Builders;

global using System.ComponentModel.DataAnnotations;
global using System.ComponentModel.DataAnnotations.Schema;

The DbContext Class
The DbContext class is the ringleader component of EF Core and provides access to the database through the Database property. DbContext manages the ChangeTracker instance, exposes the virtual
OnModelCreating() method for access to the Fluent API, holds all the DbSet properties, and supplies the SaveChanges method to persist data to the data store. It is not used directly, but through a custom class that inherits DbContext. It is in the derived class that the DbSet properties are placed.
Table 21-1 shows some of the more commonly used members of DbContext.

Table 21-1. Common Members of DbContext

Member of DbContext Meaning in Life
Database Provides access to database-related information and functionality, including execution of SQL statements.
Model The metadata about the shape of entities, the relationships between them, and how they map to the database. Note: This property is usually not interacted with directly.
ChangeTracker Provides access to information and operations for entity instances this
DbContext is tracking.
DbSet Not truly a member of DbContext, but properties added to the custom derived DbContext class. The properties are of type DbSet and are used to query and save instances of application entities. LINQ queries against DbSet properties are translated into SQL queries.
Entry() Provides access to change tracking information and operations for the entity, such as explicitly loading related entities or changing the EntityState. Can also be called on an untracked entity to change the state to tracked.
Set() Creates an instance of the DbSet property that can be used to query and persist data.
SaveChanges()/SaveChange sAsync() Saves all entity changes to the database and returns the number of records affected. Executes in a transaction (implicit or explicit).
Add()/AddRange() Update()/UpdateRange() Remove()/RemoveRange() Methods to add, update, and remove entity instances. Changes are persisted only when SaveChanges() is executed successfully. Async versions are available as well. Note: While available on the derived DbContext, these methods are usually called directly on the DbSet properties.
Find() Finds an entity of a type with the given primary key values. Async versions are available as well. Note: While available on the derived DbContext, these methods are usually called directly on the DbSet properties.
(continued)

Table 21-1. (continued)

Member of DbContext Meaning in Life
Attach()/AttachRange() Begins tracking an entity (or list of entities). Async versions are available as well. Note: While available on the derived DbContext, these methods are usually called directly on the DbSet properties.
SavingChanges() Event fired at the beginning of a call to SaveChanges()/SaveChangesAsync().
SavedChanges() Event fired at the end of a call to SaveChanges()/SaveChangesAsync().
SaveChangesFailed Event fired if a call to SaveChanges()/SaveChangesAsync() fails.
OnModelCreating() Called when a model has been initialized, but before it’s finalized. Methods from the Fluent API are placed in this method to finalize the shape of the model.
OnConfiguring() A builder used to create or modify options for DbContext. Executes each time a DbContext instance is created. Note: It is recommended not to use this and instead use DbContextOptions to configure the DbContext instance at runtime and use an instance of IDesignTimeDbContextFactory at design time.

Creating a Derived DbContext
The first step in EF Core is to create a custom class that inherits from DbContext. Then add a constructor that accepts a strongly typed instance of DbContextOptions (covered next) and passes the instance through to the base class. Add a file named ApplicationDbContext.cs and update the code to match the following:

namespace AutoLot.Samples;

public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions options)
: base(options)
{
}
}

This is the class that is used to access the database and work with entities, the change tracker, and all components of EF Core.

Configuring the DbContext
The DbContext instance is configured using an instance of the DbContextOptions class. The DbContextOptions instance is created using DbContextOptionsBuilder, as the DbContextOptions class is not meant to be directly constructed in your code. Through the DbContextOptionsBuilder instance, the database provider is selected (along with any provider-specific settings), and EF Core DbContext general options (such as logging) are set. Then the instance of the DbContextOptions is injected into the base DbContext at runtime. You will see this in action in the next section.
This dynamic configuration capability enables changing settings at runtime simply by selecting different options (e.g., MySQL instead of the SQL Server provider) and creating a new instance of your derived DbContext.

The Design-Time DbContext Factory
The design-time DbContext factory is a class that implements the IDesignTimeDbContextFactory interface, where T is the derived DbContext class. The interface has one method, CreateDbContext(), that you must implement to create an instance of your derived DbContext. This class is not meant for production use, but only during development, and exists primarily for the EF Core command-line tools, which you will explore shortly. In the examples in this and the next chapter, it will be used to create new instances of the ApplicationDbContext.

■ Note It is considered bad practice to use the DbContext factory to create instances of your derived DbContext class. Remember that this is demo code meant for teaching, and using it in this way keeps the demo code cleaner. you will see how to properly instantiate your derived DbContext class in the chapters on windows presentation Foundation and aSp.nET Core.

The following ApplicationDbContextFactory class uses the CreateDbContext() method to create a strongly typed DbContextOptionsBuilder for the ApplicationDbContext class, sets the database provider to the SQL Server provider (using the Docker instance connection string from Chapter 20), and then creates and returns a new instance of the ApplicationDbContext:

namespace AutoLot.Samples;

public class ApplicationDbContextFactory : IDesignTimeDbContextFactory
{
public ApplicationDbContext CreateDbContext(string[] args)
{
var optionsBuilder = new DbContextOptionsBuilder();
var connectionString = @"server=.,5433;Database=AutoLotSamples;User Id=sa;Password= P@ssw0rd;Encrypt=False;";
optionsBuilder.UseSqlServer(connectionString); Console.WriteLine(connectionString);
return new ApplicationDbContext(optionsBuilder.Options);
}
}

■ Note The database name used in these samples is AutoLotSamples, and not AutoLot, which was the name used in Chapter 20. The autoLot database will be updated to its final form starting with Chapter 20.

Again, the context factory is designed for the EF Core command-line interface to create an instance of the derived DbContext class, and not for production use. The command-line interface uses the factory when performing actions such as creating or applying database migrations. One major reason that you don’t want to use this in production is the hard-coded connection string. Since this is for design-time use, using a set connection string that points to the development database works perfectly.
The CreateDbContext() method takes a string array as argument. While not used in earlier versions, support for passing in arguments from the command line into the IDesignTimeDbContextFactory CreateDbContext() method was added in EF Core 5.

OnModelCreating
The base DbContext class exposes the OnModelCreating method that is used to shape your entities using the Fluent API. This will be covered in depth later in this chapter, but for now, add the following code to the ApplicationDbContext class:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Fluent API calls go here
}

Saving Changes
To persist any changes (add, update, or delete) to entities, call the SaveChanges() (or SaveChangesAsync()) method on the derived DbContext. The SaveChanges()/SaveChangesAsync() methods wrap the database calls in an implicit transaction and persist them as a unit of work. Transactions are covered next, and the change tracker is covered later in this section.
Add the following global using statement to the GlobalUsings.cs file:

global using AutoLot.Samples;

Clear out any code in the Program.cs file, and update it to match the following:
Console.WriteLine("Fun with Entity Framework Core"); static void SampleSaveChanges()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
//make some changes context.SaveChanges();
}

There will be many examples of saving changes through the rest of this chapter (and book).

Transaction and Save Point Support
As mentioned previously, EF Core wraps each call to SaveChanges()/SaveChangesAsync() in an implicit transaction. By default, the transaction uses the isolation level of the database. For more control, you can enlist the derived DbContext into an explicit transaction instead of using the default implicit transaction. To execute in an explicit transaction, create a transaction using the Database property of the derived DbContext. Conduct your operation(s) as usual and then commit or roll back the transaction. Here is a code snippet that demonstrates this:

static void TransactedSaveChanges()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); using var trans = context.Database.BeginTransaction();

try
{
//Create, change, delete stuff context.SaveChanges(); trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
}

Save points for EF Core transactions were introduced in EF Core 5. When SaveChanges()/SaveChange sAsync() is called and a transaction is already in progress, EF Core creates a save point in that transaction. If the call fails, the transaction is rolled back to the save point and not the beginning of the transaction. Save points can also be managed programmatically by calling CreateSavePoint() and RollbackToSavepoint() on the transaction, like this:

static void UsingSavePoints()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); using var trans = context.Database.BeginTransaction();
try
{
//Create, change, delete stuff trans.CreateSavepoint("check point 1"); context.SaveChanges();
trans.Commit();
}
catch (Exception ex)
{
trans. RollbackToSavepoint("check point 1");
}
}

Explicit Transactions and Execution Strategies
When an execution strategy is active (covered in the next chapter in the “Connection Resiliency” section), before creating an explicit transaction, you must get a reference to the current execution strategy in use. Then call the Execute() method on the strategy to create an explicit transaction.

static void TransactionWithExecutionStrategies()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var strategy = context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
{
using var trans = context.Database.BeginTransaction();

try
{
//actionToExecute(); trans.Commit();
Console.WriteLine("Insert succeeded");
}
catch (Exception ex)
{

}
});
}

trans.Rollback();
Console.WriteLine($"Insert failed: {ex.Message}");

Saving/Saved Changes Events
EF Core 5 introduced three new events that are triggered by the SaveChanges()/SaveChangesAsync() methods. The SavingChanges event fires when SaveChanges() is called but before the SQL statements are executed against the data store. The SavedChanges event fires after SaveChanges() has completed. The SaveChangesFailed event fires if the call to SaveChanges() was unsuccessful. The following (trivial) code examples in the ApplicationDbContext class constructor show the events and their handlers in action:

public ApplicationDbContext(DbContextOptions options)
: base(options)
{
SavingChanges += (sender, args) =>
{
Console.WriteLine($"Saving changes for {((DbContext)sender).Database.
GetConnectionString()}");
};
SavedChanges += (sender, args) =>
{
Console.WriteLine($"Saved {args.EntitiesSavedCount} entities");
};
SaveChangesFailed += (sender, args) =>
{
Console.WriteLine($"An exception occurred! {args.Exception.Message} entities");
};
}

The DbSet Class
For each entity type (T) in your object model, you add a property of type DbSet to the derived DbContext
class. The DbSet class is a specialized collection property used to interact with the database provider to read, add, update, or delete records in the database. Each DbSet provides a number of core services for the database interactions, including translating LINQ queries executed against a DbSet property into database queries by the database provider. Table 21-2 describes some of the core members of the DbSet class.

Table 21-2. Common Members and Extension Methods of DbSet

Member of DbSet Meaning in Life
Add()/AddRange() Begins tracking the entity/entities in the Added state. Item(s) will be added when SaveChanges() is called. Async versions are available as well.
AsAsyncEnumerable() Returns the collection as IAsyncEnumerable.
AsQueryable() Returns the collection as IQueryable.
Find() Searches for the entity in the ChangeTracker by primary key. If not found in the change tracker, the data store is queried for the object. An async version is available as well.
Update()UpdateRange() Begins tracking the entity/entities in the Modified state. Item(s) will be updated when SaveChanges is called. Async versions are available as well.
Remove()RemoveRange() Begins tracking the entity/entities in the Deleted state. Item(s) will be removed when SaveChanges() is called. Async versions are available as well.
Attach()AttachRange() Begins tracking the entity/entities. Entities with numeric primary keys defined as an identity and value equaling zero are tracked as Added. All others are tracked as Unchanged. Async versions are available as well.
FromSqlRaw() FromSqlInterpolated() Creates a LINQ query based on a raw or interpolated string representing a SQL query. Can be combined with additional LINQ statements for server-side execution.
AsQueryable() Returns an IQueryable instance from DbSet.

The DbSet type implements IQueryable, which enables the use of LINQ queries to retrieve records from the database. In addition to extension methods added by EF Core, DbSet supports the same extension methods you learned about in Chapter 13, such as ForEach(), Select(), and All().
You will be adding the DbSet properties to ApplicationDbContext in the “Entities” section.

■ Note Many of the methods listed in Table 21-2 are named the same as the methods in Table 21-1. The main difference is that the DbSet methods already know the type to operate on and have the list of entities. The DbContext methods must determine what to act on using reflection. It is much more common to use the methods on the DbSet properties rather than the more general methods on the derived DbContext.

The ChangeTracker
The ChangeTracker instance tracks the state for objects loaded into DbSet within a DbContext instance. Table 21-3 lists the possible values for the state of an object.

Table 21-3. Entity State Enumeration Values

Value Meaning in Life
Added The entity is being tracked but does not yet exist in the database.
Deleted The entity is being tracked and is marked for deletion from the database.
Detached The entity is not being tracked by the change tracker.
Modified The entry is being tracked and has been changed.
Unchanged The entity is being tracked, exists in the database, and has not been modified.

If you need to check the state of an object, use the following code:

EntityState state = context.Entry(entity).State;

You can also programmatically change the state of an object using the same mechanism. To change the state to Deleted (for example), use the following code:

context.Entry(entity).State = EntityState.Deleted;

ChangeTracker Events
There are two events that can be raised by ChangeTracker. The first is StateChanged, and the second is Tracked. The StateChanged event fires when an entity’s state is changed. It does not fire when an entity is first tracked. The Tracked event fires when an entity starts being tracked, either by being programmatically added to a DbSet instance or when returned from a query.
Update the constructor for the ApplicationDbContext class to the following to specify the event handlers for the StateChanged and Tracked events:

public ApplicationDbContext(DbContextOptions options)
: base(options)
{
…
ChangeTracker.StateChanged += ChangeTracker_StateChanged; ChangeTracker.Tracked += ChangeTracker_Tracked;
}

The StateChanged Event
As mentioned, the StateChanged event fires when the state of an entity changes, but not when an entity is first tracked. The OldState and NewState are exposed through the EntityStateChangedEventArgs. The following example writes to the console anytime an entity is updated:

private void ChangeTracker_StateChanged(object sender, EntityStateChangedEventArgs e)
{
if (e.OldState == EntityState.Modified && e.NewState == EntityState.Unchanged)
{
Console.WriteLine($"An entity of type {e.Entry.Entity.GetType().Name} was updated.");
}
}

The Tracked Event
The Tracked event fires when ChangeTracker starts to track an entity. The FromQuery property of the EntityTrackedEventArgs indicates if the entity was loaded via a database query or programmatically. The following example writes to the console anytime an entity is loaded from the database:

private void ChangeTracker_Tracked(object sender, EntityTrackedEventArgs e)
{
if (e.FromQuery)
{
Console.WriteLine($"An entity of type {e.Entry.Entity.GetType().Name} was loaded from the database.");
}
}

Resetting DbContext State
EF Core 5 added the ability to reset a derived DbContext back to its original state. The ChangeTracker. Clear() method clears out all entities from the DbSet collections by setting their state to Detached. The main benefit of this is to improve performance. As with any ORM, there is some overhead that comes with instantiating a derived DbContext class. While that overhead isn’t usually significant, the ability to clean up an already instantiated context could help with performance in some scenarios.

Entities
The strongly typed classes that map to database tables are officially called entities. The collection of entities in an application comprises a conceptual model of a physical database. Formally speaking, this model is termed an entity data model (EDM), usually referred to simply as the model. The model is mapped to the application/business domain. The entities and their properties are mapped to the tables and columns using Entity Framework Core conventions, configuration, and the Fluent API (code). Entities do not need to map directly to the database schema. You are free to structure your entity classes to fit your application needs and then map your unique entities to your database schema.
This loose coupling between the database and your entities means you can shape the entities to match your business domain, independent of the database design and structure. For example, take the simple Inventory table in the AutoLot database and the Car entity class from the previous chapter. The names are different, yet the Car entity can be mapped to the Inventory table. EF Core examines the configuration of your entities in the model to map the client-side representation of the Inventory table (in our example, the Car class) to the correct columns of the Inventory table.
The next several sections detail how EF Core conventions, data annotations, and code (using the Fluent API) map entities, properties, and the relationships between entities in the mode to the tables, columns, and foreign key relationships in your database. Each of these topics is covered in depth later in this chapter.

Entity Properties and Database Columns
When using a relational data store, EF Core uses data from a table’s columns to populate an entity’s properties when reading from the data store and writes from the entity’s properties to a table’s columns when persisting data. If the property is an automatic property, EF Core reads and writes through the getter and setter. If the property has a backing field, EF Core will read and write to the backing field instead of the public property, even if the backing field is private. While EF Core can read and write to private fields, there still must be a public read-write property that encapsulates the backing field.

Two scenarios where the backing field support is advantageous are when using the INotifyPropertyChanged pattern in Windows Presentation Foundation (WPF) applications and when database default values clash with .NET default values. Using EF Core with WPF is covered in Chapter 28, and database default values are covered later in this chapter.

Table Mapping Schemes
There are two class to table mapping schemes available in EF Core: table-per-hierarchy (TPH) and table-per- type (TPT). TPH mapping is the default and maps an inheritance hierarchy to a single table. Introduced in EF Core 5, TPT maps each class in the hierarchy to its own table.

■ Note Classes can also be mapped to views and raw SQL queries. These are referred to as query types and are covered later in this chapter.

Table-Per-Hierarchy Mapping
Consider the following example, which shows the Car class from Chapter 20 split into two classes: a base class (BaseEntity) for the Id and TimeStamp properties, and the rest of the properties in the Car class. The code for the TPH examples are in the AutoLot.TPH project in the source for this chapter.

//BaseEntity.cs
namespace AutoLot.TPH.Models;

public abstract class BaseEntity
{
public int Id { get; set; }
public byte[] TimeStamp { get; set; }
}

//Car.cs
namespace AutoLot.TPH.Models;

public class Car : BaseEntity
{
public string Color { get; set; } public string PetName { get; set; } public int MakeId { get; set; }
}

To make EF Core aware that an entity class is part of the object model, add a DbSet property for the entity. Create an ApplicationDbContext class and update it to the following:

using Microsoft.EntityFrameworkCore; using AutoLot.TPH.Models;

namespace AutoLot.TPH;

public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions options)
: base(options) { }
public DbSet Cars { get; set; }
}

Note the DbSet property in the ApplicationDbContext class. This informs EF Core that the Car class maps to the Cars table in the database (more on this in the “Entity Conventions” section). Also notice that there isn’t a DbSet property for the BaseEntity class. This is because in the TPH scheme, the entire hierarchy becomes a single table. The properties of the tables up the inheritance chain are folded into the table with the DbSet property. This is shown by the following SQL:

CREATE TABLE [dbo].[Cars](
[Id] [INT] IDENTITY(1,1) NOT NULL, [Color] NVARCHAR NULL, [PetName] NVARCHAR NULL, [MakeId] [INT] NOT NULL, [TimeStamp] VARBINARY NULL,
CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Table-per-Type Mapping
To explore the TPT mapping scheme, the BaseEntity and Car classes can be used, even with the base class marked as abstract. Since TPH is the default, EF Core must be instructed to map each class to a table. This can be done with data annotations (shown later in this chapter) or the Fluent API. To use the TPT mapping scheme, use the following Fluent API code in the OnModelCreating() method of the ApplicationDbContext. These examples are in the AutoLot.TPT project in the chapter’s code samples.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity().ToTable("BaseEntities"); modelBuilder.Entity().ToTable("Cars");
}

EF Core will create two tables, shown here. The indexes also show that the tables have a one-to-one mapping between the BaseEntities and Cars tables.

CREATE TABLE [dbo].[BaseEntities]( [Id] [INT] IDENTITY(1,1) NOT NULL, [TimeStamp] VARBINARY NULL,
CONSTRAINT [PK_BaseEntities] PRIMARY KEY CLUSTERED

(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

CREATE TABLE [dbo].[Cars]( [Id] [INT] NOT NULL,
[Color] NVARCHAR NULL, [PetName] NVARCHAR NULL, [MakeId] [INT] NOT NULL,
CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

ALTER TABLE [dbo].[Cars] WITH CHECK ADD CONSTRAINT [FK_Cars_BaseEntities_Id] FOREIGN KEY([Id])
REFERENCES [dbo].[BaseEntities] ([Id]) GO

ALTER TABLE [dbo].[Cars] CHECK CONSTRAINT [FK_Cars_BaseEntities_Id] GO

■ Note Table-per-type mapping can have significant performance implications that should be considered before using this mapping scheme. For more information, refer to the documentation: https://docs.microsoft.com/en-us/ef/core/performance/modeling-for– performance#inheritance-mapping.

Navigation Properties and Foreign Keys
Navigation properties represent how entity classes relate to each other and enable code to traverse from one entity instance to another. By definition, a navigation property is any property that maps to a nonscalar type as defined by the database provider. In practice, navigation properties map to another entity (called reference navigation properties) or a collection of another entity (called collection navigation properties).
On the database side, navigation properties are translated into foreign key relationships between tables. One-to-one, one-to-many, and (new in EF Core 5) many-to-many relationships are directly supported in EF Core. Entity classes can also have navigation properties that point back to themselves, representing self- referencing tables.

■ Note I find it helpful to consider objects with navigation properties as linked lists, and if the navigation properties are bidirectional, the objects act like doubly linked lists.

Before covering the details of navigation properties and entity relationship patterns, refer to Table 21-4.
These terms are used in all three relationship patterns.

Table 21-4. Terms Used to Describe Navigation Properties and Relationships

Term Meaning in Life
Principal entity The parent of the relationship.
Dependent entity The child of the relationship.
Principal key The property/properties used to define the principal entity. Can be the primary key or an alternate key. Keys can be configured using a single property or multiple properties.
Foreign key The property/properties held by the child entity to store the principal key.
Required relationship Relationship where the foreign key value is required (non-nullable).
Optional relationship Relationship where the foreign key value is not (nullable).

Missing Foreign Key Properties
If an entity with a reference navigation property does not have a property for the foreign key value, EF Core will create the necessary property/properties on the entity. These are known as shadow foreign key
properties and are named in the format of or . This is true for all the relationship types (one-to-many, one-to-one, many-to-many). It is a much cleaner approach to build your entities with explicit foreign key property/properties than to make EF Core create them for you.

One-to-Many Relationships
To create a one-to-many relationship, the entity class on the one side (the principal) adds a collection property of the entity class that is on the many side (the dependent). The dependent entity should also have properties for the foreign key back to the principal. If not, EF Core will create shadow foreign key properties, as explained earlier.
For example, in the database created in Chapter 20, the Makes table (represented by the Make entity class) and Inventory table (represented by the Car entity class) have a one-to-many relationship.

■ Note For these initial examples, the Car class will map to a table named Cars. Later in this chapter the
Car class will be mapped to the Inventory table.

Back in the AutoLot.Samples project, create a new folder named Models. Add the following BaseEntity. cs, Make.cs, and Car.cs files as shown in the code listing. The bold code shows the bidirectional navigation properties representing the one-to-many relationship:

//BaseEntity.cs
namespace AutoLot.Samples.Models;

public abstract class BaseEntity
{
public int Id { get; set; }
public byte[] TimeStamp { get; set; }
}

//Make.cs
namespace AutoLot.Samples.Models;

public class Make : BaseEntity
{
public string Name { get; set; }
public IEnumerable Cars { get; set; } = new List();
}

//Car.cs
namespace AutoLot.Samples.Models;

public class Car : BaseEntity
{
public string Color { get; set; } public string PetName { get; set; } public int MakeId { get; set; }
public Make MakeNavigation { get; set; }
}

■ Note when scaffolding an existing database (as you will do in the next chapter), EF Core names reference navigation properties the same as the property type name (e.g., public Make Make {get; set;}). This can cause issues with navigation and IntelliSense, as well as make the code difficult to work with. I prefer to add the suffix Navigation to reference navigation properties for clarity, as shown in the previous example.

In the Car/Make example, the Car entity is the dependent entity (the many of the one-to-many), and the
Make entity is the principal entity (the one of the one-to-many).
Update the GlobalUsings.cs file to include the new namespace for the models:

global using AutoLot.Samples.Models;

Next, add the DbSet and DbSet properties to ApplicationDbContext, as shown here:

public DbSet Cars { get; set; } public DbSet Makes { get; set; }

When the database is updated using EF Core migrations, the following tables are created:

CREATE TABLE [dbo].[Makes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] nvarchar NULL, [TimeStamp] varbinary NULL,
CONSTRAINT [PK_Makes] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

CREATE TABLE [dbo].[Cars](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Color] nvarchar NULL, [PetName] nvarchar NULL, [TimeStamp] varbinary NULL, [MakeId] [int] NOT NULL,
CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

ALTER TABLE [dbo].[Cars] WITH CHECK ADD CONSTRAINT [FK_Cars_Makes_MakeId] FOREIGN KEY([MakeId]) REFERENCES [dbo].[Makes] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Cars] CHECK CONSTRAINT [FK_Cars_Makes_MakeId]
GO

Note the foreign key and check constraints created on the dependent (Cars) table.

■ Note Updating the database using EF Core migrations is covered later in this chapter. If you are already familiar with EF Core migrations or want to skip ahead to the section on EF Core CLI commands to learn about migrations before continuing on with this section, the specific commands to create these tables are here: dotnet ef migrations add Initial -o Migrations -c AutoLot.Samples.ApplicationDbContextdotnet ef database update Initial -c AutoLot.Samples.ApplicationDbContext

One-to-One Relationships
In one-to-one relationships, both entities have a reference navigation property to the other entity. While one-to-many relationships clearly denote the principal and dependent entity, when building one-to-one
relationships, EF Core must be informed which side is the principal entity. This can be done either by having

a clearly defined foreign key to the principal entity or by indicating the principal using the Fluent API. If EF Core is not informed through one of those two methods, it will choose one based on its ability to detect a foreign key. In practice, you should clearly define the dependent by adding foreign key properties. This removes any ambiguity and ensures that your tables are properly configured.
Add a new class named Radio.cs and update the code to the following:
namespace AutoLot.Samples.Models; public class Radio : BaseEntity
{
public bool HasTweeters { get; set; } public bool HasSubWoofers { get; set; } public string RadioId { get; set; } public int CarId { get; set; }
public Car CarNavigation { get; set; }
}

Add the Radio navigation property to the Car class:
namespace AutoLot.Samples.Models; public class Car : BaseEntity
{

public Radio RadioNavigation { get; set; }
}

Since Radio has a foreign key to the Car class (based on convention, covered shortly), Radio is the dependent entity, and Car is the principal entity. EF Core creates the required unique index on the foreign key property in the dependent entity implicitly. If you want to change the name of the index, that can be accomplished using data annotations or the Fluent API.
Add the DbSet property to the ApplicationDbContext class:

public DbSet Cars { get; set; } public DbSet Makes { get; set; } public DbSet Radios { get; set; }

When the database is updated using the following EF Core migrations, the Cars table is unchanged, and the following Radios table is created:

dotnet ef migrations add Radio -o Migrations -c AutoLot.Samples.ApplicationDbContext dotnet ef database update Radio -c AutoLot.Samples.ApplicationDbContext

The following shows the added Radios table:

CREATE TABLE [dbo].[Radios](
[Id] [int] IDENTITY(1,1) NOT NULL,
[HasTweeters] [bit] NOT NULL, [HasSubWoofers] [bit] NOT NULL, [RadioId] nvarchar NULL, [TimeStamp] varbinary NULL, [CarId] [int] NOT NULL,

CONSTRAINT [PK_Radios] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
ALTER TABLE [dbo].[Radios] WITH CHECK ADD CONSTRAINT [FK_Radios_Cars_CarId] FOREIGN KEY([CarId])
REFERENCES [dbo].[Cars] ([Id]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Radios] CHECK CONSTRAINT [FK_Radios_Cars_CarId]
GO

Note the foreign key and check constraints created on the dependent (Radios) table (shown in bold).

Many-to-Many Relationships
In many-to-many relationships, both entities have a collection navigation property to the other entity. This is implemented in the data store with a join table in between the two entity tables. This join table, by default, is named after the two tables using , but can be changed programmatically through the Fluent API. The join entity has one-to-many relationships to each of the entity tables.
Start by creating a Driver class, which will have a many-to-many relationship with the Car class. On the
Driver side, this is implemented with a collection navigation property to the Car class:
namespace AutoLot.Samples.Models; public class Driver : BaseEntity
{
public string FirstName { get; set; } public string LastName { get; set; }
public IEnumerable Cars { get; set; } = new List();
}

Add the DbSet property to the ApplicationDbContext class:

public DbSet Cars { get; set; } public DbSet Makes { get; set; } public DbSet Radios { get; set; } public DbSet Drivers { get; set; }

Next, update the Car class to have a collection navigation property to the new Driver class:
namespace AutoLot.Samples.Models; public class Car : BaseEntity
{
public string Color { get; set; } public string PetName { get; set; } public int MakeId { get; set; }
public Make MakeNavigation { get; set; }

public Radio RadioNavigation { get; set; }
public IEnumerable Drivers { get; set; } = new List();
}

To update the database, use the following migration commands (again, migrations will be fully explained later in this chapter):

dotnet ef migrations add Drivers -o Migrations -c AutoLot.Samples.ApplicationDbContext dotnet ef database update Drivers -c AutoLot.Samples.ApplicationDbContext

When the database is updated, the Cars table is unchanged, and the Drivers and CarDriver tables are created. Here are the definitions for the new tables:

CREATE TABLE [dbo].[Drivers](
[Id] [INT] IDENTITY(1,1) NOT NULL, [FirstName] NVARCHAR NULL, [LastName] NVARCHAR NULL, [TimeStamp] VARBINARY NULL,
CONSTRAINT [PK_Drivers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

CREATE TABLE [dbo].[CarDriver]( [CarsId] [int] NOT NULL, [DriversId] [int] NOT NULL,
CONSTRAINT [PK_CarDriver] PRIMARY KEY CLUSTERED (
[CarsId] ASC, [DriversId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] GO
ALTER TABLE [dbo].[CarDriver] WITH CHECK ADD CONSTRAINT [FK_CarDriver_Cars_CarsId] FOREIGN KEY([CarsId]) REFERENCES [dbo].[Cars] ([Id]) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[CarDriver] CHECK CONSTRAINT [FK_CarDriver_Cars_CarsId] GO

ALTER TABLE [dbo].[CarDriver] WITH CHECK ADD CONSTRAINT [FK_CarDriver_Drivers_DriversId] FOREIGN KEY([DriversId]) REFERENCES [dbo].[Drivers] ([Id]) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[CarDriver] CHECK CONSTRAINT [FK_CarDriver_Drivers_DriversId] GO

Note the compound primary key, the check constraints (foreign keys), and the cascade behavior are all created by EF Core to make sure the CarDriver table is configured as a proper join table.

Many-to-Many Prior to EF Core 5
The equivalent Car-Driver many-to-many relationship can be accomplished by creating the three tables explicitly and is how it must be done in EF Core versions earlier than EF Core 5. Here is an abbreviated example:

public class Driver
{
…
public IEnumerable CarDrivers { get; set; }
}

public class Car
{
…
public IEnumerable CarDrivers { get; set; }
}
public class CarDriver
{
public int CarId {get;set;}
public Car CarNavigation {get;set;}
public int DriverId {get;set;}
public Driver DriverNavigation {get;set;}
}

Cascade Behavior
Most data stores (like SQL Server) have rules controlling the behavior when a row is deleted. If the related (dependent) records should also be deleted, this is referred to as cascade delete. In EF Core, there are three actions that can occur when a principal entity (with dependent entities loaded into memory) is deleted.
•Dependent records are deleted.
•Dependent foreign keys are set to null.
•The dependent entity remains unchanged.
The default behavior is different between optional and required relationships. The behavior can also be configured to one of seven values, although only five are recommended for use. The behavior is
configured with the DeleteBehavior enum using the Fluent API. The options available in the enumeration are listed here:
•Cascade
•ClientCascade
•ClientNoAction (not recommended for use)
•ClientSetNull
•NoAction (not recommended for use)
•SetNull
•Restrict

In EF Core, the specified behavior is triggered only after an entity is deleted and SaveChanges() is called on the derived DbContext. See the “Query Execution” section for more details about when EF Core interacts with the data store.

Optional Relationships
Recall from Table 21-4 that optional relationships are where the dependent entity can set the foreign key value(s) to null. For optional relationships, the default behavior is ClientSetNull. Table 21-5 shows the cascade behavior with dependent entities and the effect on database records when using SQL Server.

Table 21-5. Cascade Behavior with Optional Relationships

Delete Behavior Effect on Dependents (In
Memory) Effect on Dependents (In Database)
Cascade Entities are deleted. Entities are deleted by the database.
ClientCascade Entities are deleted. For databases that do not support cascade delete, EF Core deletes the entities.
ClientSetNull
(default) Foreign key property/ properties set to null. None.
SetNull Foreign key property/ properties set to null. Foreign key property/properties set to null.
Restrict None. None.

Required Relationships
Required relationships are where the dependent entity cannot set the foreign key value(s) to null. For required relationships, the default behavior is Cascade. Table 21-6 shows the cascade behavior with dependent entities and the effect on database records when using SQL Server.

Table 21-6. Cascade Behavior with Required Relationships

Delete Behavior Effect on Dependents (In
Memory) Effect on Dependents (In Database)
Cascade
(default) Entities are deleted. Entities are deleted.
ClientCascade Entities are deleted. For databases that do not support cascade delete, EF Core deletes the entities.
ClientSetNull SaveChanges throws exception. None.
SetNull SaveChanges throws exception. SaveChanges throws exception.
Restrict None. None.

Entity Conventions
There are many conventions that EF Core uses to define an entity and how it relates to the data store. The conventions are always enabled unless overruled by data annotations or code in the Fluent API. Table 21-7 lists some of the more important EF Core conventions.

Table 21-7. Some of the EF Core Conventions

Convention Meaning in Life
Included tables All classes with a DbSet property and all classes that can be reached (through navigation properties) by a DbSet class are created in the database.
Included columns All public properties with a getter and setter (including automatic properties) are mapped to columns.
Table name Maps to the name of the DbSet property name in the derived DbContext. If no DbSet
exists, the class name is used.
Schema Tables are created in the data store’s default schema (dbo on SQL Server).
Column name Column names are mapped to the property names of the class.
Column data type Data types are selected based on the .NET data type and translated by the database provider (SQL Server). DateTime maps to datetime2(7), and string maps to nvarchar(max). Strings as part of a primary key map to nvarchar(450).
Column nullability Non-nullable data types are created as Not Null persistence columns. EF Core honors C# 8 nullability.
Primary key Properties named Id or Id will be configured as the primary key. Keys of type short, int, long, or Guid have values controlled by the data store. Numerical values are created as Identity columns (SQL Server).
Relationships Relationships between tables are created when there are navigation properties between two entity classes.
Foreign key Properties named Id are foreign keys for navigation properties of type

.

The previous navigation property examples all leverage EF Core conventions to build the relationships between the tables.

Mapping Properties to Columns
By convention, the public read-write properties map to columns of the same name. The data type matches the data store’s equivalent of the property’s CLR data type. Non-nullable properties are set to not null in the data store, and nullable properties (including nullable reference types introduced in C# 8) are set to allow null.
EF Core also supports reading and writing to property backing fields. EF Core expects the backing field to be named using one of the following conventions (in order of precedence):
•_
•_ •m_
•m_

If the Color property of the Car class is updated to use a backing field, it would (by convention) need to be named one of _color, _Color, m_color, or m_Color, like this:

private string _color; public string Color
{
get => _color;
set => _color = value;
}

Overriding EF Core Conventions
New in EF Core 6, the conventions can be overridden using the ConfigureConventions() method. For example, if you want string properties to default to a certain size (instead of nvarchar(max)), you can add the following code into the ApplicationDbContext class:

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
configurationBuilder.Properties().HaveMaxLength(50);
}

When a new migration is created and executed, you will see that all of the string properties are updated to nvarchar(50).

Entity Framework Data Annotations
Data annotations are C# attributes that are used to further shape your entities. Table 21-8 lists some of the most commonly used data annotations for defining how your entity classes and properties map to database tables and fields. Data annotations override any conflicting conventions. There are many more annotations that you can use to refine the entities in the model, as you will see throughout the rest of this chapter and book.

Table 21-8. Some Data Annotations Supported by the Entity Framework Core (*New Attributes in EF Core 6)

Data Annotation Meaning in Life
Table Defines the schema and table name for the entity.
EntityTypeConfiguration* In conjunction with the IEntityTypeConfiguration interface, allows an entity to be configured in its own class using the Fluent API. The use of this attribute is covered in the Fluent API section.
Keyless Indicates an entity does not have a key (e.g., representing a database view).
Column Defines the column name for the entity property.
BackingField Specifies the C# backing field for a property.
Key Defines the primary key for the entity. Key fields are implicitly also [Required].
Index Placed on a class to specify a single column or multicolumn index. Allows for specifying the index is unique.
(continued)

Table 21-8. (continued)

Data Annotation Meaning in Life
Owned Declares that the class will be owned by another entity class.
Required Declares the property as not nullable in the database.
ForeignKey Declares a property that is used as the foreign key for a navigation property.
InverseProperty Declares the navigation property on the other end of a relationship.
StringLength Specifies the max length for a string property.
TimeStamp Declares a type as a rowversion in SQL Server and adds concurrency checks to database operations involving the entity.
ConcurrencyCheck Flags field to be used in concurrency checking when executing updates and deletes.
DatabaseGenerated Specifies if the field is database generated or not. Takes a
DatabaseGeneratedOption value of Computed, Identity, or None.
DataType Provides for a more specific definition of a field than the intrinsic data type.
Unicode* Maps string property to Unicode/non-Unicode database column without specifying the native datatype.
Precision* Specifies precision and scale for the database column without specifying the native datatype.
NotMapped Excludes the property or class in regard to database fields and tables.

The following code shows the BaseEntity class with annotations that declare the Id field as the primary key. The second data annotation on the Id property indicates that it is an Identity column in SQL Server.
The TimeStamp property will be a SQL Server timestamp/rowversion property (for concurrency checking, covered later in this chapter).

public abstract class BaseEntity
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
[Timestamp]
public byte[] TimeStamp { get; set; }
}

Here is the Car class and the data annotations that shape it in the database, explained after the code sample:

[Table(“Inventory”, Schema=”dbo”)] [Index(nameof(MakeId), Name = “IX_Inventory_MakeId”)] public class Car : BaseEntity
{
private string _color; [Required, StringLength(50)] public string Color

{
get => _color;
set => _color = value;
}
[Required, StringLength(50)]
public string PetName { get; set; } public int MakeId { get; set; } [ForeignKey(nameof(MakeId))]
public Make MakeNavigation { get; set; } public Radio RadioNavigation { get; set; } [InverseProperty(nameof(Driver.Cars))]
public IEnumerable Drivers { get; set; }
}

The Table attribute maps the Car class to the Inventory table in the dbo schema. The Column attribute (not shown in this example) works in a similar fashion and is used to change a column name or data type. The Index attribute creates an index on the foreign key MakeId. The two text fields (Color and PetName)
are set to be Required and a max StringLength of 50 characters. The InverseProperty and ForeignKey
attributes are explained in the next section.
The changes from the EF Core conventions are as follows:
•Renaming the table from Cars to Inventory.
•Changing the TimeStamp column from varbinary(max) to the SQL Server timestamp data type.
•Setting the nullability for the Color and PetName columns from null to not null.
•Explicitly setting the size of the Color and PetName columns to nvarchar(50). This was already handled when the EF Core conventions for string properties were overridden but included here for visibility.
•Renaming the index on the MakeId column.
The rest of the annotations used match the configuration defined by the EF Core conventions. To confirm the changes, we examine the table created by EF Core:

CREATE TABLE [dbo].[Inventory](
[Id] [INT] IDENTITY(1,1) NOT NULL, [Color] [NVARCHAR](50) NOT NULL, [PetName] [NVARCHAR](50) NOT NULL, [MakeId] [INT] NOT NULL, [TimeStamp] [TIMESTAMP] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

ALTER TABLE [dbo].[Inventory] ADD DEFAULT (N”) FOR [Color] GO

ALTER TABLE [dbo].[Inventory] ADD DEFAULT (N”) FOR [PetName] GO

ALTER TABLE [dbo].[Inventory] WITH CHECK ADD CONSTRAINT [FK_Inventory_Makes_MakeId] FOREIGN KEY([MakeId]) REFERENCES [dbo].[Makes] ([Id]) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_Inventory_Makes_MakeId] GO

■ Note If you have been following along and running migrations with each of these changes, you might be surprised to see a failure when updating the TimeStamp column to the SQL Server timestamp data type. This is because SQL Server does not allow an existing column’s datatype to be changed to the timestamp datatype from another datatype. The column must be dropped and then re-added with the new timestamp datatype. EF Core sees the column as already existing and issues an alter statement and not the paired drop/add commands that are required to make the change. To update your database, comment out the TimeStamp property on the base class, create a new migration and apply it, and then uncomment the TimeStamp property and create another migration and apply it.

Note the defaults added to the Color and PetName columns. If there was any data that had null values for either of these columns, it would cause the migration to fail. This change ensures the change to not null will succeed by placing an empty string in those columns if they were null at the time of the migration being applied.
To change the Radio’s CarId property to map to a field named InventoryId, and make the RadioId
required and explicitly set the size to 50, update the Radio entity to the following (note the changes in bold):

namespace AutoLot.Samples.Models;

[Table(“Radios”, Schema=”dbo”)]
public class Radio : BaseEntity
{
public bool HasTweeters { get; set; } public bool HasSubWoofers { get; set; } [Required, StringLength(50)]
public string RadioId { get; set; }
[Column(“InventoryId”)]
public int CarId { get; set; }
[ForeignKey(nameof(CarId))]
public Car CarNavigation { get; set; }
}

The migration commands and the resulting table are shown here:

dotnet ef migrations add UpdateRadio -o Migrations -c AutoLot.Samples.ApplicationDbContext dotnet ef database update UpdateRadio -c AutoLot.Samples.ApplicationDbContext

CREATE TABLE [dbo].[Radios](
[Id] [INT] IDENTITY(1,1) NOT NULL,

[HasTweeters] [BIT] NOT NULL, [HasSubWoofers] [BIT] NOT NULL, [RadioId] [NVARCHAR](50) NOT NULL,
[InventoryId] [INT] NOT NULL, [TimeStamp] [TIMESTAMP] NULL,
CONSTRAINT [PK_Radios] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] GO

ALTER TABLE [dbo].[Radios] ADD DEFAULT (N”) FOR [RadioId] GO

ALTER TABLE [dbo].[Radios] WITH CHECK ADD CONSTRAINT [FK_Radios_Inventory_InventoryId] FOREIGN KEY([InventoryId]) REFERENCES [dbo].[Inventory] ([Id]) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Radios] CHECK CONSTRAINT [FK_Radios_Inventory_InventoryId] GO

As a final step in updating our models, update the Name property on the Make entity to be required, as well as set the max length of to 50, and do the same for the FirstName and LastName properties on the Driver entity:

//Make.cs
namespace AutoLot.Samples.Models;

[Table(“Makes”, Schema=”dbo”)]
public class Make : BaseEntity
{
[Required, StringLength(50)]
public string Name { get; set; }
[InverseProperty(nameof(Car.MakeNavigation))]
public IEnumerable Cars { get; set; } = new List();
}
//Driver.cs
namespace AutoLot.Samples.Models;

[Table(“Drivers”, Schema=”dbo”)]
public class Driver : BaseEntity
{
[Required, StringLength(50)]
public string FirstName { get; set; }

[Required, StringLength(50)]
public string LastName { get; set; }
[InverseProperty(nameof(Car.Drivers))]
public IEnumerable Cars { get; set; } = new List();
}

Annotations and Navigation Properties
The ForeignKey annotation lets EF Core know which property is the backing field for the navigation property. By convention, Id would automatically be set as the foreign key property. In the preceding examples, it is explicitly set for readability. This supports different naming styles as well as having more than one foreign key to the same table. Note that in a one-to-one relationship, only the dependent entity has a foreign key.
InverseProperty informs EF Core of how the entities relate by indicating the navigation property on the other entity that navigates back to this entity. InverseProperty is required when an entity relates to another entity more than once and also (in my honest opinion) makes the code more readable.

The Fluent API
The Fluent API configures the application entities through C# code. The methods are exposed by the ModelBuilder instance available in the DbContext OnModelCreating() method. The Fluent API is the most powerful of the configuration methods and overrides any conventions or data annotations that are in conflict. Some configuration options are available only using the Fluent API, such as setting default values and cascade behavior for navigation properties.

Class and Property Methods
The Fluent API is a superset of the data annotations when shaping your individual entities. It supports all of the functionality contained in the data annotations, but has additional capabilities, such as specifying composite keys and indices, and defining computed columns.

Class and Property Mapping
The following code shows the previous Car example with the Fluent API equivalent to the data annotations used (omitting the navigation properties, which will be covered next).

modelBuilder.Entity(entity =>
{
entity.ToTable(“Inventory”,”dbo”);
});

The following maps the CarId property of the Radio class to the InventoryId column of the Makes table:

modelBuilder.Entity(entity =>
{
entity.Property(e => e.CarId).HasColumnName(“InventoryId”);
});

Keys and Indices
To set the primary key for an entity, use the HasKey() method, as shown here:

modelBuilder.Entity(entity =>
{
entity.ToTable(“Inventory”,”dbo”);
entity.HasKey(e=>e.Id);
});

To set a composite key, select the properties that are in the key in the expression for the HasKey() method. For example, if the primary key for the Car entity should be the Id columns and an OrganizationId property, you would set it like this:

modelBuilder.Entity(entity =>
{
entity.ToTable(“Inventory”,”dbo”);
entity.HasKey(e=> new { e.Id, e.OrganizationId});
});

The process is the same for creating indices, except that it uses the HasIndex() Fluent API method. For example, to create an index named IX_Inventory_MakeId, use the following code:

modelBuilder.Entity(entity =>
{
entity.ToTable(“Inventory”,”dbo”); entity.HasKey(e=>e.Id);
entity.HasIndex(e => e.MakeId, “IX_Inventory_MakeId”);
});

To make the index unique, use the IsUnique() method. The IsUnique() method takes an optional bool
that defaults to true:

entity.HasIndex(e => e.MakeId, “IX_Inventory_MakeId”).IsUnique();

Field Size and Nullability
Properties are configured by selecting them using the Property() method and then using additional methods to configure the property. You already saw an example of this with the mapping of the CarId property to the InventoryId column.
The IsRequired() takes an optional bool that defaults to true and defines the nullability of the database column. The HasMaxLength() method sets the size of the column. Here is the Fluent API code that sets the Color and PetName properties as required with a max length of 50 characters:

modelBuilder.Entity(entity =>
{
…
entity.Property(e => e.Color)
.IsRequired()
.HasMaxLength(50); entity.Property(e => e.PetName)
.IsRequired()
.HasMaxLength(50);
});

Default Values
The Fluent API provides methods to set default values for columns. The default value can be a value type or a SQL string. For example, to set the default Color for a new Car to Black, use the following:

modelBuilder.Entity(entity =>
{
…
entity.Property(e => e.Color)
.IsRequired()
.HasMaxLength(50)
.HasDefaultValue(“Black”);
});

To set the value to a database function (like getdate()), use the HasDefaultValueSql() method.
Presume that a DateTime property named DateBuilt has been added to the Car class:

public class Car : BaseEntity
{
…
public DateTime? DateBuilt { get; set; }
}

The default value should be the current date using the SQL Server getdate() method. To configure this property to have this default, use the following Fluent API code:

modelBuilder.Entity(entity =>
{
…
entity.Property(e => e.DateBuilt)
.HasDefaultValueSql(“getdate()”);
});

SQL Server will use the result of the getdate() function if the DateBuilt property on the entity does not have a value when saved to the database.
A problem exists when a Boolean or numeric property has a database default value that contradicts the CLR default value. For example, if a Boolean property (such as IsDrivable) has a default set to true in the database, the database will set the value to true when inserting a record if a value isn’t specified for that column. This is, of course, the expected behavior on the database side of the equation. However, the default
CLR value for Boolean properties is false, which causes an issue due to how EF Core handles default values.
For example, add a bool property named IsDrivable to the Car class. If you are following along, make sure to create and apply a new migration to update the database.

//Car.cs
public class Car : BaseEntity
{
…
public bool IsDrivable { get; set; }
}

Before discussing default values, let’s examine the EF Core behavior for the Boolean datatype. Take the following code to create a new Car record with the IsDrivable set to false:

context.Cars.Add(new() { MakeId = 1, Color = “Rust”, PetName = “Lemon”, IsDrivable = false }); context.SaveChanges();

Here is the generated SQL for the insert:

exec sp_executesql N’SET NOCOUNT ON;
INSERT INTO [dbo].[Inventory] ([Color], [IsDrivable], [MakeId], [PetName], [Price]) VALUES (@p0, @p1, @p2, @p3, @p4);
SELECT [Id], [DateBuilt], [Display], [IsDeleted], [TimeStamp], [ValidFrom], [ValidTo] FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

‘,N’@p0 nvarchar(50),@p1 bit,@p2 int,@p3 nvarchar(50),@p4 decimal(18,2)’,@p0=N’Rust’,@p1=0, @p2=1,@p3=N’Lemon’,@p4=NULL

Now set the default for the property’s column mapping to true in the ApplicationDbContext’s OnModelCreating() method (once again creating and applying a new database migration):

//ApplicationDbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity(entity =>
{
…
entity.Property(e => e.IsDrivable).HasDefaultValue(true);
});

Executing the same code to insert the previous Car record generates different SQL:

exec sp_executesql N’SET NOCOUNT ON;
INSERT INTO [dbo].[Inventory] ([Color], [MakeId], [PetName], [Price]) VALUES (@p0, @p1, @p2, @p3);
SELECT [Id], [DateBuilt], [Display], [IsDeleted], [IsDrivable], [TimeStamp], [ValidFrom], [ValidTo]
FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

‘,N’@p0 nvarchar(50),@p1 int,@p2 nvarchar(50),@p3 decimal(18,2)’,@p0=N’Rust’,@p1=1, @p2=N’Lemon’,@p3=NULL

Notice that the IsDrivable column is not included in the insert statement. EF Core knows that the IsDrivable property’s value is the CLR default, and it knows that the column has a SQL Server default, so the column isn’t included in the statement. Therefore, when you save a new record with IsDrivable = false, the value is ignored, and the database default will be used. This means that the value for IsDrivable will always be true!

EF Core does alert you to this problem when you create a migration. In this particular example, this warning is output:

The ‘bool’ property ‘IsDrivable’ on entity type ‘Car’ is configured with a database- generated default. This default will always be used for inserts when the property has the value ‘false’, since this is the CLR default for the ‘bool’ type. Consider using the
nullable ‘bool?’ type instead, so that the default will only be used for inserts when the property value is ‘null’.

One solution for this is to make your public property (and therefore the column) nullable, since the default value for a nullable value type is null, so setting a Boolean property to false works as expected. However, changing the nullability of the property might not fit the business need.
Another solution is provided by EF Core and its support for backing fields. Recall from earlier that if a backing field exists (and is identified as the backing field for the property through convention, data
annotation, or Fluent API), then EF Core will use the backing field for read-write actions and not the public property.
If you update IsDrivable to use a nullable backing field (but keep the property non-nullable), EF Core will read-write from the backing field and not the property. The default value for a nullable bool is null and not false. This change now makes the property work as expected.

public class Car
{
…
private bool? _isDrivable; public bool IsDrivable
{
get => _isDrivable ?? true; set => _isDrivable = value;
}
}

The Fluent API is used to inform EF Core of the backing field.

modelBuilder.Entity(entity =>
{
entity.Property(p => p.IsDrivable)
.HasField(“_isDrivable”)
.HasDefaultValue(true);
});

■ Note The HasField() method is not necessary in this example since the name of the backing field follows the naming conventions. I included it to show how to use the Fluent apI to set it and to keep the code readable.

EF Core translates the field to the following SQL definition:

CREATE TABLE [dbo].[Inventory](
…
[IsDrivable] [BIT] NOT NULL,
… GO
ALTER TABLE [dbo].[Inventory] ADD DEFAULT (CONVERT([BIT],(1))) FOR [IsDrivable]
GO

Although not shown in the previous examples, numeric properties work the same way. If you are setting a non-zero default value, the backing field (or property itself if not using a backing field) must be nullable.
As a final note, the warning will still appear even when the fields are properly configured with nullable backing fields. The warning can be suppressed; however, I recommend leaving it in place as a reminder to check to make sure the field/property is properly configured. If you want to suppress it, set the following option in the DbContextOptions:

options.ConfigureWarnings(wc => wc.Ignore(RelationalEventId.BoolWithDefaultWarning));

RowVersion/Concurrency Tokens
To set a property as the rowversion datatype, use the IsRowVersion() method. To also set the property as a concurrency token, use the IsConcurrencyToken() method. The combination of these two methods has the same effect as the [Timestamp] data annotation:

modelBuilder.Entity(entity =>
{
…
entity.Property(e => e.TimeStamp)
.IsRowVersion()
.IsConcurrencyToken();
});

Concurrency checking will be covered in the next chapter.

SQL Server Sparse Columns
SQL Server sparse columns are optimized to store null values. EF Core 6 added support for sparse columns with the IsSparse() method in the Fluent API. The following code illustrates setting the fictitious IsRaceCar property to use SQl Server sparse columns:

modelBuilder.Entity(entity =>
{
entity.Property(p => p.IsRaceCare).IsSparse();
});

Computed Columns
Columns can also be set to computed based on the capabilities of the data store. For SQL Server, two of the options are to compute the value based on the value of other fields in the same record or to use a scalar function. For example, to create a computed column on the Inventory table that combines the PetName and Color values to create a property named Display, use the HasComputedColumnSql() function.
First add the new column to the Car class:

public class Car : BaseEntity
{
…
public string Display { get; set; }
}

Then add the Fluent API call to HasComputedColumnSql():

modelBuilder.Entity(entity =>
{
entity.Property(p => p.Display)
.HasComputedColumnSql(“[PetName] + ‘ (‘ + [Color] + ‘)'”);
});

Introduced in EF Core 5, the computed values can be persisted, so the value is calculated only on row creation or update. While SQL Server supports this, not all data stores do, so check the documentation of your database provider.

modelBuilder.Entity(entity =>
{
entity.Property(p => p.Display)
.HasComputedColumnSql(“[PetName] + ‘ (‘ + [Color] + ‘)'”, stored:true);

});

The DatabaseGenerated data annotation is often used in conjunction with the Fluent API to increase readability of the code. Here is the updated version of the Display property with the annotation included:

public class Car : BaseEntity
{
…
[DatabaseGenerated(DatabaseGeneratedOption.Computed)] public string Display { get; set; }
}

Check Constraints
Check constraints are a SQL Server feature that define a condition on a row that must be true. For example, in an ecommerce system, a check constraint can be added to make sure the quantity is greater than zero or that the price is greater than the discounted price. Since we don’t have any numeric values in our system, we will make a contrived constraint that prevents using the name “Lemon” in the Makes table.

Add the following to the OnModelCreating() method in the ApplicationDbContext class, which creates the check constraint preventing a Name of “Lemon” in the Makes table:

modelBuilder.Entity()
.HasCheckConstraint(name:”CH_Name”, sql:”[Name]<>‘Lemon'”, buildAction:c => c.HasName(“CK_Check_Name”));

The first parameter gives the constraint a name in the model, the second is the SQL for the constraint, and the final assigns the SQL Server name for the check constraint. Here is the check constraint as
defined in SQL:

ALTER TABLE [dbo].[Makes] WITH CHECK ADD CONSTRAINT [CK_Check_Name] CHECK (([Name]<>‘Lemon’))

Now, when a record with the Name of “Lemon” is added to the table, a SQL exception will be thrown.
Execute the following code to see the exception in action:

var context = new ApplicationDbContextFactory().CreateDbContext(null); context.Makes.Add(new Make { Name = “Lemon” });
context.SaveChanges();

This throws the following exception:

The INSERT statement conflicted with the CHECK constraint “CK_Check_Name”. The conflict occurred in database “AutoLotSamples”, table “dbo.Makes”, column ‘Name’.

Feel free to revert the migration for the check constraint and remove the migration, as the rest of the book doesn’t use the check constraint. It was added in this section for demonstration purposes.

One-to-Many Relationships
To use the Fluent API to define one-to-many relationships, pick one of the entities to update. Both sides of the navigation chain are set in one block of code.

modelBuilder.Entity(entity =>
{
…
entity.HasOne(d => d.MakeNavigation)
.WithMany(p => p.Cars)
.HasForeignKey(d => d.MakeId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName(“FK_Inventory_Makes_MakeId”);
});

If you select the principal entity as the base for the navigation property configuration, the code looks like this:

modelBuilder.Entity(entity =>
{
…
entity.HasMany(e=>e.Cars)

.WithOne(c=>c.MakeNavigation)
.HasForeignKey(c=>c.MakeId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName(“FK_Inventory_Makes_MakeId”);
});

One-to-One Relationships
One-to-one relationships are configured the same way, except that the WithOne() Fluent API method is used instead of WithMany(). Also, a unique index is required on the dependent entity and will be created automatically if one is not defined. The following example explicitly creates the unique index to specify the name. Here is the code for the relationship between the Car and Radio entities using the dependent entity (Radio):

modelBuilder.Entity(entity =>
{
entity.HasIndex(e => e.CarId, “IX_Radios_CarId”)
.IsUnique();

entity.HasOne(d => d.CarNavigation)
.WithOne(p => p.RadioNavigation)
.HasForeignKey(d => d.CarId);
});

If the relationship is defined on a principal entity, a unique index will still be added to the dependent entity. Here is the code for the relationship between the Car and Radio entities using the principal entity for the relationship and specifying the name of the index on the dependent entity:

modelBuilder.Entity(entity =>
{
entity.HasIndex(e => e.CarId, “IX_Radios_CarId”)
.IsUnique();
});

modelBuilder.Entity(entity =>
{
entity.HasOne(d => d.RadioNavigation)
.WithOne(p => p.CarNavigation)
.HasForeignKey(d => d.CarId);
});

Many-to-Many Relationships
Many-to-many relationships are much more customizable with the Fluent API. The foreign key field names, index names, and cascade behavior can all be set in the statements that define the relationship. It also allows for specifying the pivot table directly, which allows for additional fields to be added and for simplified querying.

Start by adding a CarDriver entity:

//CarDriver.cs
namespace AutoLot.Samples.Models;

[Table(“InventoryToDrivers”, Schema = “dbo”)] public class CarDriver : BaseEntity
{
public int DriverId {get;set;} [ForeignKey(nameof(DriverId))]
public Driver DriverNavigation {get;set;}

[Column(“InventoryId”)] public int CarId {get;set;} [ForeignKey(nameof(CarId))]
public Car CarNavigation {get;set;}
}

Add a DbSet for the new entity into the ApplicationDbContext: public DbSet CarsToDrivers {get;set;}
Next, update the Car entity to add a navigation property for the new CarDriver entity:

public class Car : BaseEntity
{
…
[InverseProperty(nameof(CarDriver.CarNavigation))]
public IEnumerable CarDrivers { get; set; } = new List();
}

Now, update the Driver entity for the navigation property to the CarDriver entity:

public class Driver : BaseEntity
{
[InverseProperty(nameof(CarDriver.DriverNavigation))]
public IEnumerable CarDrivers { get; set; } = new List();
}

Finally, add in the Fluent API code for the many-to-many relationship:

modelBuilder.Entity()
.HasMany(p => p.Drivers)
.WithMany(p => p.Cars)
.UsingEntity( j => j
.HasOne(cd => cd.DriverNavigation)
.WithMany(d => d.CarDrivers)
.HasForeignKey(nameof(CarDriver.DriverId))
.HasConstraintName(“FK_InventoryDriver_Drivers_DriverId”)
.OnDelete(DeleteBehavior.Cascade),

j => j
.HasOne(cd => cd.CarNavigation)
.WithMany(c => c.CarDrivers)
.HasForeignKey(nameof(CarDriver.CarId))
.HasConstraintName(“FK_InventoryDriver_Inventory_InventoryId”)
.OnDelete(DeleteBehavior.ClientCascade), j =>
{
j.HasKey(cd => new { cd.CarId, cd.DriverId });
});

Excluding Entities from Migrations
If an entity is shared between multiple DbContexts, each DbContext will create code in the migration files for creation of or changes to that entity. This causes a problem since the second migration script will fail if the changes are already present in the database. Prior to EF Core 5, the only solution was to manually edit one of the migration files to remove those changes.
In EF Core 5, a DbContext can mark an entity as excluded from migrations, letting the other DbContext become the system of record for that entity. The following code shows an entity being excluded from migrations:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity().ToTable(“Logs”, t => t.ExcludeFromMigrations());
}

Using IEntityTypeConfiguration Classes
As you might have surmised at this stage of working with the Fluent API, the OnModelCreating() method can become quite lengthy (and unwieldy) the more complex your model becomes. Introduced in EF Core 6, the IEntityTypeConfiguration interface and the EntityTypeConfiguration attribute allow for moving the Fluent API configuration for an entity into its own class. This makes for a cleaner ApplicationDbContext and supports the separation of concerns design principle.
Start by creating a new directory named Configuration in the Models directory. In this new directory, add a new file named CarConfiguration.cs, make it public, and implement the IEntityTypeConfiguration interface, like this:

namespace AutoLot.Samples.Models.Configuration;

public class CarConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
}
}

Next, move the contents of the configuration for the Car entity from the OnModelCreating() method in the ApplicationDbContext into the Configure() method of the CarConfiguration class. Replace the entity variable with the builder variable so the Configure() method looks like this:

public void Configure(EntityTypeBuilder builder)
{
builder.ToTable(“Inventory”, “dbo”); builder.HasKey(e => e.Id);
builder.HasIndex(e => e.MakeId, “IX_Inventory_MakeId”); builder.Property(e => e.Color)
.IsRequired()
.HasMaxLength(50)
.HasDefaultValue(“Black”); builder.Property(e => e.PetName)
.IsRequired()
.HasMaxLength(50);
builder.Property(e => e.DateBuilt).HasDefaultValueSql(“getdate()”); builder.Property(e => e.IsDrivable)
.HasField(“_isDrivable”)
.HasDefaultValue(true); builder.Property(e => e.TimeStamp)
.IsRowVersion()
.IsConcurrencyToken();
builder.Property(e => e.Display).HasComputedColumnSql(“[PetName] + ‘ (‘ + [Color] + ‘)'”, stored: true);
builder.HasOne(d => d.MakeNavigation)
.WithMany(p => p.Cars)
.HasForeignKey(d => d.MakeId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName(“FK_Inventory_Makes_MakeId”);
}

This configuration also works with the fluent many-to-many configuration between Car and Driver. It’s your choice whether to add the configuration into the CarConfiguration class or create a DriverConfiguration class. For this example, move it into the CarConfiguration class at the end of the Configure() method:

public void Configure(EntityTypeBuilder builder)
{
…
builder
.HasMany(p => p.Drivers)
.WithMany(p => p.Cars)
.UsingEntity( j => j
.HasOne(cd => cd.DriverNavigation)
.WithMany(d => d.CarDrivers)
.HasForeignKey(nameof(CarDriver.DriverId))
.HasConstraintName(“FK_InventoryDriver_Drivers_DriverId”)
.OnDelete(DeleteBehavior.Cascade), j => j
.HasOne(cd => cd.CarNavigation)
.WithMany(c => c.CarDrivers)
.HasForeignKey(nameof(CarDriver.CarId))
.HasConstraintName(“FK_InventoryDriver_Inventory_InventoryId”)
.OnDelete(DeleteBehavior.ClientCascade),

j =>
{
j.HasKey(cd => new { cd.CarId, cd.DriverId });
});
}

Update the GlobalUsings.cs file to include the new namespace for the configuration classes:

global using AutoLot.Samples.Models.Configuration;

Replace all the code in the OnModelBuilding() method (in the ApplicationDbContext.cs class) that configures the Car class and the Car to Driver many-to-many relationship with the following single line of code:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
new CarConfiguration().Configure(modelBuilder.Entity());
…
}

The final step for the Car class is to add the EntityTypeConfiguration attribute:

[Table(“Inventory”, Schema = “dbo”)] [Index(nameof(MakeId), Name = “IX_Inventory_MakeId”)] [EntityTypeConfiguration(typeof(CarConfiguration))] public class Car : BaseEntity
{
…
}

Next, repeat the same steps for the Radio Fluent API code. Create a new class named RadioConfiguration, implement the IEntityTypeConfiguration interface, and add the code from the ApplicationDbContext OnModelBuilding() method:

namespace AutoLot.Samples.Models.Configuration;

public class RadioConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.Property(e => e.CarId).HasColumnName(“InventoryId”); builder.HasIndex(e => e.CarId, “IX_Radios_CarId”).IsUnique(); builder.HasOne(d => d.CarNavigation)
.WithOne(p => p.RadioNavigation)
.HasForeignKey(d => d.CarId);
}
}

Update the OnModelCreating() method in the ApplicationDbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
new CarConfiguration().Configure(modelBuilder.Entity());
new RadioConfiguration().Configure(modelBuilder.Entity());
}

Finally, add the EntityTypeConfiguration attribute to the Radio class:

[Table(“Radios”, Schema = “dbo”)] [EntityTypeConfiguration(typeof(RadioConfiguration))] public class Radio : BaseEntity
{
…
}

While this didn’t reduce the total number of lines of code, this new feature made the
ApplicationDbContext a lot cleaner.

Conventions, Annotations, and the Fluent API, Oh My!
At this point in the chapter, you might be wondering which of the three options to use to shape your entities and their relationship to each other and the data store. The answer is all three. The conventions are always active (unless you override them with data annotations or the Fluent API). The data annotations can do almost everything the Fluent API methods can do and keep the information in the entity class themselves, which can increase code readability and support. The Fluent API is the most powerful of all three. Whether you use data annotations or the Fluent API, know that data annotations overrule the built-in conventions, and the methods of the Fluent API overrule everything.

Owned Entity Types
There will be time when two or more entities contain the same set of properties. Using a C# class as a property on an entity to define a collection of properties for another entity was first introduced in EF Core version 2.0. When types marked with the [Owned] attribute (or configured with the Fluent API) are added as a property of an entity, EF Core will add all the properties from the [Owned] entity class to the owning entity. This increases the possibility of C# code reuse.
Behind the scenes, EF Core considers this a one-to-one relation. The owned class is the dependent entity, and the owning class is the principal entity. The owned class, even though it is considered an entity, cannot exist without the owning entity. The default column names from the owned type will be formatted as NavigationPropertyName_OwnedEntityPropertyName (e.g., PersonalNavigation_FirstName). The default names can be changed using the Fluent API.
Take this Person class (notice the Owned attribute):

namespace AutoLot.Samples.Models;

[Owned]
public class Person
{
[Required, StringLength(50)]

public string FirstName { get; set; } [Required, StringLength(50)]
public string LastName { get; set; }
}

With this in place, we can replace the FirstName and LastName properties on the Driver class with the new Person class:

namespace AutoLot.Samples.Models;

[Table(“Drivers”, Schema = “dbo”)] public class Driver : BaseEntity
{
public Person PersonInfo {get;set;} = new Person();
public IEnumerable Cars { get; set; } = new List(); [InverseProperty(nameof(CarDriver.DriverNavigation))]
public IEnumerable CarDrivers { get; set; } = new List();
}

By default, the two Person properties are mapped to columns named PersonInfo_FirstName and PersonInfo_LastName. To change this, first add a new file named DriverConfiguration.cs into the Configuration folder, and update the code to the following:

namespace AutoLot.Samples.Models.Configuration;

public class DriverConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.OwnsOne(o => o.PersonInfo, pd =>
{
pd.Property(nameof(Person.FirstName))
.HasColumnName(nameof(Person.FirstName))
.HasColumnType(“nvarchar(50)”); pd.Property(nameof(Person.LastName))
.HasColumnName(nameof(Person.LastName))
.HasColumnType(“nvarchar(50)”);
});
}
}

Update the ApplicationDbContext OnConfiguring() method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
new CarConfiguration().Configure(modelBuilder.Entity());
new RadioConfiguration().Configure(modelBuilder.Entity());
new DriverConfiguration().Configure(modelBuilder.Entity());
}

Finally, update the Driver class:

[Table(“Drivers”, Schema = “dbo”)] [EntityTypeConfiguration(typeof(DriverConfiguration))] public class Driver : BaseEntity
{
…
}

The Drivers table is updated like this (note that the nullability of the FirstName and LastName columns doesn’t match the Required data annotations on the Person owned entity):

CREATE TABLE [dbo].[Drivers](
[Id] [INT] IDENTITY(1,1) NOT NULL, [FirstName] [NVARCHAR](50) NULL, [LastName] [NVARCHAR](50) NULL, [TimeStamp] [TIMESTAMP] NULL,
CONSTRAINT [PK_Drivers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]] GO

While the Person class has the Required data annotation on both of its properties, the SQL Server columns are both set as NULL. This is due to an issue with how the migration system translates owned entities when they are used with an optional relationship.
To correct this, there are a couple of options. The first is to enable C# null reference types (at the project level or in the classes). This makes the PersonInfo navigation property non-nullable, which EF Core honors, and in turn EF Core then appropriately configures the columns in the owned entity. The other option is to add a Fluent API statement to make the navigation property required.

public class DriverConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
…
builder.Navigation(d=>d.PersonInfo).IsRequired(true);
}
}

This updates the properties of the Person owned type to be set as a not null column in SQL Server:

CREATE TABLE [dbo].[Drivers](
[Id] [INT] IDENTITY(1,1) NOT NULL, [FirstName] [NVARCHAR](50) NOT NULL, [LastName] [NVARCHAR](50) NOT NULL, [TimeStamp] [TIMESTAMP] NULL,

CONSTRAINT [PK_Drivers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] GO

There are four limitations when using owned types:
•You cannot create a DbSet for an owned type.
•You cannot call Entity() with an owned type on ModelBuilder.
•Instances of an owned entity type cannot be shared between multiple owners.
•Owned entity types cannot have inheritance hierarchies.

There are additional options to explore with owned entities, including collections, table splitting, and nesting. These are all beyond the scope of this book. To find out more information, consult the EF Core documentation on owned entities here: https://docs.microsoft.com/en-us/ef/core/modeling/owned- entities.

Query Types
Query types are DbSet collections that are used to represent views, a SQL statement, or tables without a primary key. Prior versions of EF Core used DbQuery for these, but from EF Core 3.x on, the DbQuery type has been retired. Query types are added to the derived DbContext using DbSet properties and are configured as keyless.
Query types are usually used to represent combinations of tables, such as combining the details from the Make and Inventory tables. Take this query, for example:

SELECT m.Id MakeId, m.Name Make,
i.Id CarId, i.IsDrivable, i.Display, i.DateBuilt, i.Color, i.PetName FROM dbo.Makes m
INNER JOIN dbo.Inventory i ON i.MakeId = m.Id

To hold the results of this query, create a new folder named ViewModels, and in that folder create a new class named CarMakeViewModel:
namespace AutoLot.Samples.ViewModels; [Keyless]
public class CarMakeViewModel
{
public int MakeId { get; set; } public string Make { get; set; } public int CarId { get; set; } public bool IsDrivable { get; set; } public string Display { get; set; }
public DateTime DateBuilt { get; set; }

public string Color { get; set; } public string PetName { get; set; }

[NotMapped]
public string FullDetail => $” The {Color} {Make} is named {PetName}”; public override string ToString() => FullDetail;
}

The Keyless attribute instructs EF Core that this entity is a query type and will never be used for updates and is to be excluded from the change tracker when queried. Note the use of the NotMapped attribute to create a display string that combines several properties into a single, human-readable string. Update the ApplicationDbContext to include a DbSet for the view model:

public class ApplicationDbContext : DbContext
{
…

…
}

public DbSet Cars { get; set; } public DbSet Makes { get; set; } public DbSet Radios { get; set; } public DbSet Drivers { get; set; }
public DbSet< CarMakeViewModel> CarMakeViewModels { get; set; }

Update the GlobalUsings.cs file to include the new namespace for the view model and the configuration (which will be created next):

global using AutoLot.Samples.ViewModels;
global using AutoLot.Samples.ViewModels.Configuration;

The remainder of the configuration takes place in the Fluent API. Create a new folder named Configuration under the ViewModels folder, and in that folder create a new class named CarMakeViewModelConfiguration and update the code to the following:

namespace AutoLot.Samples.ViewModels.Configuration;

public class CarMakeViewModelConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
}
}

Update the CarMakeViewModel class to add the EntityTypeConfiguration attribute:

[Keyless] [EntityTypeConfiguration(typeof(CarMakeViewModelConfiguration))] public class CarMakeViewModel : INonPersisted
{
…
}

Update the OnModelCreating() method to the following:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
new CarConfiguration().Configure(modelBuilder.Entity());
new RadioConfiguration().Configure(modelBuilder.Entity()); new DriverConfiguration().Configure(modelBuilder.Entity());
new CarMakeViewModelConfiguration().Configure(modelBuilder.Entity());
}

The following example sets the entity as keyless and maps the query type to raw SQL query. The HasNoKey() Fluent API method is not necessary if the Keyless data annotation is on the model, and vice versa, but is shown in this example for completeness):

public void Configure(EntityTypeBuilder builder)
{
builder.HasNoKey().ToSqlQuery(@”
SELECT m.Id MakeId, m.Name Make, i.Id CarId, i.IsDrivable, i.DisplayName, i.DateBuilt, i.Color, i.PetName
FROM dbo.Makes m
INNER JOIN dbo.Inventory i ON i.MakeId = m.Id”);
}

Query types can also be mapped to a database view. Presuming there was a view named dbo.
CarMakeView, the configuration would look like this:

builder.HasNoKey().ToView(“CarMakeView”, “dbo”);

■ Note when using EF Core migrations to update your database, query types that are mapped to a view do not get created as tables. Query types that are not mapped to views are created as keyless tables.

If you don’t want the view model mapped to a table in your database and don’t have a view to map, use the following overload of the ToTable() method to exclude the item from migrations:

builder.ToTable( x => x.ExcludeFromMigrations());

The final mechanisms that query types can be used with are the FromSqlRaw() and FromSqlInterpolated() methods. These will be covered in detail in the next chapter, but here is a sneak peek:

var records = context.CarMakeViewModel.FromSqlRaw(
@” SELECT m.Id MakeId, m.Name Make, i.Id CarId, i.IsDrivable, i.Display, i.DateBuilt, i.Color, i.PetName
FROM dbo.Makes m
INNER JOIN dbo.Inventory i ON i.MakeId = m.Id “);

Flexible Query/Table Mapping
EF Core 5 introduced the ability to map the same class to more than one database object. These objects can be tables, views, or functions. For example, CarViewModel from Chapter 20 can be mapped to a view that returns the make name with the Car data and the Inventory table. EF Core will then query from the view and send updates to the table.

modelBuilder.Entity()
.ToTable(“Inventory”)
.ToView(“InventoryWithMakesView”);

Query Execution
Data retrieval queries are created with LINQ queries written against the DbSet properties. The LINQ query is changed to the database-specific language (e.g., T-SQL) by the database provider’s LINQ translation engine and executed on the server side. Multirecord (or potential multirecord) LINQ queries are not executed until the query is iterated over (e.g., using a foreach) or bound to a control for display (like a data grid). This deferred execution allows building up queries in code without suffering performance issues from chattiness with the database or retrieving more records than intended.
For example, to get all yellow Car records from the database, execute the following query:

//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
var cars = context.Cars.Where(x=>x.Color == “Yellow”);

With deferred execution, that database is not actually queried until the results are iterated over. To have the query execute immediately, use ToList().

var listOfCars = context.Cars.Where(x=>x.Color == “Yellow”).ToList();

Since queries aren’t executed until triggered, they can be built up over multiple lines of code. The following code sample executes the same as the previous example:

var query = context.Cars.AsQueryable(); query = query.Where(x=>x.Color == “Yellow”); var moreCars = query.ToList();

Single-record queries (such as when using First()/FirstOrDefault()) execute immediately on calling the action (such as FirstOrDefault()), and create, update, and delete statements are executed immediately when the DbContext.SaveChanges() method is executed.

■ Note The next chapters covers executing CRUD operations in great detail.

Mixed Client-Server Evaluation
Prior versions of EF Core introduced the ability to mix server-side and client-side execution. This meant that a C# function could be used in the middle of a LINQ statement and essentially negate what I described in the previous paragraph. The part up to the C# function would execute on the server side, but then all of the

results (at that point of the query) were brought back on the client side, and then the rest of the query would execute as LINQ to Objects. This ended up causing more problems than it solved, and with the release of EF Core 3.1, this functionality was changed. Now, only the final node of a LINQ statement can execute on the client side.

Tracking vs. NoTracking Queries
When data is read from the database into a DbSet instance with a primary key, the entities (by default) are tracked by the change tracker. This is typically what you want in your application. Any changes to the item can then be persisted to the database merely by calling SaveChanges() on your derived DbContext instance without any additional work on your part. Also, once an instance is tracked by the change tracker, any further calls to the database for that same item (based on the primary key) will result in an update of the item and not a duplication.
However, there might be times when you need to get some data from the database, but you don’t want it to be tracked by the change tracker. The reason might be performance (tracking original and current values for a large set of records can add memory pressure), or maybe you know those records will never be changed by the part of the application that needs the data.
To load data into a DbSet instance without adding the data to the ChangeTracker, add AsNoTracking() into the LINQ statement. This signals EF Core to retrieve the data without adding it into the ChangeTracker. For example, to load a Car record without adding it into the ChangeTracker, execute the following:

var untrackedCar = context.Cars.Where(x=>x.Id ==1).AsNoTracking();

This provides the benefit of not adding the potential memory pressure with a potential drawback: additional calls to retrieve the same Car will create additional copies of the record. At the expense of using more memory and having a slightly slower execution time, the query can be modified to ensure there is only one instance of the unmapped Car.

var untrackedWithIdResolution =
context.Cars.Where(x=>x.Id == 1).AsNoTrackingWithIdentityResolution();

Query types are never tracked since they cannot be updated. The exception to this is when using flexible query/table mapping. In that case, instances are tracked by default so they can be persisted to the target table.

Code First vs. Database First
Whether you are building a new application or adding EF Core into an existing application, you will fall into one of two camps: you have an existing database that you need to work with, or you don’t yet have a database and need to create one.
Code first means that you create and configure your entity classes and the derived DbContext in code and then use migrations to update the database. This is how most greenfield, or new, projects are
developed. The advantage is that as you build your application, your entities evolve based on the needs of your application. The migrations keep the database in sync, so the database design evolves along with your application. This emerging design process is popular with agile development teams, as you build the right parts at the right time.

If you already have a database or prefer to have your database design drive your application, that is referred to as database first. Instead of creating the derived DbContext and all of the entities manually, you scaffold the classes from the database. When the database changes, you need to re-scaffold your classes to keep your code in sync with the database. Any custom code in the entities or the derived DbContext must be placed in partial classes so it doesn’t get overwritten when the classes are re-scaffolded. Fortunately, the scaffolding process creates partial classes just for that reason.
Whichever method you chose, code first or database first, know that it is essentially a commitment. If you are using code first, all changes are made to the entity and context classes, and the database is updated using migrations. If you are working database first, all changes must be made in the database, and then the classes are re-scaffolded. With some effort and planning, you can switch from database first to code first (and vice versa), but you should not be making manual changes to the code and the database at the same time.

The EF Core Global Tool CLI Commands
The dotnet-ef global CLI tool EF Core tooling contains the commands needed to scaffold existing databases into code, to create/remove database migrations, and to operate on a database (update, drop, etc.). Before you can use the dotnet-ef global tooling, it must be installed with the following command:

dotnet tool install –global dotnet-ef –version 6.0.0

■ Note If you have an earlier version of the EF Core command-line tooling installed, you will need to uninstall the older version before installing the latest version. To uninstall the global tool, use
dotnet tool uninstall –global dotnet-ef.

To test the install, open a command prompt and enter the following command:

dotnet ef

If the tooling is successfully installed, you will get the EF Core Unicorn (the team’s mascot) and the list of available commands, like this (the unicorn looks better on the screen):

_/\
—==/ \\
|. \|\
| || | | ) \\\
| _| | _| \_/ | //|\\
| ||_| / \\\/\\

Entity Framework Core .NET Command-line Tools 6.0.0 Usage: dotnet ef [options] [command]
Options:
–version Show version information
-h|–help Show help information
-v|–verbose Show verbose output.
–no-color Don’t colorize output.
–prefix-output Prefix output with level.

Commands:
database Commands to manage the database. dbcontext Commands to manage DbContext types. migrations Commands to manage migrations.

Use “dotnet ef [command] –help” for more information about a command.

Table 21-9 describes the three main commands in the EF Core global tool. Each main command has additional subcommands. As with all the .NET commands, each command has a rich help system that can be accessed by entering -h along with the command.

Table 21-9. EF Core Tooling Commands

Command Meaning in Life
Database Commands to manage the database. Subcommands include drop and update.
DbContext Commands to manage the DbContext types. Subcommands include scaffold, list, and
info.
Migrations Commands to manage migrations. Subcommands include add, list, remove, and script.

The EF Core commands execute on .NET project files. The target project needs to reference the EF Core tooling NuGet package Microsoft.EntityFrameworkCore.Design. The commands operate on the project file located in the same directory where the commands are run, or a project file in another directory if referenced through the command-line options.
For the EF Core CLI commands that need an instance of a derived DbContext class (Database and Migrations), if there is only one in the project, that one will be used. If there are more than one, then the DbContext needs to be specified in the command-line options. The derived DbContext class will be instantiated using an instance of a class implementing the IDesignTimeDbContextFactory interface if one can be located. If the tooling cannot find one, the derived DbContext will be instantiated using the parameterless constructor. If neither exists, the command will fail. Note that the using the parameterless constructor (and not the constructor that takes in the DbContextOptions) requires the existence of the OnConfiguring override, which is not considered a good practice to have. The best (and really only) option is to always create an IDesignTimeDbContextFactory for each derived DbContext that you have in your application.
There are common options available for the EF Core commands, shown in Table 21-10. Many of the commands have additional options or arguments.

Table 21-10. EF Core Command Options

Option (Shorthand || Longhand) Meaning in Life
–c || –context The fully qualified derived DbContext class to use. If more than one derived DbContext exists in the project, this is a required option.
-p || –project The project to use (where to place the files). Defaults to the current working directory.
-s || –startup-project
The startup project to use (contains the derived DbContext). Defaults to the current working directory.
-h || –help Displays the help and all of the options.
-v || –verbose Shows verbose output.

To list all the arguments and options for a command, enter dotnet ef -h in a command window, like this:

dotnet ef migrations add -h

■ Note It is important to note that the CLI commands are not C# commands, so the rules of escaping slashes and quotes do not apply.

The Migrations Commands
The migrations commands are used to add, remove, list, and script migrations. As migrations are applied to a base, a record is created in the EFMigrationsHistory table. Table 21-11 describes the commands. The following sections explain the commands in detail.

Table 21-11. EF Core Migrations Commands

Command Meaning in Life
Add Creates a new migration based on the changes from the previous migration
Remove Checks if the last migration in the project has been applied to the database and, if not, deletes the migration file (and its designer) and then rolls back the snapshot class to the previous migration
List Lists all of the migrations for a derived DbContext and their status (applied or pending)
Bundle Creates an executable to update the database.
Script Creates a SQL script for all, one, or a range of migrations

The Add Command
The add command creates a new database migration based on the current object model. The process examines every entity with a DbSet property on the derived DbContext (and every entity that can be reached from those entities using navigation properties) and determines whether there are any changes that need to be applied to the database. If there are changes, the proper code is generated to update the database. You’ll learn more about that shortly.
The Add command requires a name argument, which is used to name the create class and files for the migration. In addition to the common options, the option -o or –output-dir indicates where the migration files should go. The default directory is named Migrations relative to the current path.
Each migration added creates two files that are partials of the same class. Both files start their name with a timestamp and the migration name used as the argument to the add command. The first file is named
_.cs, and the second is named _. Designer.cs. The timestamp is based on when the file was created and will match exactly for both files.
The first file represents the code generated for the database changes in this migration, and the designer file represents the code to create and update the database based on all migrations up to and including this one.

The main file contains two methods, Up() and Down(). The Up() method contains the code to update the database with this migration’s changes, and the Down() method contains the code to roll back this migration’s changes. A partial listing of the initial migration from earlier in this chapter is listed here (all of the migrations used in the previous examples are in the AutoLot.Samples project in the companion code):

public partial class Radio : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable( name: “Make”,
columns: table => new
{
Id = table.Column(type: “int”, nullable: false)
.Annotation(“SqlServer:Identity”, “1, 1”),
Name = table.Column(type: “nvarchar(max)”, nullable: true), TimeStamp = table.Column(type: “varbinary(max)”, nullable: true)
},
constraints: table =>
{

…

}

table.PrimaryKey(“PK_Make”, x => x.Id);
});

migrationBuilder.CreateIndex( name: “IX_Cars_MakeId”, table: “Cars”,
column: “MakeId”);

protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(name: “Cars”); migrationBuilder.DropTable(name: “Make”);
}
}

As you can see, the Up() method is creating tables, columns, indexes, etc. The Down() method is dropping the items created. The migrations engine will issue alter, add, and drop statements as necessary to ensure the database matches your model.
The designer file contains two attributes that tie these partials to the filename and the derived
DbContext. The attributes are shown here with a partial list of the design class:

[DbContext(typeof(ApplicationDbContext))] [Migration(“20210613000105_Radio”)] partial class Radio
{
protected override void BuildTargetModel(ModelBuilder modelBuilder)
{
//omitted for brevity
}
}

The first migration creates an additional file in the target directory named for the derived DbContext in the format of ModelSnapshot.cs. The format of this file is the same as the designer partial and contains the code that is the sum of all migrations. When migrations are added or removed, this file is automatically updated to match the changes.

■ Note It is extremely important that you don’t delete migration files manually. This will result in the ModelSnapshot.cs becoming out of sync with your migrations, essentially breaking them. If you are going to manually delete them, delete them all and start over. To remove a migration, use the remove command, covered shortly.

The Remove Command
The remove command is used to remove migrations from the project and always operates on the last migration (based on the timestamps of the migrations). When removing a migration, EF Core will make sure it hasn’t been applied by checking the EFMigrationsHistory table in the database. If the migration has been applied, the process fails. If the migration hasn’t yet been applied or has been rolled back, the migration is removed, and the model snapshot file is updated.
The remove command doesn’t take any arguments (since it always works on the last migration) and uses the same options as the add command. There is one additional option, the force option (-f || –force).
This will roll back the last migration and then remove it in one step.

The List Command
The list command is used to show all the migrations for a derived DbContext. By default, it will list all migrations and query the database to determine whether they have been applied. If they have not been applied, they will be listed as pending. There is an option to pass in a specific connection string and another option to not connect to the database at all and instead just list the migrations. Table 21-12 shows those options.

Table 21-12. Additional Options for EF Core Migrations List Command

Option (Shorthand ||
Longhand) Meaning in Life
–connection
Connection string to the database. Defaults to the one specified in the instance of
IDesignTimeDbContextFactory or the DbContext’s OnConfiguring method.
–no-connect Instructs the command to skip the database check.

The Bundle Command
The bundle command creates an executable to update the database. The generated executable, built for a target runtime (e.g., Windows, Linux), will apply all contained migrations to the database. Table 21-13 describes the most commonly used arguments with the bundle command.

Table 21-13. Common Arguments for the EF Core Migrations Bundle Command

Argument Meaning in Life
-o | –output The path to the executable file to create.
-f | –force Overwrite existing files.
–self-contained Also bundle the .NET runtime with the executable.
-r | –target-runtime
Target runtime to bundle for. If no runtime is specified, the file will use the runtime of the current machine’s operating system.

The executable will use the connection string from the IDesignTimeDbContextFactory; however, another connection string can be passed into the executable using the –connection flag. If the migrations have already been applied to the target database, they will not be reapplied.
When applying the –self-contained flag, the size of the executable will grow significantly. On my machine with the sample project from this chapter, the regular bundle file is 11MB in size, while the self- contained file is 74MB.

The Script Command
The script command creates a SQL script based on one or more migrations. The command takes two optional arguments representing the migration to start with and the migration to end with. If neither is entered, all migrations are scripted. Table 21-14 describes the arguments.

Table 21-14. Arguments for the EF Core Migrations Script Command

Argument Meaning in Life
The starting migration. Defaults to 0 (zero), the starting migration.
The target migration. Defaults to the last migration.

If no migrations are named, the script created will be the cumulative total of all the migrations. If named migrations are provided, the script will contain the changes between the two migrations (inclusive). Each migration is wrapped in a transaction. If the EFMigrationsHistory table does not exist in the database where the script is executed, it will be created. The table will also be updated to match the migrations that were executed. Some examples are shown here:

//Script all of the migrations dotnet ef migrations script
//script from the beginning to the Many2Many migrations dotnet ef migrations script 0 Many2Many

There are some additional options available, as shown in Table 21-15. The -o option allows you to specify a file for the script (the directory is relative to where the command is executed), and -i creates an idempotent script. This means it contains checks to see whether a migration has already been applied and skips that migration if it has. The –no-transaction option disables the normal transactions that are added to the script.

Table 21-15. Additional Options for the EF Core Migrations Script Command

Option (Shorthand ||
Longhand) Meaning in Life
-o || -output The file to write the resulting script to
-i || –idempotent Generates a script that checks if a migration has already been applied before applying it
–no-transactions Does not wrap each migration in a transaction

The Database Commands
There are two database commands, drop and update. The drop command deletes the database if it exists. The update command updates the database using migrations.

The Drop Command
The drop command drops the database specified by the connection string in the context factory of the OnConfiguring method of DbContext. Using the force option does not ask for confirmation and force closes all connections. See Table 21-16.

Table 21-16. EF Core Database Drop Options

Option (Shorthand || Longhand) Meaning in Life
-f || –force Don’t confirm the drop. Force close all connections.
–dry-run Show which database will be dropped but don’t drop it.

The Database Update Command
The update command takes one argument (the migration name) and the usual options. The command has one additional option, –connection . This allows for using a connection string that isn’t configured in the design-time factory or DbContext.
If the command is executed without a migration name, the command updates the database to the most recent migration, creating the database if necessary. If a migration is named, the database will be updated to that migration. All previous migrations that have not yet been applied will be applied as well. As migrations are applied, their names are stored in the EFMigrationsHistory table.
If the named migration has a timestamp that is earlier than other applied migrations, all later migrations are rolled back. If a 0 (zero) is passed in as the named migration, all migrations are reverted, leaving an empty database (except for the EFMigrationsHistory table).

The DbContext Commands
There are four DbContext commands. Three of them (list, info, script) operate on derived DbContext classes in your project. The scaffold command creates a derived DbContext and entities from an existing database. Table 21-17 shows the available commands.

Table 21-17. The DbContext Commands

Command Meaning in Life
Info Gets information about a DbContext type
List Lists available DbContext types
Optimize Generates a compiled version of the model used by the DbContext
Scaffold Scaffolds a DbContext and entity types for a database
Script Generates SQL script from the DbContext based on the object model, bypassing any migrations

The list and info commands have the usual options available. The list command lists the derived DbContext classes in the target project. The info command provides details about the specified derived DbContext class, including the connection string, provider name, database name, and data source. The script command creates a SQL script that creates your database based on the object model, ignoring any migrations that might be present. The scaffold command is used to reverse engineer an existing database and is covered in the next section.

The DbContext Scaffold Command
The scaffold command creates the C# classes (derived DbContext and entities) complete with data annotations (if requested) and Fluent API commands from an existing database. There are two
required arguments, the database connection string, and the fully qualified provider (e.g., Microsoft. EntityFrameworkCore.SqlServer). Table 21-18 describes the arguments.

Table 21-18. The DbContext Scaffold Arguments

Argument Meaning in Life
Connection The connection string to the database
Provider The EF Core database provider to use (e.g., Microsoft. EntityFrameworkCore.SqlServer)

The options available include selecting specific schemas and tables, the created context class name and namespace, the output directory and namespace of the generated entity classes, and many more. The
standard options are also available. The extended options are listed in Table 21-19, with discussion to follow.

Table 21-19. The DbContext Scaffold Options

Option (Shorthand ||
Longhand) Meaning in Life
-d || –data-annotations Use attributes to configure the model (where possible). If omitted, only the Fluent API is used.
-c || –context The name of the derived DbContext to create.
–context-dir The directory to place the derived DbContext, relative to the project directory. Defaults to database name.
-f || –force Replaces any existing files in the target directory.
-o || –output-dir The directory to put the generated entity classes into. Relative to the project directory.
–schema … The schemas of the tables to generate entity types for.
-t || –table … The tables to generate entity types for.
–use-database-names Use the table and column names directly from the database.
-n | –namespaces
The namespace for the generated entity classes. Matches the directory by default.
–context-namespace
The namespace for the generated derived DbContext class. Matches the directory by default.
–no-onconfiguring Does not generate OnConfiguring method.
–no-pluralize Does not use the pluralizer.

The scaffold command has become much more robust with EF Core 6.0. As you can see, there are plenty of options to choose from. If the data annotations (-d) option is selected, EF Core will use data annotations where it can and fill in the differences with the Fluent API. If that option is not selected, the entire configuration (where different than the conventions) is coded in the Fluent API. You can specify the namespace, schema, and location for the generated entities and derived DbContext files. If you do not want to scaffold the entire database, you can select certain schemas and tables. The –no-onconfiguring option eliminates the OnConfiguring() method from the scaffolded class, and the –no-pluralize option turns
off the pluralizer, which turns singular entities (Car) into plural tables (Cars) when creating migrations and turns plural tables into single entities when scaffolding.
New in EF Core 6, database comments on SQL tables and columns are also scaffolded into the entity classes and their properties.

The DbContext Optimize Command
The optimize command optimizes the derived DbContext, performing many of the steps that would normally happen when the derived DbContext is first used. The options available include specifying the directory to place the compiled results as well as what namespace to use. The standard options are also available. The extended options are listed in Table 21-20, with discussion to follow.

Table 21-20. The DbContext Optimize Options

Option (Shorthand || Longhand) Meaning in Life
-o || –output-dir The directory to put the files in. Paths are relative to the project directory.
-n | –namespace The namespace to use. Matches the directory by default.

When the derived DbContext is compiled, the results include a class for each entity in your model, the compiled derived DbContext, and the compiled derived DbContext ModelBuilder. For example, you can compile AutoLot.Samples.ApplicationDbContext using the following command:

dotnet ef dbcontext optimize –output-dir CompiledModels

The compiled files are placed in a directory named CompiledModels. The files are listed here:

ApplicationDbContextModel.cs ApplicationDbContextModelBuilder.cs CarDriverEntityType.cs CarEntityType.cs CarMakeViewModelEntityType.cs DriverEntityType.cs MakeEntityType.cs PersonEntityType.cs RadioEntityType.cs

To use the compiled model, call the UseModel() method in the DbContextOptions, like this:

var optionsBuilder = new DbContextOptionsBuilder();
var connectionString = @”server=.,5433;Database=AutoLotSamples;User Id=sa;Password=P @ssw0rd;Encrypt=False;”; optionsBuilder.UseSqlServer(connectionString).UseModel(ApplicationDbContextModel.Instance); var context = new ApplicationDbContext(optionsBuilder.Options);

Compiling the derived DbContext can significantly improve performance in certain situations, but there are some restrictions:
• Global query filters are not supported.
• Lazy loading proxies are not supported.
• Change tracking proxies are not supported.
• The model must be recompiled any time the model changes.

If these restrictions aren’t an issue for your situation, making use of the DbContext optimization can significantly improve your applications performance.

Summary
This chapter started the journey into Entity Framework Core. This chapter examined EF Core fundamentals, how queries execute, and change tracking. You learned about shaping your model with conventions, data annotations, and the Fluent API. The final section covered the power of the EF Core command-line interface and global tools.