Pro C#10 CHAPTER 23 Build a Data Access Layer with Entity Framework Core

CHAPTER 23

Build a Data Access Layer with Entity Framework Core

The previous chapters covered the components and capabilities of EF Core. This chapter is focused on applying what you learned about EF Core to build the AutoLot data access layer. This chapter begins with creating one project for the entities and another for the data access library code. Separation of models from the data access code is a common design decision and will be taken advantage of in the ASP.NET Core chapters.
The next step is to scaffold the existing database from Chapter 20 into entities and a derived DbContext using the EF Core command-line interface (CLI). This demonstrates the database first process. Then the project is changed to code first, where the database design is driven by the C# entities.
The entities from Chapter 20 are updated to their final version, the new entities from Chapters 21 and 22 are added into the model, and the database is updated using EF Core migrations. Then the stored procedure, database view, and user-defined functions are integrated into the EF Core migration system, providing a singular mechanism for developers to get a complete copy of the database. The final EF Core migration completes the database.
The next step is to create repositories that provide encapsulated create, read, update, and delete (CRUD) access to the database. The final step in this chapter is to add data initialization code to provide sample data, a common practice used for testing the data access layer.

Create the AutoLot.Dal and AutoLot.Models Projects
The AutoLot data access layer consists of two projects, one to hold the EF Core–specific code (the derived DbContext, context factory, repositories, migrations, etc.) and another one to hold the entities and view models. Create a new solution named Chapter23_AllProjects, add a .NET Core class library named AutoLot. Models into the solution, and add the Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore. SqlServer, and System.Text.Json NuGet packages to the project.
The Microsoft.EntityFrameworkCore.Abstractions package provides access to many EF Core constructs (like data annotations), is lighter weight than the Microsoft.EntityFrameworkCore package, and would normally be used for model projects. However, support for the new IEntityTypeConfiguration feature is not in the Abstractions package but the full EF Core package.
Add another .NET Core class library project named AutoLot.Dal to the solution. Add a reference to the AutoLot.Models project, and add the Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore. SqlServer, and Microsoft.EntityFrameworkCore.Design NuGet packages to the project.
As a refresher, 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.

© 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_23

991

To complete all these steps using the command line, use the following (in the directory where you want the solution to be created):

dotnet new sln -n Chapter23_AllProjects

dotnet new classlib -lang c# -n AutoLot.Models -o .\AutoLot.Models -f net6.0 dotnet sln .\Chapter23_AllProjects.sln add .\AutoLot.Models
dotnet add AutoLot.Models package Microsoft.EntityFrameworkCore
dotnet add AutoLot.Models package Microsoft.EntityFrameworkCore.SqlServer dotnet add AutoLot.Models package System.Text.Json

dotnet new classlib -lang c# -n AutoLot.Dal -o .\AutoLot.Dal -f net6.0 dotnet sln .\Chapter23_AllProjects.sln add .\AutoLot.Dal
dotnet add AutoLot.Dal package Microsoft.EntityFrameworkCore
dotnet add AutoLot.Dal package Microsoft.EntityFrameworkCore.Design dotnet add AutoLot.Dal package Microsoft.EntityFrameworkCore.SqlServer

dotnet add AutoLot.Dal reference AutoLot.Models

■Note If you are not using a Windows-based machine, adjust the directory separator character for your operating system in the previous commands. This adjustment will need to be done for all the CLI commands in this chapter.

The final step in creating the projects is to turn C#’s nullable reference types off. While EF Core supports this feature, we won’t be using it in this solution. Update the project files for both projects to the following (change is in bold):

net6.0
enable
disable

Finally, update AutoLot.Dal project file to enable access to the design-time model at runtime. Update the metadata for the Microsoft.EntityFrameworkCore.Design package to the following. This change is necessary for clearing out temporal tables, covered in the “Data Initialization” section:

all

Add the Database View
Before scaffolding the entities and derived DbContext from database, add a custom database view to the AutoLot database, which will be used later in this chapter. We are adding it now to demonstrate scaffolding support for views. Connect to the AutoLot database (using either SQL Server Management Studio or Azure Data Studio) and execute the following SQL statement:

CREATE VIEW [dbo].[CustomerOrderView] AS
SELECT dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Inventory.Color, dbo.Inventory.PetName, dbo.Makes.Name AS Make
FROM dbo.Orders
INNER JOIN dbo.Customers ON dbo.Orders.CustomerId=dbo.Customers.Id INNER JOIN dbo.Inventory ON dbo.Orders.CarId=dbo.Inventory.Id INNER JOIN dbo.Makes ON dbo.Makes.Id=dbo.Inventory.MakeId;

■Note In the repo’s folder for Chapter 20 are database backups for Windows and docker. If you need to restore the database, refer to the instructions in Chapter 20.

Scaffold the DbContext and Entities
The next step is to scaffold the AutoLot database using the EF Core CLI tools. Navigate to the AutoLot.Dal project directory in either a command prompt or Visual Studio’s Package Manager Console. Use the EF Core CLI tools to scaffold the AutoLot database into the entities and the DbContext-derived class with the following command, updating the connection string as necessary (all on one line):

dotnet ef dbcontext scaffold "server=.,5433;Database=AutoLot;User Id=sa;Password=P@ssw0rd;" Microsoft.EntityFrameworkCore.SqlServer –data-annotations –context ApplicationDb
Context –context-namespace AutoLot.Dal.EfStructures –context-dir EfStructures –no- onconfiguring –namespace AutoLot.Models.Entities –output-dir ..\AutoLot.Models\ Entities –force

The previous command scaffolds the database located at the provided connection string (the example command uses the connection string for the Docker container used in Chapter 20) using the SQL Server database provider. The –data-annotations flag is to prioritize data annotations where possible (over the Fluent API). The –context names the context, –context-namespaces specifies the namespace for
the context, –context-dir indicates the directory (relative to the current project) for the scaffolded context, –no-onconfiguring prevents the OnConfiguring method from being scaffolded, –output-dir is the output directory for the entities (relative to the project directory), and -n specifies the namespace for the entities. This command places all the entities in the AutoLot.Models project in the Entities folder and places the ApplicationDbContext.cs class in the EfStructures folder of the AutoLot.Dal project. The final option (–force) is used to force overwriting any existing files.

■Note The eF Core CLI commands were covered in detail in Chapter 21.

Examine the Results
After running the command to scaffold the database into C# classes, you will see six entities in the AutoLot. Models project (in the Entities folder) and one derived DbContext in the AutoLot.Dal project (in the EfStructures folder). Each table is scaffolded into a C# entity class and added as a DbSet property on the derived DbContext. Views are scaffolded into keyless entities, added as a DbSet, and mapped to the proper database view using the Fluent API.

The scaffolding command that we used specified the –data-annotations flag to prefer annotations over the Fluent API. As you examine the scaffolded classes, you will notice there are a few misses with the annotations. For example, the TimeStamp properties do not have the [Timestamp] attribute but are instead configured as RowVersion ConcurrencyTokens in the Fluent API.

■Note In my opinion, having the annotations in the class makes the code more readable than having all the configuration in the Fluent apI. If you prefer to use the Fluent apI, remove the –data-annotations option from the command.

Switch to Code First
Now that you have the database scaffolded into a derived DbContext and entities, it is time to switch from database first to code first. The process is not complicated, but also not something that should be done on a regular basis. It’s better to decide on a paradigm and stick with it. Most agile teams prefer code first, as the
emerging design of the application and its entities flows into the database. The process we are following here simulates starting a new project using EF Core targeting an existing database.
The steps involved in switching from database first to code first involves creating a DbContext factory (for the CLI tooling), creating an initial migration for the current state of the object graph, and then dropping the database and re-creating the database with either the migration or “fake” applied by tricking EF Core.

Create the DbContext Design-Time Factory
As you recall from the previous EF Core chapters, IDesignTimeDbContextFactory is used by the EF Core CLI tooling to create an instance of the derived DbContext class. Create a new class file named ApplicationDbContextFactory.cs in the AutoLot.Dal project in the EfStructures directory.
The details of the factory were covered in the previous chapter, so I’m just going to list the code here.
Make sure to update your connection string to match your environment.

namespace AutoLot.Dal.EfStructures;

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

Create the Initial Migration
Recall that the first migration will create three files: the two files for the migration itself and the complete model snapshot. Enter the following in a command prompt (in the AutoLot.Dal directory) to create a new

migration named Initial (using the ApplicationDbContext instance that was just scaffolded) and place the migration files in the EfStructures\Migrations folder of the AutoLot.Dal project:

dotnet ef migrations add Initial -o EfStructures\Migrations -c AutoLot.Dal.EfStructures. ApplicationDbContext

■Note It is important to make sure no changes are applied to the scaffolded files or the database until this first migration is created and applied. Changes on either side will cause the code and database to become out of sync. once applied, all changes to the database need to be completed through eF Core migrations.

To confirm that the migration was created and is waiting to be applied, execute the list command.

dotnet ef migrations list -c AutoLot.Dal.EfStructures.ApplicationDbContext

The result will show the Initial migration pending (your timestamp will be different). The connection string is shown in the output due to Console.Writeline() in the CreateDbContext() method.

Build started…
Build succeeded.
server=.,5433;Database=AutoLot;User Id=sa;Password=P@ssw0rd; 20210703194100_Initial (Pending)

Applying the Migration
The easiest method of applying the migration to the database is to drop the database and re-create it. If that is an option, you can enter the following commands and move on to the next section:

dotnet ef database drop -f
dotnet ef database update Initial -c AutoLot.Dal.EfStructures.ApplicationDbContext

If dropping and re-creating the database is not an option (e.g., it is an Azure SQL database), then EF Core needs to believe that the migration has been applied. Fortunately, this is straightforward with EF Core doing most of the work. Start by creating a SQL script from the migration by using the following command:

dotnet ef migrations script –idempotent -o FirstMigration.sql

The relevant portions of this script are the parts that create the EFMigrationsHistory table and then add the migration record into the table to indicate that it was applied. Copy those pieces to a new query in either Azure Data Studio or SQL Server Manager Studio. Here is the SQL code that you need (your timestamp will be different):

IF OBJECT_ID(N'[ EFMigrationsHistory]’) IS NULL BEGIN
CREATE TABLE [ EFMigrationsHistory] ( [MigrationId] nvarchar(150) NOT NULL, [ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);

END;
GO
…
IF NOT EXISTS(SELECT * FROM [ EFMigrationsHistory] WHERE [MigrationId] = N’20210703194100_ Initial’)
BEGIN
INSERT INTO [ EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N’20210703194100_Initial’, N’6.0.0′);
END;
GO

Now if you run the list command, it will no longer show the Initial migration as pending. With the initial migration applied, the project and database are in sync, and the development can continue code first. Before continuing with the database development, the project’s custom exceptions need to be created.

Create the GlobalUsings Files
To tidy up the code in the projects, we are going to take advantage of the new C# 10 feature for global using statements. Rename the Class1.cs files in the AutoLot.Dal and AutoLot.Models projects to GlobalUsings. cs. Clear out all the code in each of the files and replace them as follows:

//AutoLot.Dal
//GlobalUsings.cs
global using AutoLot.Dal.EfStructures; global using AutoLot.Dal.Exceptions;

global using AutoLot.Models.Entities;
global using AutoLot.Models.Entities.Configuration; global using AutoLot.Models.Entities.Base;

global using System.Data;
global using System.Linq.Expressions;

global using Microsoft.Data.SqlClient; global using Microsoft.EntityFrameworkCore;
global using Microsoft.EntityFrameworkCore.ChangeTracking; global using Microsoft.EntityFrameworkCore.Design;
global using Microsoft.EntityFrameworkCore.Metadata; global using Microsoft.EntityFrameworkCore.Migrations; global using Microsoft.EntityFrameworkCore.Query; global using Microsoft.EntityFrameworkCore.Storage; global using Microsoft.Extensions.DependencyInjection;

//AutoLot.Models
//GlobalUsings.cs
global using AutoLot.Models.Entities.Base; global using AutoLot.Models.Entities.Owned;
global using AutoLot.Models.Entities.Configuration;

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

global using System.ComponentModel;
global using System.ComponentModel.DataAnnotations;
global using System.ComponentModel.DataAnnotations.Schema; global using System.Globalization;
global using System.Xml.Linq;

■Note adding all the namespaces at once will prevent the code from compiling, as all of the namespaces listed here do not yet exist. normally, you would add to this file as you develop your project. We are adding them most of them here in one shot to save space in an already long chapter.

Create Custom Exceptions
A common pattern in exception handling is to catch system exceptions (and/or EF Core exceptions, as in this example), log the exception, and then throw a custom exception. If a custom exception is caught in an upstream method, the developer knows the exception has already been logged and just needs to react to the exception appropriately in the current code block.
Create a new directory named Exceptions in the AutoLot.Dal project. In that directory, create four new class files: CustomException.cs, CustomConcurrencyException.cs, CustomDbUpdateException.cs, and CustomRetryLimitExceededException.cs. All four files are shown in the following listing:

//CustomException.cs
namespace AutoLot.Dal.Exceptions;

public class CustomException : Exception
{
public CustomException() {}
public CustomException(string message) : base(message) { } public CustomException(string message, Exception innerException)
: base(message, innerException) { }
}

//CustomConcurrencyException.cs namespace AutoLot.Dal.Exceptions;

public class CustomConcurrencyException : CustomException
{
public CustomConcurrencyException() { }
public CustomConcurrencyException(string message) : base(message) { } public CustomConcurrencyException(
string message, DbUpdateConcurrencyException innerException)
: base(message, innerException) { }
}

//CustomDbUpdateException.cs namespace AutoLot.Dal.Exceptions;

public class CustomDbUpdateException : CustomException

{
public CustomDbUpdateException() { }
public CustomDbUpdateException(string message) : base(message) { } public CustomDbUpdateException(
string message, DbUpdateException innerException)
: base(message, innerException) { }
}

//CustomRetryLimitExceededException.cs namespace AutoLot.Dal.Exceptions;

public class CustomRetryLimitExceededException : CustomException
{
public CustomRetryLimitExceededException() { }
public CustomRetryLimitExceededException(string message)
: base(message) { }
public CustomRetryLimitExceededException(
string message, RetryLimitExceededException innerException)
: base(message, innerException) { }
}

■Note Custom exception handling was covered in detail in Chapter 7.

Finalize the Entities and ViewModel
This section updates scaffolded entities to their final version, adds the additional entities from the previous two chapters, and adds a logging entity.

■Note your projects will not compile until this section is complete.

The Entities
In the Entities directory of the AutoLot.Models project, you will find six files, one for each table in the database and one for the database view. Note that the names are singular and not plural (as they are in the database). This is a change starting with EF Core 5 where the pluralizer is on by default when scaffolding entities from the database. The pluralizer, as the name describes, maps singular entity names to plural table names, and vice versa.
The previous chapters covered the EF Core conventions, data annotations, and the Fluent API in depth, so most of this section will be code listings with brief descriptions.

The BaseEntity Class
The BaseEntity class will hold the Id and TimeStamp columns that are on every entity. Create a new directory named Base in the Entities directory of the AutoLot.Models project. In this directory, create a new file named BaseEntity.cs and update the code to match the following:

namespace AutoLot.Models.Entities.Base;

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

All of the entities (except for the logging entity) will be updated to use this base class throughout the next sections.

The Owned Person Entity
The Customer, CreditRisk, and Driver entities all have FirstName and LastName properties. Entities that have the same properties in each can benefit from owned classes. While moving these two properties to an owned class is a somewhat trivial example, owned entities help to reduce code duplication and increase consistency.
Create a new directory named Owned in the Entities directory of the AutoLot.Models project. In this new directory, create a new file named Person.cs. Update the code to match the following:
namespace AutoLot.Models.Entities.Owned; [Owned]
public class Person
{
[Required, StringLength(50)]
public string FirstName { get; set; }

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

[DatabaseGenerated(DatabaseGeneratedOption.Computed)] public string FullName { get; set; }
}

Notice the additional computed column that combined the names into a FullName property. Owned classes are configured as part of the owning classes, so the configuration for the column name mapping and computed column takes place as part of the Customer, CreditRisk, and Driver configuration.

The Car (Inventory) Entity
The Inventory table was scaffolded to an entity class named Inventory. We are going to change the entity name to Car while leaving the table name alone. This is an example of mapping an entity to a table with a different name. This is easy to fix: change the name of the file to Car.cs and the name of the class to Car. The Table attribute is already applied correctly, so just add the dbo schema. Note that the schema parameter is optional because SQL Server defaults to dbo, but I include it for completeness. The namespaces can also be deleted, as they are covered with the global namespaces.

[Table("Inventory", Schema = "dbo")] [Index(nameof(MakeId), Name = "IX_Inventory_MakeId")] public partial class Car
{
…
}

Next, inherit from BaseEntity, and remove the Id (and its attribute) and TimeStamp properties as well as the constructor. This is the code for the class after these changes:

namespace AutoLot.Models.Entities;

[Table("Inventory", Schema = "dbo")] [Index(nameof(MakeId), Name = "IX_Inventory_MakeId")] public partial class Car : BaseEntity
{
public int MakeId { get; set; } [Required]
[StringLength(50)]
public string Color { get; set; } [Required]
[StringLength(50)]
public string PetName { get; set; } [ForeignKey(nameof(MakeId))] [InverseProperty("Inventories")] public virtual Make Make { get; set; } [InverseProperty(nameof(Order.Car))]
public virtual ICollection Orders { get; set; }
}

Add the DisplayName attribute to the PetName property, add the Display property with the DatabaseGenerated attribute to hold the computed value from SQL Server, and add the Price and DateBuilt properties. Update the code to the following (changes in bold):

[Required] [StringLength(50)] [DisplayName("Pet Name")]
public string PetName { get; set; }

[DatabaseGenerated(DatabaseGeneratedOption.Computed)] public string Display { get; set; }

public string Price { get; set; } public DateTime? DateBuilt { get; set;}

■Note The DisplayName attribute is used by asp.neT Core and will be covered in that section.

The Make navigation property needs to be renamed to MakeNavigation, and the inverse property is using a magic string instead of the C# nameof() method. Here is the updated property:

[ForeignKey(nameof(MakeId))] [InverseProperty(nameof(Make.Cars))]
public virtual Make MakeNavigation { get; set; }

■Note This is a prime example of why naming the property the same as the class name becomes problematic. If the property name was left as Make, then the nameof function wouldn’t work properly since Make (in this instance) is referring to the property and not the type.

The scaffolded code for the Orders navigation property does use the nameof() method in the inverse property but needs an update since all reference navigation properties will have the suffix Navigation added to their names. The final change for that navigation property is to have the type of the property typed as IEnumerable instead of ICollection and initialized with a new List. This is not
a required change, as ICollection will also work. I prefer to use the lower-level IEnumerable on collection navigation properties (since IQueryable and ICollection both derive from IEnumerable). Update the code to match the following:

[InverseProperty(nameof(Order.CarNavigation))]
public virtual IEnumerable Orders { get; set; } = new List();

Add the collection navigation property for the Driver and CarDriver entities and the reference navigation property for the Radio entity:
[InverseProperty(nameof(Driver.Cars))]
public virtual IEnumerable Drivers { get; set; } = new List();

[InverseProperty(nameof(CarDriver.CarNavigation))]
public virtual IEnumerable CarDrivers { get; set; } = new List();

[InverseProperty(nameof(Radio.CarNavigation))] public virtual Radio RadioNavigation { get; set; }

Next, add a NotMapped property that will display the Make value of the Car. If the related Make information was retrieved from the database with the Car record, the Make Name will be displayed. If the related data was not retrieved, the property displays “Unknown.” As a reminder, NotMapped properties are not part of the database and exist only on the entity. Add the following:

[NotMapped]
public string MakeName => MakeNavigation?.Name ?? "Unknown";

Add an override for the ToString() method to display vehicle information.

public override string ToString()
{
// Since the PetName column could be empty, supply
// the default name of No Name.
return $"{PetName ?? "No Name"} is a {Color} {MakeNavigation?.Name} with ID {Id}.";
}

Add the Required and DisplayName attributes to the MakeId. Even though the MakeId property is considered by EF Core to be required since it is non-nullable, I always add it for readability and UI framework support. Update the code to match the following:

[Required] [DisplayName("Make")]
public int MakeId { get; set; }

The next change is to add the non-nullable bool IsDrivable property with a nullable backing field and a display name.

private bool? _isDrivable;

[DisplayName("Is Drivable")] public bool IsDrivable
{
get => _isDrivable ?? true; set => _isDrivable = value;
}

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
{
…
}

That completes the updates to the Car entity class and is listed here in its entirety:

namespace AutoLot.Models.Entities;

[Table("Inventory", Schema = "dbo")] [Index(nameof(MakeId), Name = "IX_Inventory_MakeId")] [EntityTypeConfiguration(typeof(CarConfiguration))] public partial class Car : BaseEntity
{
[Required] [StringLength(50)]
public string Color { get; set; } public string Price { get; set; }

private bool? _isDrivable; [DisplayName("Is Drivable")] public bool IsDrivable
{
get => _isDrivable ?? true; set => _isDrivable = value;
}

public DateTime? DateBuilt { get; set; }

[DatabaseGenerated(DatabaseGeneratedOption.Computed)] public string Display { get; set; }

[Required] [StringLength(50)] [DisplayName("Pet Name")]
public string PetName { get; set; }

[Required] [DisplayName("Make")]
public int MakeId { get; set; } [ForeignKey(nameof(MakeId))] [InverseProperty(nameof(Make.Cars))]
public virtual Make MakeNavigation { get; set; }

[InverseProperty(nameof(Radio.CarNavigation))] public virtual Radio RadioNavigation { get; set; }

[InverseProperty(nameof(Driver.Cars))]
public virtual IEnumerable Drivers { get; set; } = new List();

[InverseProperty(nameof(CarDriver.CarNavigation))]
public virtual IEnumerable CarDrivers { get; set; } = new List();

[InverseProperty(nameof(Order.CarNavigation))]
public virtual IEnumerable Orders { get; set; } = new List();

[NotMapped]
public string MakeName => MakeNavigation?.Name ?? "Unknown"; public override string ToString()
{
// Since the PetName column could be empty, supply
// the default name of No Name.
return $"{PetName ?? "No Name"} is a {Color} {MakeNavigation?.Name} with ID {Id}.";
}
}

Update the ApplicationDbContext Class
Since the Inventory class was renamed to Car, the ApplicationDbContext class must be updated. Locate the DbSet property and update the line to the following:

public virtual DbSet Cars { get; set; }

The CarConfiguration Class
Just as we did in Chapter 21, we will use IEntityTypeConfiguration to hold the Fluent API code. This keeps the configuration for each entity in its own class, significantly decreasing the size of the ApplicationDbContext OnModelCreating() method. Start by creating a new directory named Configuration under the Entities directory. In this new directory, add a new file named
CarConfiguration.cs, make it public, and implement the IEntityTypeConfiguration interface, like this:

namespace AutoLot.Models.Entities.Configuration;

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

Next, move the contents of the configuration for the Car entity (note that it will still be named Inventory) from the OnModelCreating() method in the ApplicationDbContext into the Configure() method of the CarConfiguration class. The scaffolded Fluent API code for each entity is wrapped in a construct similar to the following:

modelBuilder.Entity(entity =>
{
//Fluent API code here
});

Each IEntityTypeConfiguration is strongly typed to an entity, so the outer code for each entity is not needed, just the scaffolded inner code. Move the entire block, and then delete the entity specifier. Replace the entity variable in each of the inner code blocks with the builder variable, and then add the additional Fluent API code so the Configure() method looks like this:

public void Configure(EntityTypeBuilder builder)
{
builder.HasQueryFilter(c => c.IsDrivable);

builder.Property(p => p.IsDrivable)
.HasField("_isDrivable")
.HasDefaultValue(true);
builder.Property(e => e.DateBuilt).HasDefaultValueSql("getdate()"); builder.Property(e => e.Display)
.HasComputedColumnSql("[PetName] + ‘ (‘ + [Color] + ‘)’", stored: true);

CultureInfo provider = new CultureInfo("en-us");
NumberStyles style = NumberStyles.Number | NumberStyles.AllowCurrencySymbol; builder.Property(p => p.Price)
.HasConversion(
v => decimal.Parse(v, style, provider), v => v.ToString("C2"));

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( j => j
.HasOne(cd => cd.DriverNavigation)
.WithMany(d => d.CarDrivers)
.HasForeignKey(nameof(CarDriver.DriverId))
.HasConstraintName("FK_InventoryDriver_Drivers_DriverId")
.OnDelete(DeleteBehavior.Cascade), j => j
.HasOne(cd => cd.CarNavigation)
.WithMany(c => c.CarDrivers)
.HasForeignKey(nameof(CarDriver.CarId))
.HasConstraintName("FK_InventoryDriver_Inventory_InventoryId")
.OnDelete(DeleteBehavior.ClientCascade), j =>
{
j.HasKey(cd => new { cd.CarId, cd.DriverId });
});
}

The Inventory table will be configured as a temporal table, so add the following to the
Configure() method:

public void Configure(EntityTypeBuilder builder)
{
//omitted for brevity
builder.ToTable( b => b.IsTemporal(t =>
{
t.HasPeriodEnd("ValidTo"); t.HasPeriodStart("ValidFrom"); t.UseHistoryTable("InventoryAudit");
}));
}

Make sure all the code in the OnModelBuilding() method (in the ApplicationDbContext.cs class) that configures the Inventory class is deleted, and add the following single line of code in its place:

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

The Driver Entity
Chapter 21 added a new entity named Driver and set up a many-to-many relationship with the Car entity. Since this table wasn’t in the Chapter 20 database, it is not in our scaffolded code. Add a new file named Driver.cs to the Entities folder and update the code in the file to match the following:
namespace AutoLot.Models.Entities; [Table("Drivers", Schema = "dbo")]
[EntityTypeConfiguration(typeof(DriverConfiguration))]
public class Driver : BaseEntity
{
public Person PersonInformation{ get; set; } = new Person(); [InverseProperty(nameof(Car.Drivers))]
public virtual IEnumerable Cars { get; set; } = new List();

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

Update the ApplicationDbContext Class
Since this is a new table, a new DbSet property must be added into the ApplicationDbContext
class. Add the following to the DbSet properties:

public virtual DbSet Drivers { get; set; }

The DriverConfiguration Class
Add a new file named DriverConfiguration.cs into the Configuration folder, and update the code to the following:

namespace AutoLot.Models.Entities.Configuration;

public class DriverConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.OwnsOne(o => o. PersonInformation, pd =>
{
pd.Property(nameof(Person.FirstName))
.HasColumnName(nameof(Person.FirstName))
.HasColumnType("nvarchar(50)"); pd.Property(nameof(Person.LastName))
.HasColumnName(nameof(Person.LastName))
.HasColumnType("nvarchar(50)"); pd.Property(p => p.FullName)
.HasColumnName(nameof(Person.FullName))
.HasComputedColumnSql("[LastName] + ‘, ‘ + [FirstName]");
});

builder.Navigation(d => d.PersonInformation).IsRequired(true);
}
}

The Driver entity uses the Person-owned property, so it cannot be configured as a temporal table.

Update the ApplicationDbContext OnModelCreating() method:

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

The CarDriver Entity
Continuing with configuration the many-to-many relationship between Car and Driver, add a new class named CarDriver. Update the code to the following:

namespace AutoLot.Models.Entities;

[Table("InventoryToDrivers", Schema = "dbo")] [EntityTypeConfiguration(typeof(CarDriverConfiguration))] public class CarDriver : BaseEntity
{
public int DriverId { get; set; } [ForeignKey(nameof(DriverId))]
public virtual Driver DriverNavigation { get; set; }

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

Update the ApplicationDbContext Class
Since this is a new table, a new DbSet property must be added into the ApplicationDbContext
class. Add the following to the DbSet properties:

public virtual DbSet CarsToDrivers { get; set; }

The CarDriverConfiguration Class
Because of the query filter for nondrivable cars on the Car class, the related tables (CarDriver and Order) need to have the same query filter applied to their navigation properties. Add a new file named CarDriverConfiguration.cs into the Configuration folder, and update the code to the following:

namespace AutoLot.Models.Entities.Configuration;

public class CarDriverConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.HasQueryFilter(cd=>cd.CarNavigation.IsDrivable);
}
}

The InventoryToDrivers table will be configured as a temporal table, so add the following to the
Configure() method:

Update the ApplicationDbContext OnModelCreating() method:

The Radio Entity
Chapter 21 also added a new entity named Radio and set up a one-to-one relationship with the Car entity. Add a new file named Radio.cs to the Entities folder and update the code in the file to match the following:
namespace AutoLot.Models.Entities; [Table("Radios", Schema = "dbo")]
[EntityTypeConfiguration(typeof(RadioConfiguration))] 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 virtual Car CarNavigation { get; set; }
}

Update the ApplicationDbContext Class
Since this is a new table, a new DbSet property must be added into the ApplicationDbContext class. Add the following to the DbSet properties:

public virtual DbSet Radios { get; set; }

The RadioConfiguration Class
Create a new class named RadioConfiguration, implement the IEntityTypeConfiguration
interface, and add the code from the ApplicationDbContext OnModelBuilding() method:

namespace AutoLot.Models.Entities.Configuration;

public class RadioConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.HasQueryFilter(r=>r.CarNavigation.IsDrivable); builder.HasIndex(e => e.CarId, "IX_Radios_CarId")
.IsUnique();

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

The Radios table will be configured as a temporal table, so add the following to the
Configure() method:

Update the OnModelCreating() method in the ApplicationDbContext:

The Customer Entity
The Customers table was scaffolded to an entity class named Customer. Inherit from BaseEntity and remove the Id and TimeStamp properties. Delete the constructor and add the Table attribute with schema. Remove the FirstName and LastName properties as they will be replaced by the Person-owned entity. This is where the class code stands at this time:

namespace AutoLot.Models.Entities; [Table("Customers", Schema = "dbo")] public partial class Customer : BaseEntity
{
[InverseProperty(nameof(CreditRisk.Customer))]
public virtual ICollection CreditRisks { get; set; } [InverseProperty(nameof(Order.Customer))]
public virtual ICollection Orders { get; set; }
}

Like the Car entity, there are still some issues with this code that need to be fixed, and the owned entity must be added. The inverse property attributes need to be updated with the Navigation suffix and the types changed to an IEnumerable and initialized. Update the code to match the following:

[InverseProperty(nameof(CreditRisk.CustomerNavigation))]
public virtual IEnumerable CreditRisks { get; set; } = new List();

[InverseProperty(nameof(Order.CustomerNavigation))]
public virtual IEnumerable Orders { get; set; } = new List();

The next step is to add the owned property. The relationship will be further configured in the Fluent API.

public Person PersonInformation { get; set; } = new Person();

The final step is to add the EntityTypeConfiguration attribute. Here is the complete class, with the final update in bold:
namespace AutoLot.Models.Entities; [Table("Customers", Schema = "dbo")]
[EntityTypeConfiguration(typeof(CustomerConfiguration))]
public partial class Customer : BaseEntity
{
public Person PersonInformation { get; set; } = new Person(); [InverseProperty(nameof(CreditRisk.CustomerNavigation))]
public virtual IEnumerable CreditRisks { get; set; } = new List(); [InverseProperty(nameof(Order.CustomerNavigation))]
public virtual IEnumerable Orders { get; set; } = new List();
}

The CustomerConfiguration Class
Add a new file named CustomerConfiguration.cs into the Configuration folder, and update the code to the following:

namespace AutoLot.Models.Entities.Configuration;

public class CustomerConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.OwnsOne(o => o. PersonInformation, pd =>
{

});

pd.Property(nameof(Person.FirstName))
.HasColumnName(nameof(Person.FirstName))
.HasColumnType("nvarchar(50)"); pd.Property(nameof(Person.LastName))
.HasColumnName(nameof(Person.LastName))
.HasColumnType("nvarchar(50)"); pd.Property(p => p.FullName)
.HasColumnName(nameof(Person.FullName))
.HasComputedColumnSql("[LastName] + ‘, ‘ + [FirstName]");

builder.Navigation(d => d. PersonInformation).IsRequired(true);
}
}

The Customer entity uses the Person-owned property, so it cannot be configured as a temporal table. Delete the Customer configuration code in the ApplicationDbContext OnModelCreating() method,
and add the configuration line for the Customer:

The Make Entity
The Makes table was scaffolded to an entity class named Make. Inherit from BaseEntity and remove the Id and TimeStamp properties. Delete the constructor and add the Table attribute with schema. Here is the current state of the entity:

namespace AutoLot.Models.Entities;

[Table("Makes", Schema = "dbo")]

public partial class Make : BaseEntity
{
[Required] [StringLength(50)]
public string Name { get; set; } [InverseProperty(nameof(Inventory.Make))]
public virtual ICollection Inventories { get; set; }
}

The following code shows the Cars navigation property corrected, renaming the
Inventory/Inventories references to Car/Cars, the type changed to IEnumerable, and an initializer added:

[InverseProperty(nameof(Car.MakeNavigation))]
public virtual IEnumerable Cars { get; set; } = new List();

The MakeConfiguration Class
Create a new class named MakeConfiguration, implement the IEntityTypeConfiguration interface, and add the code from the ApplicationDbContext OnModelBuilding() method:

namespace AutoLot.Models.Entities.Configuration;

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

The Makes table will be configured as a temporal table, so add the following to the Configure() method:

public void Configure(EntityTypeBuilder builder)
{
builder.ToTable( b => b.IsTemporal(t =>
{
t.HasPeriodEnd("ValidTo"); t.HasPeriodStart("ValidFrom"); t.UseHistoryTable("MakesAudit");
}));
}

Delete the scaffolded configuration for the Make entity from the ApplicationDbContext Configure()
method to complete the Make entity.
Update the OnModelCreating() method in the ApplicationDbContext: protected override void OnModelCreating(ModelBuilder modelBuilder)
{
new CarConfiguration().Configure(modelBuilder.Entity());
new DriverConfiguration().Configure(modelBuilder.Entity());
new CarDriverConfiguration().Configure(modelBuilder.Entity());

new RadioConfiguration().Configure(modelBuilder.Entity());
new CustomerConfiguration().Configure(modelBuilder.Entity());
new MakeConfiguration().Configure(modelBuilder.Entity());
}

The CreditRisk Entity
The CreditRisks table was scaffolded to an entity class named CreditRisk. Inherit from BaseEntity and remove the Id and TimeStamp properties. Delete the constructor and add the Table attribute with schema. Remove the FirstName and LastName properties, as they will be replaced by the Person-owned entity. Here is the updated class code:

namespace AutoLot.Models.Entities;

[Table("CreditRisks", Schema = "dbo")] [Index(nameof(CustomerId), Name = "IX_CreditRisks_CustomerId")] [EntityTypeConfiguration(typeof(CreditRiskConfiguration))] public partial class CreditRisk : BaseEntity
{
public int CustomerId { get; set; } [ForeignKey(nameof(CustomerId))] [InverseProperty("CreditRisks")]
public virtual Customer Customer { get; set; }
}

Fix the navigation property by using the nameof() method in the InverseProperty attribute and add the Navigation suffix to the property name.

[ForeignKey(nameof(CustomerId))] [InverseProperty(nameof(Customer.CreditRisks))]
public virtual Customer CustomerNavigation { get; set; }

The final change is to add the owned property. The relationship will be further configured in the Fluent API.

public Person PersonInformation { get; set; } = new Person();

■Note as discussed when the CreditRisk table was introduced, having the Person-owned class and a navigation property to the Customer table feels like an odd design, and in truth, it is. all of these tables were created to teach a different aspect of eF Core, and this is no different. Consider the extra FirstName/LastName as a place to put the uncreditworthy individual’s alias.

That completes the CreditRisk entity.

The CreditRiskConfiguration Class
Add a new file named CreditRiskConfiguration.cs into the Configuration folder, and update the code to the following:

namespace AutoLot.Models.Entities.Configuration;

public class CreditRiskConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.HasOne(d => d.CustomerNavigation)
.WithMany(p => p.CreditRisks)
.HasForeignKey(d => d.CustomerId)
.HasConstraintName("FK_CreditRisks_Customers");

builder.OwnsOne(o => o.PersonInformation, pd =>
{

});

builder.Navigation(d => d.PersonInformation).IsRequired(true);
}
}

The CreditRisk entity uses the Person-owned property, so it cannot be configured as a temporal table.

Update the ApplicationDbContext OnModelCreating() method by removing the CreditRisk configuration and adding the line for the CreditRiskConfiguration class:

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

The Order Entity
The Orders table was scaffolded to an entity class named Order. Inherit from BaseEntity and remove the Id and
TimeStamp properties. Delete the constructor and add the Table attribute with schema. Here is the current code:

namespace AutoLot.Models.Entities;

[Table("Orders", Schema = "dbo")]
[Index(nameof(CarId), Name = "IX_Orders_CarId")]
[Index(nameof(CustomerId), nameof(CarId), Name = "IX_Orders_CustomerId_CarId", IsUnique
= true)]
public partial class Order : BaseEntity
{
public int CarId { get; set; } [ForeignKey(nameof(CarId))] [InverseProperty(nameof(Inventory.Orders))] public virtual Inventory Car { get; set; }

public int CustomerId { get; set; } [ForeignKey(nameof(CustomerId))] [InverseProperty("Orders")]
public virtual Customer { get; set; }
}

The Car and Customer navigation properties need the Navigation suffix added to their property names. The Car navigation property needs the type corrected to Car from Inventory. The inverse property needs the nameof() method to use Car.Orders instead of Inventory.Orders. The Customer navigation property needs to use the nameof() method for the InverseProperty.

[ForeignKey(nameof(CarId))] [InverseProperty(nameof(Car.Orders))]
public virtual Car CarNavigation { get; set; }

[ForeignKey(nameof(CustomerId))] [InverseProperty(nameof(Customer.Orders))]
public virtual Customer CustomerNavigation { get; set; }

The final step is to add the EntityTypeConfiguration attribute with the update in bold:

namespace AutoLot.Models.Entities;

[Table("Orders", Schema = "dbo")] [Index(nameof(CarId), Name = "IX_Orders_CarId")]
[Index(nameof(CustomerId), nameof(CarId), Name = "IX_Orders_CustomerId_CarId", IsUnique
= true)] [EntityTypeConfiguration(typeof(OrderConfiguration))] public partial class Order : BaseEntity
{
…
}

That completes the Order entity.

The OrderConfiguration Class
Add a new file named OrderConfiguration.cs into the Configuration folder, and update the code to the following:

namespace AutoLot.Models.Entities.Configuration;

public class OrderConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.HasIndex(cr => new { cr.CustomerId, cr.CarId }).IsUnique(true); builder.HasQueryFilter(e => e.CarNavigation!.IsDrivable); builder.HasOne(d => d.CarNavigation)
.WithMany(p => p.Orders)
.HasForeignKey(d => d.CarId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_Orders_Inventory"); builder.HasOne(d => d.CustomerNavigation)
.WithMany(p => p.Orders)
.HasForeignKey(d => d.CustomerId)
.HasConstraintName("FK_Orders_Customers");
}
}

The Orders table will be configured as a temporal table, so add the following to the Configure() method:

Update the ApplicationDbContext OnModelCreating() method by removing the Order configuration and adding the line for the OrderConfiguration class:

■Note at this time, both the autoLot.models project and the autoLot.dal project should build properly.

The SeriLogEntry Entity
The database needs an additional table to hold log records. The ASP.NET Core projects later in this book use the SeriLog logging framework, and one of the options is to write log records to a SQL Server table. We are going to add the table now, knowing it will be used a few chapters from now.
The table does not relate to any other tables and does not use the BaseEntity class. Add a new class file named SeriLogEntry.cs in the Entities folder. The code is listed in its entirety here:
namespace AutoLot.Models.Entities; [Table("SeriLogs", Schema = "Logging")]
[EntityTypeConfiguration(typeof(SeriLogEntryConfiguration))] public class SeriLogEntry
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; }
public string Message { get; set; }
public string MessageTemplate { get; set; } [MaxLength(128)]
public string Level { get; set; } [DataType(DataType.DateTime)]
public DateTime TimeStamp { get; set; } public string Exception { get; set; } public string Properties { get; set; } public string LogEvent { get; set; } public string SourceContext { get; set; } public string RequestPath { get; set; } public string ActionName { get; set; }
public string ApplicationName { get; set; } public string MachineName { get; set; } public string FilePath { get; set; }
public string MemberName { get; set; } public int LineNumber { get; set; } [NotMapped]
public XElement PropertiesXml => (Properties != null)? XElement.Parse(Properties):null;
}

■Note The TimeStamp property in this entity is not the same as the TimeStamp property in the BaseEntity class. The names are the same, but in this table it holds the date and time of when the entry was logged and not the rowversion used in the other entities.

Update the ApplicationDbContext Class
Since this is a new table, a new DbSet property must be added into the
ApplicationDbContext class. Add the following to the DbSet properties:

public virtual DbSet SeriLogEntries { get; set; }

The SerilogEntryConfiguration Class
Add a new file named SeriLogEntryConfiguration.cs into the Configuration folder, and update the code to the following:

namespace AutoLot.Models.Entities.Configuration;

public class SeriLogEntryConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.Property(e => e.Properties).HasColumnType("Xml"); builder.Property(e => e.TimeStamp).HasDefaultValueSql("GetDate()");
}
}

Update the ApplicationDbContext OnModelCreating() method by adding the line for the
SeriLogEntryConfiguration class:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
new CarConfiguration().Configure(modelBuilder.Entity());
new DriverConfiguration().Configure(modelBuilder.Entity());
new CarDriverConfiguration().Configure(modelBuilder.Entity()); new RadioConfiguration().Configure(modelBuilder.Entity());
new CustomerConfiguration().Configure(modelBuilder.Entity());
new CreditRiskConfiguration().Configure(modelBuilder.Entity()); new OrderConfiguration().Configure(modelBuilder.Entity());
new SeriLogEntryConfiguration().Configure(modelBuilder.Entity());
}

The View Models
The CustomerOrderView was scaffolded into a keyless entity along with the database tables. Another term used for keyless entities is view models, as they are designed to view data, usually from more than one table. This section will update the scaffolded entity into its final form as well as add a new view model to view temporal data. Start by adding a new folder named ViewModels in the AutoLot.Models project.

The CustomerOrderViewModel
Move the CustomerOrderView.cs class from the Entities folder into this folder and rename the file to
CustomerOrderViewModel.cs and the class to CustomerOrderViewModel. Add an EntityTypeConfiguration

attribute for the soon to be created configuration class. Also implement the INonPersisted interface (which will be created next):

namespace AutoLot.Models.ViewModels;

[Keyless] [EntityTypeConfiguration(typeof(CustomerOrderViewModelConfiguration))] public partial class CustomerOrderViewModel : INonPersisted
{
}

Add a new NotMapped property named FullDetail, as follows:

[NotMapped]
public string FullDetail => $"{FirstName} {LastName} ordered a {Color} {Make} named
{PetName}";

The FullDetail property is decorated with the NotMapped data annotation. Recall that this informs EF Core that this property is not to be included in the data coming from the database.
Next, add the four new properties for the Car entity to the view model:

public bool? IsDrivable { get; set; } public string Display { get; set; } public string Price {get;set; }
public DateTime? DateBuilt {get;set; }

Next add an override for the ToString() method. The ToString() override is also ignored by EF Core:

public override string ToString() => FullDetail;

That completes the changes to the view model. The complete file is shown here:
namespace AutoLot.Models.ViewModels; [Keyless]
[EntityTypeConfiguration(typeof(CustomerOrderViewModelConfiguration))] public partial class CustomerOrderViewModel : INonPersisted
{
[Required] [StringLength(50)]
public string FirstName { get; set; } [Required]
[StringLength(50)]
public string LastName { get; set; } [Required]
[StringLength(50)]
public string Color { get; set; } [Required]
[StringLength(50)]
public string PetName { get; set; } [Required]

[StringLength(50)]
public string Make { get; set; } public bool? IsDrivable { get; set; } public string Display { get;set; } [NotMapped]
public string FullDetail => $"{FirstName} {LastName} ordered a {Color} {Make} named
{PetName}";
public override string ToString() => FullDetail;
}

As a reminder, the KeyLess data annotation indicates this is an entity that works with data that does not have a primary key.

Add the INonPersisted Interface
Create a new folder named Interfaces in the ViewModels folder. In this folder, add a new interface named
INonPersisted, and update the code to the following:

namespace AutoLot.Models.ViewModels.Interfaces; public interface INonPersisted { }
Update the ApplicationDbContext Class
Since the CustomerOrderView class was renamed to CustomerOrderViewModel, the ApplicationDbContext class must be updated. Locate the DbSet< CustomerOrderView> property and update the line to the following:

public virtual DbSet< CustomerOrderViewModel> CustomerOrderViewModels { get; set; }

The CustomerOrderViewModelConfiguration Class
Create new folder named Configuration in the ViewModels folder. In this folder, add a new file named CustomerOrderViewModelConfiguration.cs into the Configuration folder, and update the code to the following:

namespace AutoLot.Models.ViewModels.Configuration;

public class CustomerOrderViewModelConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToView("CustomerOrderView");
}
}

Update the ApplicationDbContext OnModelCreating() method by deleting the configuration for the
CustomerOrderView class and adding the line for the CustomerOrderViewModelConfiguration class:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
new CarConfiguration().Configure(modelBuilder.Entity());
new DriverConfiguration().Configure(modelBuilder.Entity()); new RadioConfiguration().Configure(modelBuilder.Entity());
new CustomerConfiguration().Configure(modelBuilder.Entity());
new CreditRiskConfiguration().Configure(modelBuilder.Entity()); new OrderConfiguration().Configure(modelBuilder.Entity());
new SeriLogEntryConfiguration().Configure(modelBuilder.Entity());
new CustomerOrderViewModelConfiguration()
.Configure(modelBuilder.Entity());
}

Update the GlobalUsings.cs Files
The new namespaces need to be added into the GlobalUsings.cs files in the AutoLot.Dal and AutoLot. Models projects. Add the following global using statements to each file:

//AutoLot.Models
global using AutoLot.Models.ViewModels.Configuration; global using AutoLot.Models.ViewModels.Interfaces;

//AutoLot.Dal
global using AutoLot.Models.ViewModels;
global using AutoLot.Models.ViewModels.Configuration; global using AutoLot.Models.ViewModels.Interfaces;

The TemporalViewModel
Recall from the previous chapter that when working with temporal data, it helps to have a class that stores the row along with the row’s from and to dates. Create a new class named TemporalViewModel in the Entities folder. Update the code to the following:

namespace AutoLot.Models.ViewModels;

public class TemporalViewModel where T: BaseEntity, new()
{
public T Entity { get; set; }
public DateTime ValidFrom { get; set; } public DateTime ValidTo { get; set; }
}

Since this class is only going to be used to store the results of queries on temporal tables, it does not need to be configured in ApplicationDbContext.

Update the ApplicationDbContext
It is time to update the ApplicationDbContext.cs file. Delete the default constructor, as we won’t need it. It is only used in conjunction with an OnConfiguring() method, which, as discussed earlier, is considered not a good practice to use. The next constructor takes an instance of the DbContextOptions object and is fine for now. The event hooks for DbContext and ChangeTracker will be added later in this chapter.

Add the Mapped Database Functions
Recall that user-defined database functions can be mapped to C# functions for use in LINQ queries. Add the following functions to ApplicationDbContext for the two user-defined functions:

[DbFunction("udf_CountOfMakes", Schema = "dbo")] public static int InventoryCountFor(int makeId)
=> throw new NotSupportedException();

[DbFunction("udtf_GetCarsForMake", Schema = "dbo")] public IQueryable GetCarsFor(int makeId)
=> FromExpression(() => GetCarsFor(makeId));

Handling DbContext and ChangeTracker Events
Navigate to the constructor of ApplicationDbContext and add the three DbContext events discussed in the previous chapter.

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

Next, add handlers for the ChangeTracker, StateChanged, and Tracked events.

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

As a refresher, EntityTrackedEventArgs holds a reference to the entity that triggered the event and whether it came from a query (loaded from the database) or was added programmatically. Add the following event handler in ApplicationDbContext:

private void ChangeTracker_Tracked(object sender, EntityTrackedEventArgs e)
{
var source = (e.FromQuery) ? "Database" : "Code"; if (e.Entry.Entity is Car c)
{
Console.WriteLine($"Car entry {c.PetName} was added from {source}");
}
}

The StateChanged event is fired when a tracked entity’s state changes. In the following event handler, if the entity’s NewState is Unchanged, the OldState is examined to see whether the entity was added or modified. Add the following event handler into ApplicationDbContext:

private void ChangeTrackerStateChanged(object sender, EntityStateChangedEventArgs e)
{
if (e.Entry.Entity is not Car c)
{
return;
}
var action = string.Empty;
Console.WriteLine($"Car {c.PetName} was {e.OldState} before the state changed to
{e.NewState}"); switch (e.NewState)
{
case EntityState.Unchanged: action = e.OldState switch
{
EntityState.Added => "Added", EntityState.Modified => "Edited",
=> action
};
Console.WriteLine($"The object was {action}"); break;
}
}

Override the Conventions
Add the override for ConfigureConventions. As a reminder, the following overrides will default strings to nvarchar(50) and will ignore entities that implement the INonPersisted interface. Any data annotations or Fluent API commands that contradict those two settings will override the configured conventions:

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

Override the SaveChanges Method
Recall that the SaveChanges() method on the base DbContext class persists the data updates, additions, and deletions to the database. Overriding that method in the derived DbContext enables exception handing to be encapsulated in one place. Add the following override to the SaveChanges() method:

public override int SaveChanges()
{
try
{
return base.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
//A concurrency error occurred
//Should log and handle intelligently
throw new CustomConcurrencyException("A concurrency error happened.", ex);
}
catch (RetryLimitExceededException ex)
{
//DbResiliency retry limit exceeded
//Should log and handle intelligently
throw new CustomRetryLimitExceededException("There is a problem with SQL Server.", ex);
}
catch (DbUpdateException ex)
{
//Should log and handle intelligently
throw new CustomDbUpdateException("An error occurred updating the database", ex);
}
catch (Exception ex)
{
//Should log and handle intelligently
throw new CustomException("An error occurred updating the database", ex);
}
}

Create the Next Migration and Update the Database
At this point in the chapter, we are ready to create another migration to update the database. Enter the following commands in the AutoLot.Dal project directory (each command must be entered on one line):

dotnet ef migrations add UpdatedEntities -o EfStructures\Migrations -c AutoLot.Dal.
EfStructures.ApplicationDbContext

dotnet ef database update UpdatedEntities -c AutoLot.Dal.EfStructures.ApplicationDbContext

Use EF Migrations to Create/Update Database Objects
While the CustomerOrderViewModel was scaffolded from the CustomerOrderView in the database, the view itself is not represented in the C# code. If you were to drop the database and re-create it using the EF Core migrations, the view will not exist. For database objects, you have two options: maintain them separately and apply them using SSMS/Azure Data Studio or leverage the EF Core migrations to handle their creation.
Recall that each EF Core migration file has an Up() method (for applying the migration to the database) and a Down() method (for rolling the changes back). The MigrationBuilder also has a Sql() method that executes SQL statements directly against the database. By adding the CREATE and DROP statements into the Up() and Down() methods of a migration, the migration system will handle applying (and rolling back) database changes.

Add the MigrationHelpers Class
A helper class will hold all the SQL statements used by the custom migration. This separation prevents losing the code if the migration is removed from the system. Create a new static class named MigrationHelpers. cs in the EfStructures folder of the AutoLot.Dal project.
Add a new file named MigrationHelpers.cs in the EfStructures folder of the AutoLot.Dal project.
Add a using statement for Microsoft.EntityFrameworkCore.Migrations, make the class public and static, and add the following methods, which use the MigrationBuilder to execute SQL statements against the database:
namespace AutoLot.Dal.EfStructures; public static class MigrationHelpers
{
public static void CreateCustomerOrderView(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"exec (N’
CREATE VIEW [dbo].[CustomerOrderView] AS
SELECT c.FirstName, c.LastName, i.Color, i.PetName, i.DateBuilt, i.IsDrivable, i.Price, i.Display, m.Name AS Make
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON c.Id = o.CustomerId INNER JOIN dbo.Inventory i ON i.Id = o.CarId INNER JOIN dbo.Makes m ON m.Id = i.MakeId’)");
}
public static void DropCustomerOrderView(MigrationBuilder migrationBuilder)
{

migrationBuilder.Sql("EXEC (N’ DROP VIEW [dbo].[CustomerOrderView] ‘)");
}

public static void CreateSproc(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"exec (N’
CREATE PROCEDURE [dbo].[GetPetName] @carID int,
@petName nvarchar(50) output AS
SELECT @petName = PetName from dbo.Inventory where Id = @carID’)");
}
public static void DropSproc(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("EXEC (N’ DROP PROCEDURE [dbo].[GetPetName]’)");
}

public static void CreateFunctions(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"exec (N’
CREATE FUNCTION [dbo].[udtf_GetCarsForMake] ( @makeId int ) RETURNS TABLE
AS RETURN (
SELECT Id, IsDrivable, DateBuilt, Color, PetName, MakeId, TimeStamp, Display, Price
FROM Inventory WHERE MakeId = @makeId
)’)");
migrationBuilder.Sql(@"exec (N’
CREATE FUNCTION [dbo].[udf_CountOfMakes] ( @makeid int ) RETURNS int
AS BEGIN
DECLARE @Result int
SELECT @Result = COUNT(makeid) FROM dbo.Inventory WHERE makeid = @makeid RETURN @Result
END’)");
}
public static void DropFunctions(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("EXEC (N’ DROP FUNCTION [dbo].[udtf_GetCarsForMake]’)"); migrationBuilder.Sql("EXEC (N’ DROP FUNCTION [dbo].[udf_CountOfMakes]’)");
}
}

■Note The CREATE statements are included in a sQL server exec statement so they will successfully run when the migrations are scripted. each migration process is wrapped in an IF block, and creation statements must be wrapped in exec statements when executed inside an IF.

Create and Update the Migration
Calling the dotnet migrations add command when there aren’t any model changes will still create the properly timestamped migration files with empty Up() and Down() methods. Execute the following to create the empty migration (but do not apply the migration):

dotnet ef migrations add SQL -o EfStructures\Migrations -c AutoLot.Dal.EfStructures. ApplicationDbContext

Open the newly created migration class and notice that the Up() and Down() methods are empty. That is the key to this technique. Using a blank migration that is updated using the MigrationHelpers methods prevents mixing custom code with EF Core–generated code. The static methods to create the database objects go into the migration’s Up() method, and the methods to drop the database objects go into the migration’s Down() method. When this migration is applied, the SQL Server objects are created, and when the migration is rolled back, the SQL Server objects are dropped. Here is the updated migration code listing:
namespace AutoLot.Dal.EfStructures.Migrations’ public partial class SQL : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
MigrationHelpers.CreateCustomerOrderView(migrationBuilder); MigrationHelpers.CreateSproc(migrationBuilder); MigrationHelpers.CreateFunctions(migrationBuilder);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
MigrationHelpers.DropCustomerOrderView(migrationBuilder); MigrationHelpers.DropSproc(migrationBuilder); MigrationHelpers.DropFunctions(migrationBuilder);
}
}

Apply the Migration
If you dropped your database to run the initial migration, you can apply this migration and move on. Apply the migration by executing the following command:

dotnet ef database update -c AutoLot.Dal.EfStructures.ApplicationDbContext

If you did not drop your database for the first migration, the procedure and view already exist, and the create SQL statements for those database objects will fail, rolling the entire migration back. The simple fix is to drop the procedure and view using SSMS or Azure Data Studio, like this:

DROP VIEW [dbo].[CustomerOrderView] GO
DROP PROCEDURE [dbo].[GetPetName] GO

Now, apply the migration to add the SQL objects into the AutoLot database:

dotnet ef database update SQL -c AutoLot.Dal.EfStructures.ApplicationDbContext

■Note you could also write code that will first check for an object’s existence and drop it if it already exists, but I find that overkill for an issue happens only once when transitioning from database first to code first.

Add the Repositories
A common data access design pattern is the repository pattern. As described by Martin Fowler, the core of this pattern is to mediate between the domain and data mapping layers. Having a generic base repository that contains the common data access code helps to eliminate duplication of code. Having specific repositories and interfaces that derive from a base repository also works well with the dependency injection framework in ASP.NET Core.

■Note This next section is not meant to be (nor does it pretend to be) a literal interpretation of mr. Fowler’s design pattern. If you are interested in the original pattern that motivated this version, you can find more information on the repository pattern at www.martinfowler.com/eaaCatalog/repository.html.

Each of the domain entities in the AutoLot data access layer will have a strongly typed repo to encapsulate all the data access work. To start, create a folder named Repos in the AutoLot.Dal project to hold all the classes.

Add the IBaseViewRepo Interface
The IBaseViewRepo interface exposes three methods for getting data from a view model. Make a new folder named Base in the Repos directory. Add a new interface into the Repos\Base folder named IBaseViewRepo. Update the code to match the following:

namespace AutoLot.Dal.Repos.Base;

public interface IBaseViewRepo: IDisposable where T : class,new()
{
ApplicationDbContext Context {get;} IEnumerable ExecuteSqlString(string sql); IEnumerable GetAll();
IEnumerable GetAllIgnoreQueryFilters();
}

Add the BaseViewRepo Implementation
Next, add a class named BaseViewRepo to the Repos\Base directory. This class will implement the IBaseViewRepo interface and provide the implementation for the interface. Make the class generic with type T and constrain the type to class and new(), which limits the types to classes that have a parameterless constructor. Implement the IBaseViewRepo interface, as follows:

namespace AutoLot.Dal.Repos.Base;

public abstract class BaseViewRepo : IBaseViewRepo where T : class, new()
{
//implementation goes here
}

The repo needs an instance of the ApplicationDbContext injected into a constructor. When used with the ASP.NET Core dependency injection (DI) container, the container will handle the lifetime of the context. A second constructor, used for integration testing, will accept an instance of DbContextOptions and use that to create an instance of the ApplicationDbContext. That context will need to be disposed since it isn’t being managed by a DI container. Because this class is abstract, both constructors are protected. Add the following code for the public ApplicationDbContext, the two constructors, and the Dispose pattern:

private readonly bool _disposeContext; public ApplicationDbContext Context { get; }

protected BaseViewRepo(ApplicationDbContext context)
{
Context = context;
_disposeContext = false;
}

protected BaseViewRepo(DbContextOptions options) : this(new ApplicationDbContext(options))
{
_disposeContext = true;
}

public virtual void Dispose()
{
Dispose(true); GC.SuppressFinalize(this);
}
private bool _isDisposed;
protected virtual void Dispose(bool disposing)
{
if (_isDisposed)
{
return;
}

if (disposing)
{
if (_disposeContext)
{
Context.Dispose();
}
}
_isDisposed = true;
}

~BaseViewRepo()
{
Dispose(false);
}

The DbSet properties of ApplicationDbContext can be referenced by using the DbContext.Set() method. Create a public property named Table of type DbSet and set the value in the initial constructor, like this:

public DbSet Table { get; }
protected BaseViewRepo(ApplicationDbContext context)
{
Context = context;
Table = Context.Set();
_disposeContext = false;
}

Implement the Read Methods
The next series of methods returns records using LINQ statements or a SQL query. The GetAll() methods return all the records from the table. The first retrieves them in database order, and the second turns off all query filters.

public virtual IEnumerable GetAll()
=> Table.AsQueryable();

public virtual IEnumerable GetAllIgnoreQueryFilters()
=> Table.AsQueryable().IgnoreQueryFilters();

The ExecuteSqlString() method is there to execute FromSqlRaw() queries:

public IEnumerable ExecuteSqlString(string sql) => Table.FromSqlRaw(sql);

Add the IBaseRepo Interface
The IBaseRepo interface exposes many of the common methods used in data access with the DbSet properties where T is of type BaseEntity. Add a new interface into the Repos\Base folder named IBaseRepo. The full interface is listed here:

namespace AutoLot.Dal.Repos.Base;

public interface IBaseRepo : IBaseViewRepo where T : BaseEntity, new()
{
T Find(int? id);
T FindAsNoTracking(int id);
T FindIgnoreQueryFilters(int id);
void ExecuteParameterizedQuery(string sql, object[] sqlParametersObjects); int Add(T entity, bool persist = true);
int AddRange(IEnumerable entities, bool persist = true); int Update(T entity, bool persist = true);

int UpdateRange(IEnumerable entities, bool persist = true); int Delete(int id, byte[] timeStamp, bool persist = true);
int Delete(T entity, bool persist = true);
int DeleteRange(IEnumerable entities, bool persist = true); int SaveChanges();
}

Add the BaseRepo Implementation
Next, add a class named BaseRepo to the Repos\Base directory. This class implements the IBaseRepo interface, inherits from the BaseViewRepo abstract class, and provides the core functionality for the type-specific repos that will be built for each entity. Make the class generic with type T and constrain the type to BaseEntity and new(), which limits the types to classes that inherit from BaseEntity and have a parameterless constructor. Implement the IBaseRepo interface, as follows:

namespace AutoLot.Dal.Repos.Base;

public abstract class BaseRepo : BaseViewRepo, IBaseRepo where T : BaseEntity, new()
{
//implementation goes here
}

The repo leverages the BaseViewRepo for the handling of the ApplicationDbContext instance as well as the Dispose() pattern implementation. Add the following code for the two constructors:

protected BaseRepo(ApplicationDbContext context) : base(context) {}
protected BaseRepo(DbContextOptions options) : this(new ApplicationDbContext(options))
{
}

Implement the SaveChanges Method
The BaseRepo has a SaveChanges() method that calls into the overridden SaveChanges() method on the
ApplicationDbContext class. Add the following code to the BaseRepo class:

public int SaveChanges()
{
try
{
return Context.SaveChanges();
}
catch (CustomException ex)
{
//Should handle intelligently – already logged throw;
}
catch (Exception ex)
{

//Should log and handle intelligently
throw new CustomException("An error occurred updating the database", ex);
}
}

Implement the Common Read Methods
The next series of methods returns records using LINQ statements. The Find() method takes the primary key value(s) and searches the ChangeTracker first. If the entity is already being tracked, the tracked instance is returned. If not, the record is retrieved from the database.

public virtual T Find(int? id) => Table.Find(id);

The two additional Find() methods augment the Find() base method. The next method demonstrates retrieving a record but not adding it to the ChangeTracker using AsNoTrackingWithIdentityResolution(). Add the following code to the class:

public virtual T FindAsNoTracking(int id) => Table.AsNoTrackingWithIdentityResolution().FirstOrDefault(x => x.Id == id);

The next variation removes the query filters from the entity and then uses the shorthand version (skipping the Where() method) to get FirstOrDefault(). Add the following to the class:

public virtual T FindIgnoreQueryFilters(int id) => Table.IgnoreQueryFilters().FirstOrDefault(x => x.Id == id);

The final method’s next variation is used to execute a parameterized stored procedure. Add the final method to the class:

public virtual void ExecuteParameterizedQuery(string sql, object[] sqlParametersObjects)
=> Context.Database.ExecuteSqlRaw(sql, sqlParametersObjects);

The Add, Update, and Delete Methods
The next block of code to be added wraps the matching Add()/AddRange(), Update()/UpdateRange(), and Remove()/RemoveRange() methods on the specific DbSet property. The persist parameter determines whether the repo executes SaveChanges() immediately when the repository methods are called. All the methods are marked virtual to allow for downstream overriding. Add the following code to your class:

public virtual int Add(T entity, bool persist = true)
{
Table.Add(entity);
return persist ? SaveChanges() : 0;
}
public virtual int AddRange(IEnumerable entities, bool persist = true)
{
Table.AddRange(entities);
return persist ? SaveChanges() : 0;
}
public virtual int Update(T entity, bool persist = true)

{
Table.Update(entity);
return persist ? SaveChanges() : 0;
}
public virtual int UpdateRange(IEnumerable entities, bool persist = true)
{
Table.UpdateRange(entities);
return persist ? SaveChanges() : 0;
}
public virtual int Delete(T entity, bool persist = true)
{
Table.Remove(entity);
return persist ? SaveChanges() : 0;
}
public virtual int DeleteRange(IEnumerable entities, bool persist = true)
{
Table.RemoveRange(entities);
return persist ? SaveChanges() : 0;
}

There is one more Delete() method that doesn’t follow the same pattern. This method uses EntityState to conduct the delete operation, which is used fairly often in ASP.NET Core operations to cut down on the network traffic. It is listed here:

public int Delete(int id, byte[] timeStamp, bool persist = true)
{
var entity = new T {Id = id, TimeStamp = timeStamp}; Context.Entry(entity).State = EntityState.Deleted; return persist ? SaveChanges() : 0;
}

This concludes the BaseRepo class, and now it’s time to build the repo for temporal table support.

Add the ITemporalTableBaseRepo Interface
The ITemporalTableBaseRepo interface exposes the temporal capabilities of EF Core. Add a new interface into the Repos\Base folder named ITemporalTableBaseRepo. The full interface is listed here:

namespace AutoLot.Dal.Repos.Base;

public interface ITemporalTableBaseRepo : IBaseRepo where T : BaseEntity, new()
{
IEnumerable<TemporalViewModel> GetAllHistory(); IEnumerable<TemporalViewModel> GetHistoryAsOf(DateTime dateTime);
IEnumerable<TemporalViewModel> GetHistoryBetween(DateTime startDateTime, DateTime endDateTime);
IEnumerable<TemporalViewModel> GetHistoryContainedIn(DateTime startDateTime, DateTime endDateTime);
IEnumerable<TemporalViewModel> GetHistoryFromTo(DateTime startDateTime, DateTime endDateTime);
}

Add the TemporalTableBaseRepo Implementation
Next, add a class named TemporalTableBaseRepo to the Repos\Base directory. This class implements the ITemporalTableBaseRepo interface, inherits from BaseRepo, and provides the functionality for using temporal tables. Also make the class generic with type T and constrain the type to BaseEntity and new(). Implement the ITemporalTableBaseRepo interface, as follows:

namespace AutoLot.Dal.Repos.Base;

public abstract class TemporalTableBaseRepo : BaseRepo, ITemporalTableBaseRepo where T : BaseEntity, new()
{
//implementation goes here
}

The repo leverages the BaseRepo for the handling of the ApplicationDbContext instance as well as the Dispose() pattern implementation. Add the following code for the two constructors:

protected TemporalTableBaseRepo(ApplicationDbContext context) : base(context) {} protected TemporalTableBaseRepo(DbContextOptions options)
: this(new ApplicationDbContext(options))
{
}

Implement the Helper Methods
There are two helper methods in this class. The first converts the current time (based on the TimeZoneInfo of the executing computer) to UTC time, and the second encapsulates the execution of the query and the projection to the TemporalViewModel. Add the following code to the class to convert the current time to UTC:

internal static DateTime ConvertToUtc(DateTime dateTime)
=> TimeZoneInfo.ConvertTimeToUtc(dateTime, TimeZoneInfo.Local);

The next method takes in an IQueryable, adds the OrderBy clause for the ValidFrom field, and projects the results into a collection of TemporalViewModel instances:

internal static IEnumerable<TemporalViewModel> ExecuteQuery(IQueryable query)
=> query.OrderBy(e => EF.Property(e, "ValidFrom"))
.Select(e => new TemporalViewModel
{
Entity = e,
ValidFrom = EF.Property(e, "ValidFrom"), ValidTo = EF.Property(e, "ValidTo")
});

Implement the Temporal Methods
The final step is to implement the five temporal-based methods of the interface. They take in the required data parameters, call the relevant EF Core temporal method, and then pass execution to the ExecuteQuery() helper method:

public IEnumerable<TemporalViewModel> GetAllHistory()
=> ExecuteQuery(Table.TemporalAll());

public IEnumerable<TemporalViewModel> GetHistoryAsOf(DateTime dateTime)
=> ExecuteQuery(Table.TemporalAsOf(ConvertToUtc(dateTime)));

public IEnumerable<TemporalViewModel> GetHistoryBetween( DateTime startDateTime, DateTime endDateTime)
=> ExecuteQuery(Table.TemporalBetween(ConvertToUtc(startDateTime), ConvertToUtc(endDateTime)));
public IEnumerable<TemporalViewModel> GetHistoryContainedIn( DateTime startDateTime, DateTime endDateTime)
=> ExecuteQuery(Table.TemporalContainedIn(ConvertToUtc(startDateTime), ConvertToUtc(endDateTime)));
public IEnumerable<TemporalViewModel> GetHistoryFromTo( DateTime startDateTime, DateTime endDateTime)
=> ExecuteQuery(Table.TemporalFromTo(ConvertToUtc(startDateTime), ConvertToUtc(endDa teTime)));

Now that all the base repos are completed, it’s time to build the entity-specific repos.

Entity-Specific Repo Interfaces
Each entity and view model will have a strongly typed repository derived from BaseRepo and an interface that implements IRepo. Add a new folder named Interfaces under the Repos directory in the AutoLot. Dal project. In this new directory, add the following interfaces:
ICarDriverRepo ICarRepo.cs ICreditRiskRepo.cs
ICustomerOrderViewModelRepo.cs ICustomerRepo.cs
IDriverRepo IMakeRepo.cs IOrderRepo.cs IRadioRepo
The next sections complete the interfaces.

■Note The interfaces in this section are simple and not indicative of a real-world application. When building production applications with this pattern, the entity specific interfaces will usually hold a significant number of additional methods. These examples are kept simple to show the pattern and how it is used.

The CarDriver Repository Interface
Open the ICarDriverRepo.cs interface. This interface doesn’t add any functionality beyond what is provided in the TemporalTableBaseRepo. Update the code to the following: