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:
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
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
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
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
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
(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
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
: 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
■ 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
: 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
For each entity type (T) in your object model, you add a property of type DbSet
class. The DbSet
Table 21-2. Common Members and Extension Methods of DbSet
Member of DbSet
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
The DbSet
You will be adding the DbSet
■ 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
The ChangeTracker
The ChangeTracker instance tracks the state for objects loaded into DbSet
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
Update the constructor for the ApplicationDbContext class to the following to specify the event handlers for the StateChanged and Tracked events:
public ApplicationDbContext(DbContextOptions
: 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
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
using Microsoft.EntityFrameworkCore; using AutoLot.TPH.Models;
namespace AutoLot.TPH;
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions
: base(options) { }
public DbSet
}
Note the DbSet
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
}
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
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
}
//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
public DbSet
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
public DbSet
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
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
}
Add the DbSet
public DbSet
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
}
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
}
public class Car
{
...
public IEnumerable
}
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
Relationships Relationships between tables are created when there are navigation properties between two entity classes.
Foreign key Properties named
The previous navigation property examples all leverage EF Core conventions to build the relationships between the tables.
Mapping Properties to Columns 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 Overriding EF Core Conventions protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder) 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 Table 21-8. Some Data Annotations Supported by the Entity Framework Core (*New Attributes in EF Core 6) Data Annotation Meaning in Life Table 21-8. (continued) Data Annotation Meaning in Life 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. public abstract class BaseEntity 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 { 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) CREATE TABLE [dbo].[Inventory]( 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 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. namespace AutoLot.Samples.Models; [Table("Radios", Schema="dbo")] 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]( [HasTweeters] [BIT] NOT NULL, [HasSubWoofers] [BIT] NOT NULL, [RadioId] [NVARCHAR](50) NOT NULL, 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 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 [Table("Makes", Schema="dbo")] [Table("Drivers", Schema="dbo")] [Required, StringLength(50)] Annotations and Navigation Properties The Fluent API Class and Property Methods Class and Property Mapping modelBuilder.Entity The following maps the CarId property of the Radio class to the InventoryId column of the Makes table: modelBuilder.Entity Keys and Indices modelBuilder.Entity 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 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 To make the index unique, use the IsUnique() method. The IsUnique() method takes an optional bool entity.HasIndex(e => e.MakeId, "IX_Inventory_MakeId").IsUnique(); Field Size and Nullability modelBuilder.Entity Default Values modelBuilder.Entity To set the value to a database function (like getdate()), use the HasDefaultValueSql() method. public class Car : BaseEntity 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 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. //Car.cs 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; ',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 Executing the same code to insert the previous Car record generates different SQL: exec sp_executesql N'SET NOCOUNT ON; ',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 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. public class Car The Fluent API is used to inform EF Core of the backing field. modelBuilder.Entity ■ 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]( 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. options.ConfigureWarnings(wc => wc.Ignore(RelationalEventId.BoolWithDefaultWarning)); RowVersion/Concurrency Tokens modelBuilder.Entity Concurrency checking will be covered in the next chapter. SQL Server Sparse Columns modelBuilder.Entity Computed Columns public class Car : BaseEntity Then add the Fluent API call to HasComputedColumnSql(): modelBuilder.Entity 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 }); 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 Check Constraints 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 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 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. var context = new ApplicationDbContextFactory().CreateDbContext(null); context.Makes.Add(new Make { Name = "Lemon" }); 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 modelBuilder.Entity If you select the principal entity as the base for the navigation property configuration, the code looks like this: modelBuilder.Entity .WithOne(c=>c.MakeNavigation) One-to-One Relationships modelBuilder.Entity entity.HasOne(d => d.CarNavigation) 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 modelBuilder.Entity Many-to-Many Relationships Start by adding a CarDriver entity: //CarDriver.cs [Table("InventoryToDrivers", Schema = "dbo")] public class CarDriver : BaseEntity [Column("InventoryId")] public int CarId {get;set;} [ForeignKey(nameof(CarId))] Add a DbSet public class Car : BaseEntity Now, update the Driver entity for the navigation property to the CarDriver entity: public class Driver : BaseEntity Finally, add in the Fluent API code for the many-to-many relationship: modelBuilder.Entity j => j Excluding Entities from Migrations protected override void OnModelCreating(ModelBuilder modelBuilder) Using IEntityTypeConfiguration Classes namespace AutoLot.Samples.Models.Configuration; public class CarConfiguration : IEntityTypeConfiguration 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 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 j => 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) 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 namespace AutoLot.Samples.Models.Configuration; public class RadioConfiguration : IEntityTypeConfiguration Update the OnModelCreating() method in the ApplicationDbContext: protected override void OnModelCreating(ModelBuilder modelBuilder) 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 Conventions, Annotations, and the Fluent API, Oh My! Owned Entity Types namespace AutoLot.Samples.Models; [Owned] public string FirstName { get; set; } [Required, StringLength(50)] 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 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 Update the ApplicationDbContext OnConfiguring() method: protected override void OnModelCreating(ModelBuilder modelBuilder) 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]( 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. public class DriverConfiguration : IEntityTypeConfiguration This updates the properties of the Person owned type to be set as a not null column in SQL Server: CREATE TABLE [dbo].[Drivers]( CONSTRAINT [PK_Drivers] PRIMARY KEY CLUSTERED There are four limitations when using owned types: 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 SELECT m.Id MakeId, m.Name Make, To hold the results of this query, create a new folder named ViewModels, and in that folder create a new class named CarMakeViewModel: public string Color { get; set; } public string PetName { get; set; } [NotMapped] 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 public class ApplicationDbContext : DbContext ... public DbSet 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; 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 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) 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 Query types can also be mapped to a database view. Presuming there was a view named dbo. 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( Flexible Query/Table Mapping modelBuilder.Entity Query Execution //The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); 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 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 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 = 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 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. The EF Core Global Tool CLI Commands 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 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] Commands: 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 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. Table 21-10. EF Core Command Options Option (Shorthand || Longhand) Meaning in Life To list all the arguments and options for a command, enter dotnet ef 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 Table 21-11. EF Core Migrations Commands Command Meaning in Life The Add Command 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 ... } table.PrimaryKey("PK_Make", x => x.Id); migrationBuilder.CreateIndex( name: "IX_Cars_MakeId", table: "Cars", protected override void Down(MigrationBuilder migrationBuilder) 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. [DbContext(typeof(ApplicationDbContext))] [Migration("20210613000105_Radio")] partial class Radio The first migration creates an additional file in the target directory named for the derived DbContext in the format of ■ Note It is extremely important that you don’t delete migration files manually. This will result in the The Remove Command The List Command Table 21-12. Additional Options for EF Core Migrations List Command Option (Shorthand || The Bundle Command Table 21-13. Common Arguments for the EF Core Migrations Bundle Command Argument Meaning in Life 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. The Script Command Table 21-14. Arguments for the EF Core Migrations Script Command Argument Meaning in Life 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 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 || The Database Commands The Drop Command Table 21-16. EF Core Database Drop Options Option (Shorthand || Longhand) Meaning in Life The Database Update Command The DbContext Commands Table 21-17. The DbContext Commands Command Meaning in Life 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 Table 21-18. The DbContext Scaffold Arguments Argument Meaning in Life 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 Table 21-19. The DbContext Scaffold Options Option (Shorthand || 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 The DbContext Optimize Command Table 21-20. The DbContext Optimize Options Option (Shorthand || Longhand) Meaning in Life 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 Compiling the derived DbContext can significantly improve performance in certain situations, but there are some restrictions: If these restrictions aren’t an issue for your situation, making use of the DbContext optimization can significantly improve your applications performance. Summary
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_
{
get => _color;
set => _color = value;
}
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:
{
configurationBuilder.Properties
}
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 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)
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 TimeStamp property will be a SQL Server timestamp/rowversion property (for concurrency checking, covered later in this chapter).
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
[Timestamp]
public byte[] TimeStamp { get; set; }
}
{
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
}
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:
[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
GO
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):
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; }
}
[Id] [INT] IDENTITY(1,1) 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
GO
namespace AutoLot.Samples.Models;
public class Make : BaseEntity
{
[Required, StringLength(50)]
public string Name { get; set; }
[InverseProperty(nameof(Car.MakeNavigation))]
public IEnumerable
}
//Driver.cs
namespace AutoLot.Samples.Models;
public class Driver : BaseEntity
{
[Required, StringLength(50)]
public string FirstName { get; set; }
public string LastName { get; set; }
[InverseProperty(nameof(Car.Drivers))]
public IEnumerable
}
The ForeignKey annotation lets EF Core know which property is the backing field for the navigation property. By convention,
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 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.
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.
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).
{
entity.ToTable("Inventory","dbo");
});
{
entity.Property(e => e.CarId).HasColumnName("InventoryId");
});
To set the primary key for an entity, use the HasKey() method, as shown here:
{
entity.ToTable("Inventory","dbo");
entity.HasKey(e=>e.Id);
});
{
entity.ToTable("Inventory","dbo");
entity.HasKey(e=> new { e.Id, e.OrganizationId});
});
{
entity.ToTable("Inventory","dbo"); entity.HasKey(e=>e.Id);
entity.HasIndex(e => e.MakeId, "IX_Inventory_MakeId");
});
that defaults to true:
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:
{
...
entity.Property(e => e.Color)
.IsRequired()
.HasMaxLength(50); entity.Property(e => e.PetName)
.IsRequired()
.HasMaxLength(50);
});
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:
{
...
entity.Property(e => e.Color)
.IsRequired()
.HasMaxLength(50)
.HasDefaultValue("Black");
});
Presume that a DateTime property named DateBuilt has been added to the Car class:
{
...
public DateTime? DateBuilt { get; set; }
}
{
...
entity.Property(e => e.DateBuilt)
.HasDefaultValueSql("getdate()");
});
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.
public class Car : BaseEntity
{
...
public bool IsDrivable { get; set; }
}
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();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity
{
...
entity.Property(e => e.IsDrivable).HasDefaultValue(true);
});
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();
nullable 'bool?' type instead, so that the default will only be used for inserts when the property value is 'null'.
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.
{
...
private bool? _isDrivable; public bool IsDrivable
{
get => _isDrivable ?? true; set => _isDrivable = value;
}
}
{
entity.Property(p => p.IsDrivable)
.HasField("_isDrivable")
.HasDefaultValue(true);
});
...
[IsDrivable] [BIT] NOT NULL,
... GO
ALTER TABLE [dbo].[Inventory] ADD DEFAULT (CONVERT([BIT],(1))) FOR [IsDrivable]
GO
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:
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:
{
...
entity.Property(e => e.TimeStamp)
.IsRowVersion()
.IsConcurrencyToken();
});
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:
{
entity.Property(p => p.IsRaceCare).IsSparse();
});
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 string Display { get; set; }
}
{
entity.Property(p => p.Display)
.HasComputedColumnSql("[PetName] + ' (' + [Color] + ')'");
});
{
entity.Property(p => p.Display)
.HasComputedColumnSql("[PetName] + ' (' + [Color] + ')'", stored:true);
{
...
[DatabaseGenerated(DatabaseGeneratedOption.Computed)] public string Display { get; set; }
}
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.
.HasCheckConstraint(name:"CH_Name", sql:"[Name]<>'Lemon'", buildAction:c => c.HasName(“CK_Check_Name”));
defined in SQL:
Execute the following code to see the exception in action:
context.SaveChanges();
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.
{
...
entity.HasOne(d => d.MakeNavigation)
.WithMany(p => p.Cars)
.HasForeignKey(d => d.MakeId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_Inventory_Makes_MakeId");
});
{
...
entity.HasMany(e=>e.Cars)
.HasForeignKey(c=>c.MakeId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_Inventory_Makes_MakeId");
});
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):
{
entity.HasIndex(e => e.CarId, "IX_Radios_CarId")
.IsUnique();
.WithOne(p => p.RadioNavigation)
.HasForeignKey
});
{
entity.HasIndex(e => e.CarId, "IX_Radios_CarId")
.IsUnique();
});
{
entity.HasOne(d => d.RadioNavigation)
.WithOne(p => p.CarNavigation)
.HasForeignKey
});
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.
namespace AutoLot.Samples.Models;
{
public int DriverId {get;set;} [ForeignKey(nameof(DriverId))]
public Driver DriverNavigation {get;set;}
public Car CarNavigation {get;set;}
}
Next, update the Car entity to add a navigation property for the new CarDriver entity:
{
...
[InverseProperty(nameof(CarDriver.CarNavigation))]
public IEnumerable
}
{
[InverseProperty(nameof(CarDriver.DriverNavigation))]
public IEnumerable
}
.HasMany(p => p.Drivers)
.WithMany(p => p.Cars)
.UsingEntity
.HasOne(cd => cd.DriverNavigation)
.WithMany(d => d.CarDrivers)
.HasForeignKey(nameof(CarDriver.DriverId))
.HasConstraintName("FK_InventoryDriver_Drivers_DriverId")
.OnDelete(DeleteBehavior.Cascade),
.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 });
});
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:
{
modelBuilder.Entity
}
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
{
public void Configure(EntityTypeBuilder
{
}
}
{
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");
}
{
...
builder
.HasMany(p => p.Drivers)
.WithMany(p => p.Cars)
.UsingEntity
.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.HasKey(cd => new { cd.CarId, cd.DriverId });
});
}
{
new CarConfiguration().Configure(modelBuilder.Entity
...
}
{
...
}
{
public void Configure(EntityTypeBuilder
{
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
}
}
{
new CarConfiguration().Configure(modelBuilder.Entity
new RadioConfiguration().Configure(modelBuilder.Entity
}
{
...
}
ApplicationDbContext a lot cleaner.
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.
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):
public class Person
{
[Required, StringLength(50)]
public string LastName { get; set; }
}
{
public Person PersonInfo {get;set;} = new Person();
public IEnumerable
public IEnumerable
}
{
public void Configure(EntityTypeBuilder
{
builder.OwnsOne(o => o.PersonInfo, pd =>
{
pd.Property
.HasColumnName(nameof(Person.FirstName))
.HasColumnType("nvarchar(50)"); pd.Property
.HasColumnName(nameof(Person.LastName))
.HasColumnType("nvarchar(50)");
});
}
}
{
new CarConfiguration().Configure(modelBuilder.Entity
new RadioConfiguration().Configure(modelBuilder.Entity
new DriverConfiguration().Configure(modelBuilder.Entity
}
{
...
}
[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
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 void Configure(EntityTypeBuilder
{
...
builder.Navigation(d=>d.PersonInfo).IsRequired(true);
}
}
[Id] [INT] IDENTITY(1,1) NOT NULL, [FirstName] [NVARCHAR](50) NOT NULL, [LastName] [NVARCHAR](50) NOT NULL, [TimeStamp] [TIMESTAMP] NULL,
(
[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
•You cannot create a DbSet
•You cannot call Entity
•Instances of an owned entity type cannot be shared between multiple owners.
•Owned entity types cannot have inheritance hierarchies.
Query types are DbSet
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:
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
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 FullDetail => $" The {Color} {Make} is named {PetName}"; public override string ToString() => FullDetail;
}
{
...
}
public DbSet< CarMakeViewModel> CarMakeViewModels { get; set; }
global using AutoLot.Samples.ViewModels.Configuration;
{
public void Configure(EntityTypeBuilder
{
}
}
{
...
}
{
new CarConfiguration().Configure(modelBuilder.Entity
new RadioConfiguration().Configure(modelBuilder.Entity
new CarMakeViewModelConfiguration().Configure(modelBuilder.Entity
}
{
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");
}
CarMakeView, the configuration would look like this:
@" 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 ");
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.
.ToTable("Inventory")
.ToView("InventoryWithMakesView");
Data retrieval queries are created with LINQ queries written against the DbSet
For example, to get all yellow Car records from the database, execute the following query:
var cars = context.Cars.Where(x=>x.Color == "Yellow");
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
When data is read from the database into a DbSet
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
context.Cars.Where(x=>x.Id == 1).AsNoTrackingWithIdentityResolution();
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.
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 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 uninstall --global dotnet-ef.
---==/ \\
|. \|\
| || | | ) \\\
| _| | _| \_/ | //|\\
| ||_| / \\\/\\
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.
database Commands to manage the database. dbcontext Commands to manage DbContext types. migrations Commands to manage migrations.
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.
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
There are common options available for the EF Core commands, shown in Table 21-10. Many of the commands have additional options or arguments.
--c || --context
-p || --project
-s || --startup-project
-h || --help Displays the help and all of the options.
-v || --verbose Shows verbose output.
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.
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 creates a new database migration based on the current object model. The process examines every entity with a DbSet
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
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
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.
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable( name: "Make",
columns: table => new
{
Id = table.Column
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column
},
constraints: table =>
{
});
column: "MakeId");
{
migrationBuilder.DropTable(name: "Cars"); migrationBuilder.DropTable(name: "Make");
}
}
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:
{
protected override void BuildTargetModel(ModelBuilder modelBuilder)
{
//omitted for brevity
}
}
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 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.
Longhand) Meaning in Life
--connection
IDesignTimeDbContextFactory or the DbContext’s OnConfiguring method.
--no-connect Instructs the command to skip the database check.
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.
-o | --output
-f | --force Overwrite existing files.
--self-contained Also bundle the .NET runtime with the executable.
-r | --target-runtime
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 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.
//script from the beginning to the Many2Many migrations dotnet ef migrations script 0 Many2Many
Longhand) Meaning in Life
-o || -output
-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
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 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.
-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 update command takes one argument (the migration name) and the usual options. The command has one additional option, --connection
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).
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.
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 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.
Connection The connection string to the database
Provider The EF Core database provider to use (e.g., Microsoft. EntityFrameworkCore.SqlServer)
standard options are also available. The extended options are listed in Table 21-19, with discussion to follow.
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
--context-dir
-f || --force Replaces any existing files in the target directory.
-o || --output-dir
--schema
-t || --table
--use-database-names Use the table and column names directly from the database.
-n | --namespaces
--context-namespace
--no-onconfiguring Does not generate OnConfiguring method.
--no-pluralize Does not use the pluralizer.
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 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.
-o || --output-dir The directory to put the files in. Paths are relative to the project directory.
-n | --namespace
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);
• 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.
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.