Setting up a database and projects for this book

We need a database and some projects that we can use throughout this book. To make it reasonably realistic, we need multiple projects that use common features like a SQL Server database, class libraries, unit tests, and so on.

We will define an entity data model 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, the default configuration for how to connect to the database, and use the Fluent API to configure additional options for the model.

We will create three projects:

• A class library for entity models like Category and Product named Northwind.EntityModels
• A class library for an EF Core data context named Northwind.DataContext
• An xUnit project for unit and integration tests named Northwind.Tests

Using a sample relational database

It would be useful to have a sample database that has a medium complexity and a decent number of sample records. Microsoft offers several sample databases, most of which are too complex for our needs, so instead, we will use a database that was first created in the early 1990s known as Northwind.

Let’s take a minute to look at a diagram of the Northwind database and its eight most important tables. You can use the diagram in Figure 1.2 to refer to as we write code and queries throughout this book:

Figure 1.2: The Northwind database tables and relationships

Note that:

• Each category has a unique identifier, name, description, and picture. The picture is stored as a byte array in JPEG format.
• Each product has a unique identifier, name, unit price, number of units in stock, and other columns.
• Each product is associated with a category by storing the category’s unique identifier.
• The relationship between Categories and Products is one-to-many, meaning each category can have zero, one, or more products.
• Each product is supplied by a supplier company indicated by storing the supplier’s unique identifier.
• The quantity and unit price of a product is stored for each detail of an order.
• Each order is made by a customer, taken by an employee, and shipped by a shipping company.
• Each employee has a name, address, contact details, birth, and hire dates, a reference to their manager (except for the boss, whose ReportsTo field is null), and a photo stored as a byte array in JPEG format. The table has a one-to-many relationship to itself because one employee can manage many other employees.

Setting up SQL Server and the Northwind database

Microsoft offers various editions of its popular and capable SQL Server product for Windows, Linux, and Docker containers.

If you do not have a Windows computer or if you want to use a cross-platform database system, then please see the online-only section, Installing Azure SQL Edge in Docker, found at the following link: https://github.com/markjprice/tools-skills-net8/blob/main/docs/sql-server/edge.md.

You’ll need to have set up SQL Server, run the SQL script to create the Northwind database and confirm that you can connect to the database and view the rows in its tables like Products and Categories before continuing with the project. The following two subsections provide detailed steps to help you do so using either a local SQL Server or SQL Edge in Docker. You can skip this if you already have this set up.

Creating the Northwind database for a local SQL Server

To run a SQL script to create the Northwind sample database for a local SQL Server:

1. If you have not previously downloaded or cloned the GitHub repository for this book, then do so now using the following link: https://github.com/markjprice/tools-skills-net8/.
2. Copy the script to create the Northwind database for SQL Server from the following path in your local Git repository: /scripts/sql-scripts/Northwind4SQLServer.sql into a working folder.
3. Start SQL Server Management Studio.
4. In the Connect to Server dialog, for Server name, enter . (a dot), meaning the local computer name, and then click the Connect button. If you had to create a named instance, like tools-skills-net8, then enter .\tools-skills-net8.
5. Navigate to File | Open | File....
6. Browse to select the Northwind4SQLServer.sql file and then click the Open button.
7. In the toolbar, click Execute, and note the Command(s) completed successfully message.
8. In Object Explorer, expand the Northwind database, and then expand Tables.
9. Right-click Products, click Select Top 1000 Rows, and note the returned results.
10. Exit SQL Server Management Studio.

Creating the Northwind database for SQL Edge in Docker

To run a database script to create the Northwind sample database for SQL Edge in Docker:

1. In your preferred code editor, open the Northwind4AzureSQLedge.sql file.
2. Connect to SQL Edge in Docker using the following connection information:
   • Data Source aka server: tcp:127.0.0.1,1433
   • You must use SQL Server Authentication aka SQL Login i.e. you must supply a user name and password. Azure SQL Edge image has the sa user already created and you had to give it a strong password when you ran the container. We chose the password s3cret-Ninja.
   • Database: master or leave blank. We will create the Northwind database using a SQL script.
3. Execute the SQL script:
   • If you are using Visual Studio, right-click in the script, select Execute, and then wait to see the Command completed successfully message.
   • If you are using Code, right-click in the script, select Execute Query, select the Azure SQL Edge in Docker connection profile, and then wait to see the Commands completed successfully messages.
4. Refresh the data connection:
   • If you are using Visual Studio, then in Server Explorer, right-click Tables and select Refresh.
   • If you are using Code, then right-click the Azure SQL Edge in Docker connection profile and choose Refresh.
5. Expand Databases, expand Northwind, and then expand Tables.
6. Note that 13 tables have been created, for example, Categories, Customers, and Products. Also note that dozens of views and stored procedures have also been created.
7. Next, we will define an entity data model for the Northwind database as a pair of reusable class libraries.

Creating a class library for entity models using SQL Server

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

1. Using your preferred code editor, create a new project, as defined in the following list:
   • Project template: Class Library / classlib
   • Project file and folder: Northwind.EntityModels
   • Solution file and folder: Chapter01
2. In the Northwind.EntityModels 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>net8.0</TargetFramework>
       <ImplicitUsings>enable</ImplicitUsings>
       <Nullable>enable</Nullable>
       <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
     </PropertyGroup>
     <ItemGroup>
       <PackageReference
         Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.6" />
       <PackageReference
         Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.6">
         <PrivateAssets>all</PrivateAssets>
         <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
       </PackageReference> 
     </ItemGroup>
   </Project>
   

3. Delete the Class1.cs file.
4. Build the Northwind.EntityModels project.
5. Open a command prompt or terminal for the Northwind.EntityModels folder.
6. If you do not already have the dotnet-ef tool, then install the latest version, as shown in the following command:

   dotnet tool install --global dotnet-ef
   

7. Instead of installing, you can update using the following command:

   dotnet tool update --global dotnet-ef
   

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

   dotnet ef dbcontext scaffold "Data Source=.;Initial 
   atalog=Northwind;Integrated Security=true;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer --namespace Northwind.EntityModels --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 Northwind.EntityModels
• To use data annotations as well as the Fluent API: --data-annotations

9. Note that 28 classes were generated, from AlphabeticalListOfProduct.cs to Territory.cs.
10. At the top of the NorthwindContext.cs file, import the namespace for working with ADO.NET types, as shown in the following code:

    using Microsoft.Data.SqlClient; // To use SqlConnectionStringBuilder.
    

11. Modify the OnConfiguring method to dynamically set the connection string and set any sensitive parameters using environment variables, as shown in the following code:

    protected override void OnConfiguring(
      DbContextOptionsBuilder optionsBuilder)
    {
      // If not already configured by a client project. For example,
      // a client project could use AddNorthwindContext to override
      // the database connection string.
      if (!optionsBuilder.IsConfigured)
      {
        SqlConnectionStringBuilder builder = new();
        builder.DataSource = ".";
        builder.InitialCatalog = "Northwind";
        builder.TrustServerCertificate = true;
        builder.MultipleActiveResultSets = true;
        // If using Azure SQL Edge.
        // builder.DataSource = "tcp:127.0.0.1,1433";
        // Because we want to fail faster. Default is 15 seconds.
        builder.ConnectTimeout = 3;
        // If using Windows Integrated authentication.
        builder.IntegratedSecurity = true;
        // If using SQL Server authentication.
        // builder.UserID = Environment.GetEnvironmentVariable("MY_SQL_USR");
        // builder.Password = Environment.GetEnvironmentVariable("MY_SQL_PWD");
        optionsBuilder.UseSqlServer(builder.ConnectionString);
      }
    }
    

12. 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.DataContext
   • Solution file and folder: Chapter01
2. In the DataContext project, add a project reference to the EntityModels project, and add a package reference to the EF Core data provider for SQL Server, as shown in the following markup:

   <ItemGroup>
     <PackageReference
       Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.6" />
   </ItemGroup>
   <ItemGroup>
     <ProjectReference Include=
       "..\Northwind.EntityModels\Northwind.EntityModels.csproj" />
   </ItemGroup>
   

3. In the Northwind.DataContext project, delete the Class1.cs file.
4. Build the Northwind.DataContext project.
5. Move the NorthwindContext.cs file from the Northwind.EntityModels project/folder to the Northwind.DataContext project/folder.
6. In the Northwind.DataContext 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.Data.SqlClient; // SqlConnectionStringBuilder
   using Microsoft.EntityFrameworkCore; // UseSqlServer
   using Microsoft.Extensions.DependencyInjection; // IServiceCollection
   namespace Northwind.EntityModels;
   public static class NorthwindContextExtensions
   {
     /// <summary>
     /// Adds NorthwindContext to the specified IServiceCollection. Uses the SqlServer database provider.
     /// </summary>
     /// <param name="services">The service collection.</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 = null)
     {
       if (connectionString == null)
       {
         SqlConnectionStringBuilder builder = new();
         builder.DataSource = ".";
         builder.InitialCatalog = "Northwind";
         builder.TrustServerCertificate = true;
         builder.MultipleActiveResultSets = true;
         // If using Azure SQL Edge.
         // builder.DataSource = "tcp:127.0.0.1,1433";
         // Because we want to fail fast. Default is 15 seconds.
         builder.ConnectTimeout = 3;
         // If using Windows Integrated authentication.
         builder.IntegratedSecurity = true;
         // If using SQL Server authentication.
         // builder.UserID = Environment.GetEnvironmentVariable("MY_SQL_USR");
         // builder.Password = Environment.GetEnvironmentVariable("MY_SQL_PWD");
         connectionString = builder.ConnectionString;
       }
       services.AddDbContext<NorthwindContext>(options =>
       {
         options.UseSqlServer(connectionString);
         // Log to console when executing EF Core commands.
         options.LogTo(Console.WriteLine,
           new[] { Microsoft.EntityFrameworkCore
             .Diagnostics.RelationalEventId.CommandExecuting });
       },
       // Register with a transient lifetime to avoid concurrency
       // issues with Blazor Server projects.
       contextLifetime: ServiceLifetime.Transient,
       optionsLifetime: ServiceLifetime.Transient);
       return services;
     }
   }
   

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

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 code editor to add a new xUnit Test Project [C#] / xunit project named Northwind.Tests to the Chapter01 solution.
2. In Northwind.Tests.csproj, modify the configuration to treat warnings as errors and add an item group with a project reference to the Northwind.DataContext project, as shown in the following markup:

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

3. Build the Northwind.Tests project to build and restore project dependencies.
4. Rename the file UnitTest1.cs to NorthwindEntityModelsTests.cs (Visual Studio prompts you to rename the class when you rename the file).
5. In NorthwindEntityModelsTests.cs, if you are using Code, then manually rename the class to NorthwindEntityModelsTests.
6. In NorthwindEntityModelsTests.cs, modify the class to import the Northwind.EntityModels namespace and have some test methods for ensuring that the context class can connect, the provider is SQL Server, and the first product is named Chai, as shown in the following code:

   using Northwind.EntityModels; // To use NorthwindContext and Product.
   namespace Northwind.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);
       }
     }
   }
   

Running tests

Now we are ready to run the tests and see the results using either Visual Studio or Code.

Using Visual Studio:

1. In Visual Studio, in Solution Explorer, right-click the Northwind.Tests project, and then select Run Tests.
2. In Test Explorer, note that the results indicate that three tests ran, and all passed, as shown in Figure 1.3:

Figure 1.3: All the tests passed

Using Code:

1. In Code, in the Northwind.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 three tests ran, and all passed.