Building a reusable entity data model

Practical applications usually need to work with data in a relational database or another data store. Earlier in this chapter, we defined EF Core models in the same console app project that we used them in. Now, we will define an entity data model for the Northwind database as a pair of reusable class libraries. One part of the pair will define the entities like Product and Customer. The second part of the pair will define the tables in the database, default configuration for how to connect to the database, and use fluent API to configure additional options for the model. This pair of class libraries will be used in many of the apps and services that you create in subsequent chapters.

Good Practice: You should create a separate class library project for your entity data models. This allows easier sharing between backend web servers and frontend desktop, mobile, and Blazor WebAssembly clients.

Creating a class library for entity models using SQL Server

You will now create the entity models using the dotnet-ef tool:

1. Add a new project, as defined in the following list:
   • Project template: Class Library/classlib
   • Project file and folder: Northwind.Common.EntityModels.SqlServer
   • Workspace/solution file and folder: Chapter02
2. In the Northwind.Common.EntityModels.SqlServer project, treat warnings as errors, and add package references for the SQL Server database provider and EF Core design-time support, as shown highlighted in the following markup:

   <Project Sdk="Microsoft.NET.Sdk">
     <PropertyGroup>
       <TargetFramework>net7.0</TargetFramework>
       <ImplicitUsings>enable</ImplicitUsings>
       <Nullable>enable</Nullable>
       <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
     </PropertyGroup>
     <ItemGroup>
       <PackageReference
         Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.0" />
       <PackageReference 
         Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.0">
         <PrivateAssets>all</PrivateAssets>
       <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
       </PackageReference>  
     </ItemGroup>
   </Project>
   

3. Delete the Class1.cs file.
4. Build the Northwind.Common.EntityModels.SqlServer project.
5. Open a command prompt or terminal for the Northwind.Common.EntityModels.SqlServer folder.

   The next step assumes a database connection string for a local SQL Server authenticated with Windows Integrated security. Modify it for Azure SQL Database or Azure SQL Edge with a user ID and password if necessary.

6. At the command line, generate entity class models for all tables, as shown in the following commands:

   dotnet ef dbcontext scaffold "Data Source=.;Initial Catalog=Northwind;Integrated Security=true;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer --namespace Packt.Shared --data-annotations
   

   Note the following:

   • The command to perform: dbcontext scaffold
   • The connection string: "Data Source=.;Initial Catalog=Northwind;Integrated Security=true;TrustServerCertificate=True;"
   • The database provider: Microsoft.EntityFrameworkCore.SqlServer
   • The namespace for the generated classes: --namespace Packt.Shared
   • To use data annotations as well as the Fluent API: --data-annotations

7. Note that 28 classes were generated, from AlphabeticalListOfProduct.cs to Territory.cs.
8. In Customer.cs, the dotnet-ef tool correctly identified that the CustomerId column is the primary key and it is limited to a maximum of five characters, but we also want the values to always be uppercase. So, add a regular expression to validate its primary key value to only allow uppercase Western characters, as shown highlighted in the following code:

   [Key]
   [StringLength(5)]
   [RegularExpression("[A-Z]{5}")]  
   public string CustomerId { get; set; } = null!;
   

Creating a class library for the data context using SQL Server

Next, you will move the context model that represents the database to a separate class library:

1. Add a new project, as defined in the following list:
   • Project template: Class Library/classlib
   • Project file and folder: Northwind.Common.DataContext.SqlServer
   • Workspace/solution file and folder: Chapter02
   • In Visual Studio Code, select Northwind.Common.DataContext.SqlServer as the active OmniSharp project.
2. In the DataContext project, treat warnings as errors, add a project reference to the EntityModels project, and add a package reference to the EF Core data provider for SQL Server, as shown highlighted in the following markup:

   <Project Sdk="Microsoft.NET.Sdk">
     <PropertyGroup>
       <TargetFramework>net7.0</TargetFramework>
       <ImplicitUsings>enable</ImplicitUsings>
       <Nullable>enable</Nullable>
       <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
     </PropertyGroup>
     <ItemGroup>
       <PackageReference 
         Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.0" />
     </ItemGroup>
     <ItemGroup>
       <ProjectReference Include="..\Northwind.Common.EntityModels
   .SqlServer\Northwind.Common.EntityModels.SqlServer.csproj" />
     </ItemGroup>
   </Project>
   

   Warning! The path to the project reference should not have a line break in your project file.

3. In the Northwind.Common.DataContext.SqlServer project, delete the Class1.cs file.
4. Build the Northwind.Common.DataContext.SqlServer project.
5. Move the NorthwindContext.cs file from the Northwind.Common.EntityModels.SqlServer project/folder to the Northwind.Common.DataContext.SqlServer project/folder.
6. In the Northwind.Common.DataContext.SqlServer project, in NorthwindContext.cs, remove the compiler warning about the connection string.
7. In the Northwind.Common.DataContext.SqlServer project, add a class named NorthwindContextExtensions.cs, and modify its contents to define an extension method that adds the Northwind database context to a collection of dependency services, as shown in the following code:

   using Microsoft.EntityFrameworkCore; // UseSqlServer
   using Microsoft.Extensions.DependencyInjection; // IServiceCollection
   namespace Packt.Shared;
   public static class NorthwindContextExtensions
   {
     /// <summary>
     /// Adds NorthwindContext to the specified IServiceCollection. Uses the SqlServer database provider.
     /// </summary>
     /// <param name="services"></param>
     /// <param name="connectionString">Set to override the default.</param>
     /// <returns>An IServiceCollection that can be used to add more services.</returns>
     public static IServiceCollection AddNorthwindContext(
       this IServiceCollection services,
       string connectionString = "Data Source=.;Initial Catalog=Northwind;" +
         "Integrated Security=true;MultipleActiveResultsets=true;Encrypt=false")
     {
       services.AddDbContext<NorthwindContext>(options =>
       {
         options.UseSqlServer(connectionString);
         options.LogTo(Console.WriteLine,
           new[] { Microsoft.EntityFrameworkCore
             .Diagnostics.RelationalEventId.CommandExecuting });
       });
       return services;
     }
   }
   

8. Build the two class libraries and fix any compiler errors.

Good Practice: We have provided an optional argument for the AddNorthwindContext method so that we can override the SQL Server database connection string. This will allow us more flexibility, for example, to load these values from a configuration file.

Calculated properties on entity creation

EF Core 7 adds an IMaterializationInterceptor interface that allows interception before and after an entity is created, and when properties are initialized. This is useful for calculated values.

For example, when a service or client app requests entities to show to the user, it might want to cache a copy of the entity for a period of time. To do this, it needs to know when the entity was last refreshed. It would be useful if this information was automatically generated and stored with each entity.

To achieve this goal, we must complete four steps:

1. First, define an interface with the extra property.
2. Next, at least one entity model class must implement the interface.
3. Then, define a class that implements the interceptor interface with a method named InitializedInstance that will execute on any entity, and if that entity implements the custom interface with the extra property, then it will set its value.
4. Finally, we must create an instance of the interceptor and register it in the data context class.

Now let’s implement this for Northwind Employee entities:

1. In the Northwind.Common.EntityModels.SqlServer project, add a new file named IHasLastRefreshed.cs, and modify its contents to define the interface, as shown in the following code:

   namespace Packt.Shared;
   public interface IHasLastRefreshed
   {
     DateTimeOffset LastRefreshed { get; set; }
   }
   

2. In the Northwind.Common.EntityModels.SqlServer project, in Employee.cs, implement the interface, as shown highlighted in the following code:

   public partial class Employee : IHasLastRefreshed
   {
     ...
     [NotMapped]
     public DateTimeOffset LastRefreshed { get; set; }
   }
   

3. In the Northwind.Common.DataContext.SqlServer project, add a new file named SetLastRefreshedInterceptor.cs, and modify its contents to define the interceptor, as shown in the following code:

   // IMaterializationInterceptor, MaterializationInterceptionData
   using Microsoft.EntityFrameworkCore.Diagnostics;
   namespace Packt.Shared;
   public class SetLastRefreshedInterceptor : IMaterializationInterceptor
   {
     public object InitializedInstance(
       MaterializationInterceptionData materializationData,
       object entity)
     {
       if (entity is IHasLastRefreshed entityWithLastRefreshed)
       {
         entityWithLastRefreshed.LastRefreshed = DateTimeOffset.UtcNow;
       }
       return entity;
     }
   }
   

4. In the Northwind.Common.DataContext.SqlServer project, in NorthwindContext.cs, register the interceptor, as shown highlighted in the following code:

   public partial class NorthwindContext : DbContext
   {
     private static readonly SetLastRefreshedInterceptor
       setLastRefreshedInterceptor = new();
   ...
     protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
     {
       if (!optionsBuilder.IsConfigured)
       {
         optionsBuilder.UseSqlServer("...");
       }
       optionsBuilder.AddInterceptors(setLastRefreshedInterceptor);
     }
     ...
   }
   

5. Save changes.

Creating a test project to check the integration of the class libraries

Since we will not be creating a client project in this chapter that uses the EF Core model, we should create a test project to make sure the database context and entity models integrate correctly:

1. Use your preferred coding tool to add a new xUnit Test Project [C#]/xunit project named Northwind.Common.EntityModels.Tests to the Chapter02 workspace/solution.
2. In Northwind.Common.EntityModels.Tests.csproj, modify the configuration to treat warnings as errors and to add an item group with a project reference to the Northwind.Common.DataContext.SqlServer project, as shown in the following markup:

   <ItemGroup>
     <ProjectReference Include="..\Northwind.Common.DataContext
   .SqlServer\Northwind.Common.DataContext.SqlServer.csproj" />
   </ItemGroup>
   

   Warning! The path to the project reference should not have a line break in your project file.

3. Build the Northwind.Common.EntityModels.Tests project.

Writing unit tests for entity models

A well-written unit test will have three parts:

• Arrange: This part will declare and instantiate variables for input and output.
• Act: This part will execute the unit that you are testing. In our case, that means calling the method that we want to test.
• Assert: This part will make one or more assertions about the output. An assertion is a belief that, if not true, indicates a failed test. For example, when adding 2 and 2, we would expect the result to be 4.

Now, we will write some unit tests for the NorthwindContext and entity model classes:

1. Rename the file UnitTest1.cs to NorthwindEntityModelsTests.cs and then open it.
2. In Visual Studio Code, rename the class to NorthwindEntityModelsTests. (Visual Studio prompts you to rename the class when you rename the file.)
3. Modify the NorthwindEntityModelsTests class to import the Packt.Shared namespace and have some test methods for ensuring the context class can connect, ensuring the provider is SQL Server, and ensuring the first product is named Chai, as shown in the following code:

   using Packt.Shared;
   namespace Northwind.Common.EntityModels.Tests
   {
     public class NorthwindEntityModelsTests
     {
       [Fact]
       public void CanConnectIsTrue()
       {
         using (NorthwindContext db = new()) // arrange
         {
           bool canConnect = db.Database.CanConnect(); // act
           Assert.True(canConnect); // assert
         }
       }
       [Fact]
       public void ProviderIsSqlServer()
       {
         using (NorthwindContext db = new())
         {
           string? provider = db.Database.ProviderName;
           Assert.Equal("Microsoft.EntityFrameworkCore.SqlServer", provider);
         }
       }
       [Fact]
       public void ProductId1IsChai()
       {
         using(NorthwindContext db = new())
         {
           Product product1 = db.Products.Single(p => p.ProductId == 1);
           Assert.Equal("Chai", product1.ProductName);
         }
       }
       [Fact]
       public void EmployeeHasLastRefreshedIn10sWindow()
       {
         using (NorthwindContext db = new())
         {
           Employee employee1 = db.Employees.Single(p => p.EmployeeId == 1);
           DateTimeOffset now = DateTimeOffset.UtcNow;
           Assert.InRange(actual: employee1.LastRefreshed,
             low: now.Subtract(TimeSpan.FromSeconds(5)),
             high: now.AddSeconds(5));
         }
       }
     }
   }
   

Running unit tests using Visual Studio 2022

Now we are ready to run the unit tests and see the results:

1. In Visual Studio 2022, navigate to Test | Run All Tests.
2. In Test Explorer, note that the results indicate that some tests ran, and all passed.

Running unit tests using Visual Studio Code

Now we are ready to run the unit tests and see the results:

1. In Visual Studio Code, in the Northwind.Common.EntityModels.Tests project’s TERMINAL window, run the tests, as shown in the following command:

   dotnet test
   

2. In the output, note that the results indicate that some tests ran, and all passed.

As an optional task, can you think of other tests you could write to make sure the database context and entity models are correct?