Setting up a database and projects for this book

We will set up a database and some initial projects for this book, but first, let’s review some important features that make managing projects easier.

Central Package Management

By default, with .NET SDK CLI and most code editor-created projects, if you need to reference a NuGet package, you add the reference to the package name and version directly in the project file, as shown in the following markup:

<ItemGroup>
  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer"
                    Version="10.0.0" />
  ...
</ItemGroup>

Central Package Management (CPM) is a feature that simplifies the management of NuGet package versions across multiple projects and solutions within a directory hierarchy. This is particularly useful for large solutions with many projects, where managing package versions individually can become cumbersome and error-prone.

Features and benefits of CPM

The key features and benefits of CPM include:

• Centralized Control: CPM allows you to define package versions in a single file, typically Directory.Packages.props, which is placed in the root directory of a directory hierarchy that contains all your solutions and projects. This file centralizes the version information for all NuGet packages used across the projects in your solutions.
• Consistency: Ensures consistent package versions across multiple projects. Having a single source of truth for package versions eliminates discrepancies that can occur when different projects specify different versions of the same package.
• Simplified Updates: Updating a package version in a large solution becomes straightforward. You update the version in the central file, and all projects referencing that package automatically use the updated version. This significantly reduces the maintenance overhead.
• Reduced Redundancy: Removes the need to specify package versions in individual project files (.csproj). This makes project files cleaner and easier to manage, as they no longer contain repetitive version information.

Defining project properties to reuse version numbers

Microsoft packages usually have the same number each month, like 10.0.2 in February, 10.0.3 in March. You can define properties at the top of your Directory.Packages.props file and then reference these properties throughout the file. This approach keeps package versions consistent and makes updates easier.

For example, in your Directory.Packages.props file. At the top of the file, within a <ProjectGroup> tag, define your custom property and then reference it for the package version, as shown in the following markup:

<Project>
  <PropertyGroup>
    <MicrosoftPackageVersion>10.0.2</MicrosoftPackageVersion>
  </PropertyGroup>
  <ItemGroup>
    <PackageVersion Include="Microsoft.EntityFrameworkCore"
                    Version="$(MicrosoftPackageVersion)" />
    <PackageVersion Include="Microsoft.Extensions.Logging"
                    Version="$(MicrosoftPackageVersion)" />
    <!-- Add more Microsoft packages as needed. -->
  </ItemGroup>
  <!-- Other packages with specific versions. -->
  <ItemGroup>
    <PackageVersion Include="Newtonsoft.Json" Version="13.0.4" />
  </ItemGroup>
</Project>

Note the following about the preceding configuration:

• Define a property: The line <MicrosoftPackageVersion>10.0.2</MicrosoftPackageVersion> defines the property. This value can be changed once at the top of the file, and all references will update automatically.
• Reference the property: Use the syntax $(PropertyName) to reference the defined property. All occurrences of $(MicrosoftPackageVersion) will resolve to the version number that you set.

When the monthly update rolls around, for example, from 10.0.2 to 10.0.3, you only have to update this number once.

You might want separate properties for related packages, such as:

<AspNetCorePackageVersion>10.0.3</AspNetCorePackageVersion>
<EFCorePackageVersion>10.0.3</EFCorePackageVersion>

This allows independent updates if packages diverge in their release cycles or versions later.

After making changes, at the terminal or command prompt, run the following command:

dotnet restore

This will verify the correctness of your references and quickly alert you if you’ve introduced errors. By adopting this pattern combined with CPM, you simplify version management, reduce redundancy, and make your projects easier to maintain over time.

Configuring Central Package Management in a .NET solution

Let’s set up CPM for a solution that we will use throughout the rest of the chapters in this book:

1. Create a new folder named tools-skills-net10 that we will use for all the code in this book. For example, on Windows, create a folder: C:\tools-skills-net10.
2. In the tools-skills-net10 folder, create a new file named Directory.Packages.props. You can do this at the command prompt or terminal using the following command:

   dotnet new packagesprops
   

3. In Directory.Packages.props, modify its contents, as shown in the following markup:

   <Project>
     <PropertyGroup>
    <ManagePackageVersionsCentrally>true</ManagePackageVersionsCentrally>
       <Net10>10.0.1</Net10>
       <Aspire>13.0.2</Aspire>
       <OpenTelemetry>1.14.0</OpenTelemetry>
       <Testcontainers>4.9.0</Testcontainers>
       <MSExt>10.1.0</MSExt>
     </PropertyGroup>
     <ItemGroup Label="Chapter 1 Introducing Tools and Skills for .NET">
       <PackageVersion Include="Microsoft.EntityFrameworkCore.Design"
                       Version="$(Net10)" />
       <PackageVersion Include="Microsoft.EntityFrameworkCore.SqlServer"
                       Version="$(Net10)" />
       <!--For unit testing.-->
       <PackageVersion Include="coverlet.collector" Version="6.0.4" />
       <PackageVersion Include="Microsoft.NET.Test.Sdk" Version="18.0.1" />
       <PackageVersion Include="xunit" Version="2.9.3" />
       <PackageVersion Include="xunit.runner.visualstudio" Version="3.1.5" />
     </ItemGroup>
     <ItemGroup Label="Chapter 5 Logging, Tracing, and Metrics for Observability">
       <!--For logging to console.-->
       <PackageVersion Include="Microsoft.Extensions.Logging.Console"
                       Version="$(Net10)" />
     
       <!--For generating OpenAPI documentation.-->
       <PackageVersion Include="Microsoft.AspNetCore.OpenApi" Version="$(Net10)" />
     
       <!--For performance counters.-->
        <PackageVersion Include="System.Diagnostics.PerformanceCounter"
                          Version="$(Net10)" />
     
       <!--For OpenTelemetry.-->
       <PackageVersion Include="OpenTelemetry.Exporter.Console"
                       Version="$(OpenTelemetry)" />
       <PackageVersion Include="OpenTelemetry.Extensions.Hosting"
                       Version="$(OpenTelemetry)" />
       <PackageVersion Include="OpenTelemetry.Instrumentation.AspNetCore"
                       Version="$(OpenTelemetry)" />
       <PackageVersion Include="OpenTelemetry.Instrumentation.EntityFrameworkCore"
                       Version="1.14.0-beta.2" />
       <PackageVersion Include="OpenTelemetry.Instrumentation.SqlClient"
                       Version="1.14.0-beta.1" />
     </ItemGroup>
     <ItemGroup Label="Chapter 7 Observing and Modifying Code Execution Dynamically">
       <PackageVersion Include="Microsoft.CodeAnalysis.Analyzers"
                       Version="3.11.0" />
       <PackageVersion Include="Microsoft.CodeAnalysis.CSharp"
                       Version="5.0.0" />
     </ItemGroup>
     <ItemGroup Label="Chapter 9 Multitasking and Concurrency">
       <PackageVersion Include="Microsoft.Data.SqlClient" Version="6.1.3" />
     </ItemGroup>
     <ItemGroup Label="Chapter 10 Dependency Injection, Containers, and Service Lifetime">
       <PackageVersion Include="Microsoft.Extensions.Hosting"
                       Version="$(Net10)" />
     </ItemGroup>
     <ItemGroup Label="Chapter 11 Unit Testing and Mocking">
       <PackageVersion Include="NSubstitute" Version="5.3.0" />
       <PackageVersion Include="Bogus" Version="35.6.5" />
       <!--FluentAssertions restricted its license with 8.0 and later.-->
       <!--Minimum 7.2.0 (inclusive) up to maximum 8.0.0 (exclusive).-->
       <PackageVersion Include="FluentAssertions" Version="[7.2.0,8.0.0)" />
     </ItemGroup>
     <ItemGroup Label="Chapter 13 Benchmarking Performance, Load, and Stress Testing">
       <PackageVersion Include="BenchmarkDotNet" Version="0.15.8" />
       <PackageVersion Include="NBomber" Version="6.1.2" />
       <PackageVersion Include="NBomber.Http" Version="6.1.0" />
     </ItemGroup>
     <ItemGroup Label="Chapter 14 Functional and End-to-End Testing of Websites and Services">
       <PackageVersion Include="Microsoft.Playwright" Version="1.57.0" />
       <PackageVersion Include="Microsoft.AspNetCore.Mvc.Testing"
                       Version="$(Net10" />
     </ItemGroup>
     <ItemGroup Label="Chapter 15 Containerization Using Docker">
       <PackageVersion Include="Spectre.Console" Version="0.54.0" />
       <PackageVersion Include="Testcontainers"
                       Version="$(Testcontainers)" />
       <PackageVersion Include="Testcontainers.MsSql"
                       Version="$(Testcontainers)" />
       <PackageVersion Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore"
                       Version="$(Net10)" />
       <PackageVersion Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore"
                       Version="$(Net10)" />
       <PackageVersion Include="Microsoft.AspNetCore.Identity.UI"
                       Version="$(Net10)" />
       <PackageVersion Include="Microsoft.EntityFrameworkCore.Tools"
                       Version="$(Net10)" />
       <PackageVersion Include=
         "Microsoft.VisualStudio.Azure.Containers.Tools.Targets"
         Version="1.22.1" />
     </ItemGroup>
     <ItemGroup Label="Chapter 16 Cloud-Native Development Using Aspire">
       <PackageVersion Include="Aspire.Hosting.AppHost" Version="$(Aspire)" />
       <PackageVersion Include="Aspire.Hosting.Redis" Version="$(Aspire)" />
       <PackageVersion Include="Microsoft.Extensions.Http.Resilience"
                       Version="$(MSExt)" />
       <PackageVersion Include="Microsoft.Extensions.ServiceDiscovery"
                       Version="$(MSExt)" />
       <PackageVersion Include="Aspire.Hosting.Testing"
                       Version="$(Aspire)" />
       <PackageVersion Include="Aspire.StackExchange.Redis.OutputCaching"
                       Version="$(Aspire)" />
       <PackageVersion Include="OpenTelemetry.Exporter.OpenTelemetryProtocol"
                       Version="$(OpenTelemetry)" />
       <PackageVersion Include="OpenTelemetry.Instrumentation.Http"
                       Version="$(OpenTelemetry)" />
       <PackageVersion Include="OpenTelemetry.Instrumentation.Runtime"
                       Version="$(OpenTelemetry)" />
       <PackageVersion Include="Aspire.Hosting.PostgreSQL"
                       Version="$(Aspire)" />
       <PackageVersion Include="Aspire.Hosting.SqlServer"
                       Version="$(Aspire)" />
       <PackageVersion Include="Aspire.Hosting.RabbitMQ"
                       Version="$(Aspire)" />
       <PackageVersion Include="Aspire.Hosting.Azure.CognitiveServices"
                       Version="$(Aspire)" />
     </ItemGroup>
   </Project>
   

   Warning! The <ManagePackageVersionsCentrally> element and its true value must all go on one line. Also, you cannot use floating wildcard version numbers like 10.0-* as you can in an individual project. Wildcards are useful to automatically get the latest patch version, but with CPM, you must manually update the versions.

For any projects that we add underneath the folder containing this file, we can reference the packages without explicitly specifying the version, as shown in the following markup:

<ItemGroup>
  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Design" />
</ItemGroup>

CPM good practice

You should regularly review and update the package versions in the Directory.Packages.props file to ensure that you are using the latest stable releases with important bug fixes and performance improvements.

For example, throughout 2026, there are likely to be new versions each month, so you can go to the NuGet page for each of the packages. You can then update the versions in the Directory.Packages.props file, for example, as shown in the following markup:

<PackageVersion Include="Microsoft.EntityFrameworkCore.SqlServer"
                Version="10.0.3" />

Before updating package versions, check for any breaking changes in the release notes of the packages. Test your solution thoroughly after updating to ensure compatibility.

Educate your team and document the purpose and usage of the Directory.Packages.props file to ensure everyone understands how to manage package versions centrally.

You can override an individual package version by using the VersionOverride attribute on a <PackageReference /> element in the specific project file. For example, to force the SQL Server for EF Core package to use version 10.0.0, as shown in the following markup:

<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer"
                  VersionOverride="10.0.0" />

This can be useful if a newer version introduces a regression bug.

Package Source Mapping

If you use CPM and have more than one package source configured for your code editor, as shown in Figure 1.2, then you will see NuGet Warning NU1507, as shown in the following output:

There are 2 package sources defined in your configuration. When using central package management, please map your package sources with package source mapping (https://aka.ms/nuget-package-source-mapping) or specify a single package source. The following sources are defined: https://api.nuget.org/v3/index.json and  https://contoso.myget.org/F/development/.

Specifically in Figure 1.2, this warning occurs because we have both the default package source (https://api.nuget.org/v3/index.json) and a custom package source configured:

Figure 1.2: Visual Studio with two NuGet package sources configured

Understanding Package Source Mapping

Package Source Mapping (PSM) can help safeguard your software supply chain if you use a mix of public and private package sources, as in the preceding example.

By default, when it needs to download a package, NuGet will search all configured package sources. When a package exists on multiple sources, it may not be deterministic which source the package will be downloaded from. With PSM, you can filter, per package, which source(s) NuGet will search.

PSM is supported by Visual Studio, .NET 6 and later, and NuGet 6 and later. Older tooling will ignore the PSM configuration.

Now let’s see how to enable PSM.

Enabling Package Source Mapping

To enable PSM, you must have a nuget.config file.

There are two parts: defining package sources, and mapping package sources to packages. All requested packages must map to one or more sources by matching a defined package pattern. In other words, once you have defined a packageSourceMapping element, you must explicitly define which sources every package, including transitive packages, will be restored from.

For example, if you want most packages to be sourced from the default nuget.org site, but there are some private packages that must be sourced from your organization’s website, you would define the two package sources, and set the mapping (assuming all your private packages are named Northwind.Something), as shown in the following markup:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <!-- `clear` ensures no additional sources are inherited from another config file. -->
  <packageSources>
    <clear />
    <!-- `key` can be any identifier for your source. -->
    <add key="nuget.org" value="https://api.nuget.org/v3/index.json" />
    <add key="Northwind" value="https://northwind.com/packages" />
  </packageSources>
  <!-- All packages sourced from nuget.org except Northwind packages. -->
  <packageSourceMapping>
    <!-- key value for <packageSource> should match key values from <packageSources> element -->
    <packageSource key="nuget.org">
      <package pattern="*" />
    </packageSource>
    <packageSource key="Northwind">
      <package pattern="Northwind.*" />
    </packageSource>
  </packageSourceMapping>
</configuration>

Let’s create a nuget.config for all the solutions and projects in this book that will use nuget.org as the source for all packages:

1. In the tools-skills-net10 folder, create a new file named nuget.config.
2. In nuget.config, modify its contents, as shown in the following markup:

   <?xml version="1.0" encoding="utf-8"?>
   <configuration>
     <!-- `clear` ensures no additional sources are inherited from another config file. -->
     <packageSources>
       <clear />
       <!-- `key` can be any identifier for your source. -->
       <add key="nuget.org" value="https://api.nuget.org/v3/index.json" />
     </packageSources>
     <!-- All packages sourced from nuget.org. -->
     <packageSourceMapping>
       <!-- key value for <packageSource> should match key values from <packageSources> element -->
       <packageSource key="nuget.org">
         <package pattern="*" />
       </packageSource>
     </packageSourceMapping>
   </configuration>
   

3. Save changes.

   You can learn more about nuget.config at the following link: https://learn.microsoft.com/en-us/nuget/reference/nuget-config-file.

   You can learn more about PSM at the following link: https://learn.microsoft.com/en-us/nuget/consume-packages/package-source-mapping.

Treating warnings as errors

By default, compiler warnings may appear if there are potential problems with your code when you first build a project, but they do not prevent compilation and they are hidden if you rebuild. Warnings are given for a reason, so ignoring warnings encourages poor development practices.

Some developers would prefer to be forced to fix warnings, so .NET provides a project setting to do this, as shown highlighted in the following markup:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
    <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
  </PropertyGroup>

I have enabled the option to treat warnings as errors in (almost) all the solutions in the GitHub repository.

If you find that you get too many errors after enabling this, you can disable specific warnings by using the <WarningsNotAsErrors> element with a comma-separated list of warning codes, as shown in the following markup:

<WarningsNotAsErrors>0219,CS8981</WarningsNotAsErrors>

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 refer to the diagram in Figure 1.3 as we write code and queries throughout this book:

Figure 1.3: 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 detailed instructions in Appendix B, Setting Up Your Development Environment.

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 SQL Server in Docker. You can skip this if you already have this set up.

Northwind database SQL scripts

The script for SQL Server creates 13 tables as well as related views and stored procedures. The SQL scripts are found at the following link:

https://github.com/markjprice/tools-skills-net10/tree/main/scripts/sql-scripts.

I recommend that in your tools-skills-net10 folder, you create a sql-scripts folder and copy all the SQL scripts to that local folder.

There are multiple SQL scripts to choose from, as described in the following list:

• Northwind4SqlServerContainer.sql script: To use SQL Server on a local computer in a container system like Docker. The script creates the Northwind database. It does not drop the database if it already exists because the Docker container should be empty anyway, as a fresh one can be spun up each time. Instructions to install Docker and set up a SQL Server image and container are in the next section of this book. This is my recommendation for using SQL Server in this book.
• Northwind4SqlServerLocal.sql script: To use SQL Server on a local Windows or Linux computer. The script checks if the Northwind database already exists and, if necessary, drops (deletes) it before creating it. Instructions to install SQL Server Developer Edition (free) on your local Windows computer can be found in the GitHub repository for this book at the following link: https://github.com/markjprice/tools-skills-net10/blob/main/docs/sql-server/README.md.
• Northwind4SqlServerCloud.sql script: To use SQL Server with an Azure SQL Database resource created in the Azure cloud. You will need an Azure account; these resources cost money as long as they exist! The script does not drop or create the Northwind database because you should manually create the Northwind database using the Azure portal user interface. The script only creates the database objects, including the table structure and data.

Before you can execute any of these SQL scripts, you need a SQL Server instance. My recommendation is to use Docker and a container, so that’s what we will cover in the next section. If you prefer a local or cloud SQL Server, then you can skip this next section.

Installing Docker and an SQL Server container image

Docker provides a consistent environment across development, testing, and production, minimizing the “it works on my machine” issue. Docker containers are more lightweight than traditional virtual machines, making them faster to start up and less resource-intensive.

Docker containers can run on any system with Docker installed, making it easy to move databases between environments or across different machines. You can quickly spin up a SQL database container with a single command, making setup faster and more reproducible. Each database instance runs in its own container, ensuring that it is isolated from other applications and databases on the same machine.

You can install Docker on any operating system and use a container that has SQL Server installed. For personal, educational, and small business use, Docker Desktop is free to use. It includes the full set of Docker features, including container management and orchestration. The Docker CLI and Docker Engine are open source and free to use, allowing developers to build, run, and manage containers.

Docker also has paid tiers that offer additional features, such as enhanced security, collaboration tools, more granular access control, priority support, and higher rate limits on Docker Hub image pull.

The Docker image we will use has SQL Server 2025 hosted on Ubuntu 22.04. It is supported with Docker Engine 1.8 or later.

Let’s install Docker and set up the SQL image and container now:

1. Install Docker Desktop from the following link: https://docs.docker.com/engine/install/.
2. Start Docker Desktop, which could take a few minutes on the initial start, as shown in Figure 1.4:

Figure 1.4: Docker Desktop on Windows

3. At the command prompt or terminal, pull down the latest container image for SQL Server 2025, as shown in the following command:

   docker pull mcr.microsoft.com/mssql/server:2025-latest
   

4. Wait for the image as it downloads, and then note the results, as shown in the following output:

   2025-latest: Pulling from mssql/server
   a7f551132cc7: Pull complete
   d39c64e0c073: Pull complete
   04a0776f5c78: Pull complete
   Digest: sha256:e2e5bcfe395924ff49694542191d3aefe86b6b3bd6c024f9ea01bf5a8856c56e
   Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2025-latest
   

Running the SQL Server container image

You can create a container from the image and run it in a single docker run command with the following options:

• --cap-add SYS_PTRACE: This grants the container the SYS_PTRACE capability allows debugging tools (like strace or certain profilers) to attach to processes within the container. Microsoft recommends this for enabling debugging or diagnostic tools inside the container, but it’s not strictly necessary for normal SQL Server operation.
• -e 'ACCEPT_EULA=1' or -e "ACCEPT_EULA=1": This sets an environment variable to accept the SQL Server End User License Agreement (EULA). If you don’t provide this, the container will exit immediately with a message saying you must accept the EULA.
• -e 'MSSQL_SA_PASSWORD=s3cret-Ninja' or -e "MSSQL_SA_PASSWORD=s3cret-Ninja": This sets an environment variable to set the SQL Server sa (system administrator) account’s password. The password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, digits, and symbols. Otherwise, the container cannot set up the SQL Server engine and will fail. s3cret-Ninja satisfies those rules (lowercase, number, hyphen, uppercase), but feel free to use your own password if you wish.
• -p 1433:1433: This maps a port from the container to the host. 1433 is the default port for SQL Server. This allows applications on the host to connect to SQL Server inside the container as if it were running natively.
• --name nw-container: This gives the container a custom name. This is optional, but if you don’t set a name, a random one will be assigned for you, like frosty_mirzakhani.
• -d: This runs the container in detached mode (in the background). Without it, Docker would run the container in the foreground, tying up your terminal or command prompt window.
• mcr.microsoft.com/mssql/server:2025-latest: This specifies the image to run. mcr.microsoft.com is Microsoft’s official container registry. mssql/server is the SQL Server on Linux container image. 2025-latest is the tag for the latest build of SQL Server 2025.

Now that you understand what you are about to do, we can run the image:

1. At the command prompt or terminal, run the container image for SQL Server with a strong password, and name the container nw-container, as shown in the following command:

   docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=s3cret-Ninja' -p 1433:1433 --name nw-container -d mcr.microsoft.com/mssql/server:2025-latest
   

2. If your operating system’s firewall blocks access, then allow access.
3. In Docker Desktop, in the Containers section, confirm that the image is running, as shown in Figure 1.5:

Figure 1.5: SQL Server container running in Docker Desktop on Windows

You might assume that the link in the Port(s) column is clickable and will navigate to a working website. But the container image only has SQL Server in it. SQL Server is listening on that port and can be connected to using a TCP address, not an HTTP address, so Docker is misleading you! There is no web server listening on port 1433, so a web browser that makes a request to http://localhost:1433 will get a This page isn’t working error. This is expected behavior because a database server is not a web server. Many containers in Docker do host a web server, and in those scenarios, having a convenient clickable link is useful. But Docker has no idea which containers have web servers and which do not. All it knows is what ports are mapped from internal ports to external ports. It is up to the developer to know if those links are useful.

4. At the command prompt or terminal, ask Docker to list all containers, both running and stopped, as shown in the following command:

   docker ps -a
   

5. Note the container STATUS is Up 53 seconds and listening externally on port 1433, which is mapped to its internal port 1433, as shown highlighted in the following output:

   CONTAINER ID   IMAGE                              COMMAND                  CREATED         STATUS         PORTS                              NAMES
   183f02e84b2a   mcr.microsoft.com/ mssql/server:2025-latest   "/opt/mssql/bin/perm…"   8 minutes ago   Up 53 seconds   1401/tcp, 0.0.0.0:1433->1433/tcp   nw-container
   

Running a container using the user interface

If you successfully ran the SQL Server container, then you can skip this section and continue with the next section, titled Connecting to SQL Server in a Docker container.

If entering a command at the prompt or terminal fails for you, try following these steps to use the user interface:

1. In Docker Desktop, navigate to the Images tab.
2. In the mcr.microsoft.com/mssql/server row, in the Actions column, click the Run button.
3. In the Run a new container dialog box, expand Optional settings, and complete the configuration, as shown in Figure 1.6 and in the following items:
   • Container name: nw-container, or leave blank to use a random name.
   • Ports: Enter 1433 to map to :1433/tcp.
   • Volumes: Leave empty.
   • Environment variables (click + to add a second one):
     • Enter ACCEPT_EULA with the value Y (or 1).
     • Enter MSSQL_SA_PASSWORD with the value s3cret-Ninja.
4. Click Run.

Figure 1.6: Running a container for SQL Server with the user interface

Connecting to SQL Server in a Docker container

Use your preferred database tool to connect to SQL Server in the Docker container. Some common database tools are shown in the following list:

• Windows only:
  • SQL Server Management Studio (SSMS): The most popular and comprehensive tool for managing SQL Server databases. Free to download from Microsoft.
  • SQL Server Data Tools (SSDT): Integrated into Visual Studio and free to use, SSDT provides database development tools for designing, deploying, and managing SQL Server databases.
• Cross-platform for Windows, macOS, Linux:
  • VS Code’s MS SQL extension: Query execution, IntelliSense, database browsing, and connection to SQL Server databases.

Some notes about the database connection string for SQL Server in a container:

• Data Source, a.k.a. Server Name: tcp:127.0.0.1,1433
• Authentication: You must use SQL Server Authentication, a.k.a. SQL Login. That is, you must supply a username and password. The SQL Server 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.
• You must select the Trust server certificate checkbox.
• Optionally, you might want to save your password for future use.
• Initial Catalog, a.k.a. database: master or leave blank. (We will create the Northwind database using a SQL script, so we do not specify that as the database name yet.)

Warning! If you already have SQL Server installed locally, and its services are running, then it will be listening to port 1433, and it will take priority over any Docker-hosted SQL Server services that are also trying to listen on port 1433. You will need to stop the local SQL Server before being able to connect to any Docker-hosted SQL Server services. You can do this using Windows Services: in the Services (Local) list, right-click SQL Server (MSSQLSERVER) and choose Stop. (This can take a few minutes, so be patient.) You can also right-click and choose Properties and then set Startup type to Manual, as shown in Figure 1.7 (it defaults to Automatic, so if you restart Windows, it will be running again). Or change the port number(s) for either the local or Docker-hosted SQL Server services so that they do not conflict.

Figure 1.7: SQL Server service properties

Connecting from Visual Studio

To connect to SQL Server using Visual Studio:

1. In Visual Studio, navigate to View | Server Explorer.
2. In the Server Explorer mini-toolbar, click the Connect to Database... button.
3. If prompted to Change Data Source, then choose Microsoft SQL Server.
4. Enter the connection details, as shown in Figure 1.8:

Figure 1.8: Connecting to your SQL Server in a container from Visual Studio

Connecting from VS Code

To connect to SQL Server in a container using VS Code, follow these steps:

1. In VS Code, navigate to the SQL Server extension. Note that the mssql extension might take a few minutes to initialize the first time.
2. In the SQL extension, click Add Connection....
3. In the Connect to Database pane, enter the connection details, as shown in Figure 1.9:
   • Profile Name: SQL Server in Container
   • Connection Group: <Default>
   • Input type: Parameters
   • Server name: tcp:127.0.0.1,1433
   • Trust server certificate: Selected
   • Authentication type: SQL Login
   • User name: sa
   • Password: s3cret-Ninja
   • Save Password: Cleared (or selected for convenience during learning)
   • Database name: master or leave blank (we will create the Northwind database using a SQL script, so we do not specify that as the database name yet)
   • Encrypt: Mandatory

Figure 1.9: Connecting to your SQL Server in a container from VS Code

4. Click the Connect button and note the success notification message.

Creating the Northwind database using a SQL script

Now you can use your preferred code editor (or database tool) to execute the SQL script to create the Northwind database in SQL Server in a container:

1. Open the Northwind4SqlServerContainer.sql file. Note that this file does not know about the Server Explorer connection to the SQL Server database.
2. Connect the file to the SQL Server database. For example, in Visual Studio, right-click in the script file, navigate to Connection | Connect..., and then fill in the dialog box as before.
3. Execute the SQL script:
   • If you are using Visual Studio, right-click in the script file, select Execute, and then wait to see the Command completed successfully message.
   • If you are using VS Code, right-click in the script file, select Execute Query, select the SQL Server in a Container connection profile, and then wait to see the Commands completed successfully message.
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 VS Code, then right-click the SQL Server in a Container 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, as shown in Figure 1.10:

Figure 1.10: Northwind database created by SQL script in Visual Studio Server Explorer

You now have a running instance of SQL Server containing the Northwind database that you can connect to from your .NET projects.

You will want to keep the container while you work through all the chapters in this book. You can stop and start the container whenever you want, and the database will persist. Eventually, once you have finished this book, you might want to delete the container. This will also delete the database, so if you recreate the container, you will need to rerun the SQL script to recreate the Northwind database.

Removing Docker resources

When you have completed all the chapters in the book, or you plan to use a local SQL Server or Azure SQL Database in the cloud instead of a SQL Server container, and you want to remove all the Docker resources that it uses, then either use the Docker Desktop user interface or follow these steps at the command prompt or terminal:

1. At the command prompt or terminal, stop the nw-container container, as shown in the following command:

   docker stop nw-container
   

2. At the command prompt or terminal, remove the nw-container container, as shown in the following command:

   docker rm nw-container
   

3. At the command prompt or terminal, remove the image to release its disk space, as shown in the following command:

   docker rmi mcr.microsoft.com/mssql/server:2025-latest
   

Setting up the EF Core CLI tool

The .NET CLI tool named dotnet can be extended with capabilities useful for working with EF Core. It can perform design-time tasks like creating and applying migrations from an older model to a newer model and generating code for a model from an existing database.

The dotnet-ef command-line tool is not automatically installed. You must install this package as either a global or local tool. If you have already installed an older version of the tool, then you should update it to the latest version:

1. At a command prompt or terminal, check if you have already installed dotnet-ef as a global tool, as shown in the following command:

   dotnet tool list --global
   

2. Check in the list if an older version of the tool has been installed, like the one for .NET 9, as shown in the following output:

   Package Id      Version     Commands
   -------------------------------------
   dotnet-ef       9.0.0       dotnet-ef
   

3. If an old version is installed, then update the tool, as shown in the following command:

   dotnet tool update --global dotnet-ef
   

4. If it is not already installed, then install the latest version, as shown in the following command:

   dotnet tool install --global dotnet-ef
   

If necessary, follow any OS-specific instructions to add the dotnet tools directory to your PATH environment variable, as described in the output of installing the dotnet-ef tool.

By default, the latest general availability (GA) release of .NET will be used to install the tool. To explicitly set a version, for example, to use a preview, add the --version switch. Or, to update to the latest .NET 11 preview or release candidate version (which will be available from February 2026 to October 2026), use the following command with a version wildcard:

dotnet tool update --global dotnet-ef --version 11.0-*

Once the .NET 11 GA release happens in November 2026, you can just use the command without the --version switch to upgrade.

You can also remove the tool, as shown in the following command:

dotnet tool uninstall --global dotnet-ef

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

     Good practice: Make sure you create your solutions under the tools-skills-net10 folder which contains the Directory.Packages.props and nuget.config files to benefit from central package management.

2. In the Northwind.EntityModels project, treat warnings as errors, and add package references (without version numbers) 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>net10.0</TargetFramework>
       <ImplicitUsings>enable</ImplicitUsings>
       <Nullable>enable</Nullable>
       <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
     </PropertyGroup>
     <ItemGroup>
       <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" />
       <PackageReference Include="Microsoft.EntityFrameworkCore.Design">
         <PrivateAssets>all</PrivateAssets>
         <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
       </PackageReference>
     </ItemGroup>
   </Project>
   

   If you are unfamiliar with how packages like Microsoft.EntityFrameworkCore.Design can manage their assets, then you can learn more at the following link: https://learn.microsoft.com/en-us/nuget/consume-packages/package-references-in-project-files#controlling-dependency-assets.

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. 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;"
• Alternative connection string for SQL Server in a container: "Data Source=127.0.0.1,1433;Initial Catalog=Northwind;User ID=sa;Password=s3cret-Ninja;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

Reviewing and modifying the generated code

Now we can review the code that was generated by the dotnet-ef tool:

1. Note that 28 classes were generated, from AlphabeticalListOfProduct.cs to Territory.cs.
2. In NorthwindContext.cs, note that the second constructor can have options passed as a parameter, which allows us to override the default database connection string in any projects, such as websites, that need to work with the Northwind database, as shown in the following code:

   public NorthwindContext(
     DbContextOptions<NorthwindContext> options)
     : base(options)
   {
   }
   

3. In NorthwindContext.cs, both constructors give dozens of warnings, one for each of its DbSet<T> properties that represent tables and views. This is because they are marked as not nullable, and the compiler does not know that EF Core will automatically instantiate them all, so they will never actually be null. We can hide these warnings by disabling warning code CS8618 just for those two constructors, as shown in the following code:

   public partial class NorthwindContext : DbContext
   {
   #pragma warning disable CS8618
     public NorthwindContext()
   #pragma warning restore CS8618
     {
     }
   #pragma warning disable CS8618
     public NorthwindContext(DbContextOptions<NorthwindContext> options)
   #pragma warning restore CS8618
         : base(options)
     {
     }
   

4. 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.
   

5. 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 = "tcp:127.0.0.1,1433"; // SQL Server in container.
       builder.InitialCatalog = "Northwind";
       builder.TrustServerCertificate = true;
       builder.MultipleActiveResultSets = true;
       // 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);
     }
   }
   

6. In Customer.cs, the dotnet-ef tool identified that the CustomerId column is the primary key and it is limited to a maximum of five characters, so it decorated the property with [StringLength(5)]. We also want the values to always be uppercase and always exactly five characters. So, add a regular expression to validate its primary key value to only allow five 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" />
   </ItemGroup>
   <ItemGroup>
     <ProjectReference Include=
       "..\Northwind.EntityModels\Northwind.EntityModels.csproj" />
   </ItemGroup>
   

   After February 2026, you can try out previews of EF Core 11 by specifying VersionOverride="11.0-*". The target framework for your project should continue to use net10.0. By using a wildcard, you will automatically download the latest monthly preview when you restore the packages for the project. Once the EF Core 11 GA version is released in November 2026, change the package version to 11.0.0 or later. After February 2027, you will be able to do similar with EF Core 12 (use a package version of 12.0-*), but that will likely require a project targeting net12.0, so you will have to install a preview version of .NET 12 SDK as well.

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. You must move this file because if you copy it, there will be two database context classes with the same name and that will cause issues.

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 = "tcp:127.0.0.1,1433"; // SQL Server in container.
         builder.InitialCatalog = "Northwind";
         builder.TrustServerCertificate = true;
         builder.MultipleActiveResultSets = true;
         // 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.

Setting the user and password for SQL Server authentication

If you are using SQL Server authentication (i.e., you must supply a user and password), then complete the following steps:

1. In the Northwind.DataContext project, note the statements that set UserId and Password, as shown in the following code:

   // SQL Server authentication.
   builder.UserId = Environment.GetEnvironmentVariable("MY_SQL_USR");
   builder.Password = Environment.GetEnvironmentVariable("MY_SQL_PWD");
   

2. Set the two environment variables at the command prompt or terminal, as shown in the following commands:
   • On Windows:

   setx MY_SQL_USR <your_user_name>
   setx MY_SQL_PWD <your_password>
   

   • On macOS and Linux:

   export MY_SQL_USR=<your_user_name>
   export MY_SQL_PWD=<your_password>
   

3. You must restart any command prompts, terminal windows, and applications like Visual Studio for this change to take effect.

Registering dependency services

You can register dependency services with different lifetimes, as shown in the following list:

• Transient: These services are created each time they’re requested. Transient services should be lightweight and stateless.
• Scoped: These services are created once per client request and are disposed of; then the response returns to the client.
• Singleton: These services are usually created the first time they are requested and then shared, although you can provide an instance at the time of registration, too.

In this book, you will use all three types of lifetime.

By default, a DbContext class is registered using the Scope lifetime, meaning that multiple threads can share the same instance. But DbContext does not support multiple threads. If more than one thread attempts to use the same NorthwindContext class instance at the same time, then you will see the following runtime exception thrown: A second operation started on this context before a previous operation completed. This is usually caused by different threads using the same instance of a DbContext. However, instance members are not guaranteed to be thread-safe.

This happens in Blazor projects with components set to run on the server side because, whenever interactions on the client side happen, a SignalR call is made back to the server, where a single instance of the database context is shared between multiple clients. This issue does not occur if a component is set to run on the client side.

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

xUnit is a popular unit testing framework for .NET applications. It was created by the original inventor of NUnit and is designed to be more modern, extensible, and aligned with .NET development practices.

Several benefits of using xUnit are shown in the following list:

• xUnit is open-source and has a strong community and active development team behind it. This makes it more likely that it will stay up to date with the latest .NET features and best practices. xUnit benefits from a large and active community, which means many tutorials, guides, and third-party extensions are available for it.
• xUnit uses a more simplified and extensible approach compared to older frameworks. It encourages the use of custom test patterns and less reliance on setup and teardown methods, leading to cleaner test code.
• Tests in xUnit are configured using .NET attributes, which makes the test code easy to read and understand. It uses [Fact] for standard test cases and [Theory] with [InlineData], [ClassData], or [MemberData] for parameterized tests, enabling data-driven testing. This makes it easier to cover many input scenarios with the same test method, enhancing test thoroughness while minimizing effort.
• xUnit includes an assertion library that allows for a wide variety of assertions out of the box, making it easier to test a wide range of conditions without having to write custom test code. It can also be extended with popular assertion libraries, like FluentAssertions, that allow you to articulate test expectations with human-readable reasons.
• By default, xUnit supports parallel test execution within the same test collection, which can significantly reduce the time it takes to run large test suites. This is particularly beneficial in continuous integration environments where speed is critical. However, if you run your tests in a memory-limited Virtual Private Server (VPS), then that impacts how much data the server can handle at any given time and how many applications or processes it can run concurrently. In this scenario, you might want to disable parallel test execution. Memory-limited VPS instances are typically used as cheap testing environments.
• xUnit offers precise control over the test lifecycle with setup and teardown commands through the use of the constructor and destructor patterns and the IDisposable interface, as well as with the [BeforeAfterTestAttribute] for more granular control.

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>
   

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

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 VS 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 DatabaseConnectTest()
     {
       using NorthwindContext db = new();
       Assert.True(db.Database.CanConnect());
     }
     [Fact]
     public void CategoryCountTest()
     {
       using NorthwindContext db = new();
       int expected = 8;
       int actual = db.Categories.Count();
       Assert.Equal(expected, actual);
     }
     [Fact]
     public void ProductId1IsChaiTest()
     {
       using NorthwindContext db = new();
       string expected = "Chai";
       Product? product = db?.Products?.Single(p => p.ProductId == 1);
       string actual = product?.ProductName ?? string.Empty;
       Assert.Equal(expected, actual);
     }
   }
   

Running tests

Now we are ready to run the tests and see the results using either Visual Studio or VS 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.11:

Figure 1.11: All the tests passed

Using VS Code:

1. In VS 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.