.NET Aspire with VS Code, SQLite & SQL Server

In this Tutorial, we will explore .NET Aspire. At first, we will use it with SQLite. Thereafter, we will modify the solution so that it uses a SQL Server Docker image instead of SQLite. All this is done in a terminal window with the help of VS Code. The objective is to serve those who do not use the higher end Visual Studio 2022 IDE.

Start source code: https://github.com/medhatelmasry/SoccerFIFA
End source code: https://github.com/medhatelmasry/SoccerAspire
Companion video: https://youtu.be/FDF04Lner5k

Prerequisites

In order to continue with this tutorial, you will need the following:

• .NET 9.0
• dotnet-ef tool - If you do not have this tool, you can install it with the terminal window command "dotnet tool install --global dotnet-ef"
• Visual Studio Code
• C# Dev Kit extension for Visual Studio Code
• Docker Desktop
• Azure Data Studio

.NET Aspire Setup

In any terminal window folder, run the following command before you install .NET Aspire:

dotnet workload update 

To install the .NET Aspire workload from the .NET CLI, execute this command:

dotnet workload install aspire

Check your version of .NET Aspire, with this command:

dotnet workload list

Startup Application

We will start with a .NET 9.0 application that involves a Minimal API backend and a Blazor frontend. Clone the code from this GitHub site with:

git clone https://github.com/medhatelmasry/SoccerFIFA

To get a good sense of what the application does, follow these steps:

1) Inside the WebApiFIFA folder, run the following command in a terminal window:

dotnet watch

Try out the GET /api/games endpoint, you should see the following output:

2) Next, let us try the frontend. Inside a terminal window in the BlazorFIFA folder, run this command:

dotnet watch

We know that the application works. However, it is a pain to have to start both projects to get the solution to work. This is where .NET Aspire will come to the rescue.

Converting solution to .NET Aspire

Close both terminal windows by hitting CTRL C in each.

To add the basic .NET Aspire projects to our solution, run the following command inside the root SoccerFIFA folder:

dotnet new aspire --force

This adds these artifacts:

• SoccerFIFA.sln file (this replaces the previous .sln file because of the --force switch)
• SoccerFIFA.AppHost folder
• SoccerFIFA.ServiceDefaults folder

We will add our previous API & Blazor projects to the newly created .sln file by executing the following commands inside the root SoccerFIFA folder:

dotnet sln add ./BlazorFIFA/BlazorFIFA.csproj

dotnet sln add ./WebApiFIFA/WebApiFIFA.csproj

dotnet sln add ./LibraryFIFA/LibraryFIFA.csproj

Our cloned projects use .NET 9.0. Unfortunately, at the time of writing this article, the new ASPIRE projects are created for .NET 8.0. We must unify all the projects so that they all use .NET 9.0. Therefore, update the .csproj files for SoccerFIFA.AppHost and SoccerFIFA.ServiceDefaults with package versions as follows:

SoccerFIFA.AppHost.csproj

change .net8.0 to .net 9.0

Package	Version
Aspire.Hosting.AppHost	9.1.0

SoccerFIFA.ServiceDefaults

change .net8.0 to .net 9.0

Package	Version
Microsoft.Extensions.Http.Resilience	9.2.0
Microsoft.Extensions.ServiceDiscovery	9.1.0
OpenTelemetry.Exporter.OpenTelemetryProtocol	1.11.1
OpenTelemetry.Extensions.Hosting	1.11.1
OpenTelemetry.Instrumentation.AspNetCore/td>	1.11.0
OpenTelemetry.Instrumentation.Http	1.11.0
OpenTelemetry.Instrumentation.Runtime	1.11.0

Next, we need to add references in the SoccerFIFA.AppHost project to the BlazorFIFA and WebApiFIFA projects with these commands:

dotnet add ./SoccerFIFA.AppHost/SoccerFIFA.AppHost.csproj reference ./BlazorFIFA/BlazorFIFA.csproj
dotnet add ./SoccerFIFA.AppHost/SoccerFIFA.AppHost.csproj reference ./WebApiFIFA/WebApiFIFA.csproj

Also, both BlazorFIFA and WebApiFIFA projects need to have references into SoccerFIFA.ServiceDefaults with:

dotnet add ./BlazorFIFA/BlazorFIFA.csproj reference ./SoccerFIFA.ServiceDefaults/SoccerFIFA.ServiceDefaults.csproj

dotnet add ./WebApiFIFA/WebApiFIFA.csproj reference ./SoccerFIFA.ServiceDefaults/SoccerFIFA.ServiceDefaults.csproj

Inside the SoccerFIFA root folder, start VS Code with:

code .

In the root folder, let us build to make sure everything works properly. Therefore, run this command:

dotnet build

You will receive an error message that suggests that you add the following markup to thje the .csproj file that belongs to 

<Sdk Name="Aspire.AppHost.Sdk" Version="9.0.0" />

Add the following code to SoccerFIFA.AppHost.csproj just under the opening <Project ... > tag. Thereafter, the solution should build without any errors.

Then, in the Program.cs files of both BlazorFIFA and WebApiFIFA, add this code before "var app = builder.Build();":

// Add service defaults & Aspire components.
builder.AddServiceDefaults();

In the Program.cs file in SoccerFIFA.AppHost, add this code right before “builder.Build().Run();”:

var api = builder.AddProject<Projects.WebApiFIFA>("backend");
builder.AddProject<Projects.BlazorFIFA>("frontend")
    .WithReference(api)
    .WaitFor(api);

Now, the relative name for the API app is “backend”. Therefore, we can change the base address to http://backend. Change Program.cs file in BlazorFIFA to:

client.BaseAddress = new Uri("http://backend/");

Test .NET Aspire Solution

To test the solution, in the SoccerFIFA.AppHost folder, start the application with:

dotnet watch

NOTE: If you are asked to enter a token, copy and paste it from the value in your terminal window:

This is what you should see in your browser:

Click on the app represented by the frontend link on the second row. You should experience the Blazor app:

At this stage we get a sense of what .NET Aspire can do for us. It essentially orchestrates the connection between multiple projects and produces a single starting point in the Host project. Let us take this journey one step further by converting our backend API so it uses a SQL Server container instead of SQLite.

Using SQL Server instead of SQLite

Stop the running application by hitting CTRL C.

IMPORTANT: You will need to ensure that Docker Desktop is running on your computer because Aspire will start SQL Server in a container. Also, update your Docker Desktop to the  latest version.

Add this package to the WebApiFIFA project:

dotnet add package Aspire.Microsoft.EntityFrameworkCore.SqlServer

Also in WebApiFIFA project Program.cs file, comment out (or delete) this code:

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(connectionString));

Place the below code just before builder.AddServiceDefaults():

builder.AddSqlServerDbContext<ApplicationDbContext>("sqldata");

Cleanup the backend API project (WebApiFIFA) from all traces of SQLite by doing the following:

1. Delete SQLite files college.db, college.db-shm, and college.db-wal.
2. In WebApiFIFA.csproj, delete: <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="9.0.2" />
3. Delete the Data/Migrations folder.
4. Delete the ConnectionStrings section in appsettings.json

We will create new migrations that work with SQL Server, instead of SQLite. Therefore, run the following command from within a terminal window inside folder WebApiFIFA.

dotnet ef migrations add M1 -o Data/Migrations

Configure AppHost to use SQL Server

The WebApiFIFA.AppHost project is the orchestrator for your app. It's responsible for connecting and configuring the different projects and services of your app. Add the .NET Aspire Entity Framework Core Sql Server library package to your SoccerFIFA.AppHost project with:

dotnet add package Aspire.Hosting.SqlServer

In the Program.cs file in SoccerFIFA.AppHost project comment out (or delete) the following code:

var api = builder.AddProject<Projects.WebApiFIFA>("backend");

Replace the above code with: 

var sql = builder.AddSqlServer("sql").AddDatabase("sqldata"); 

var api = builder.AddProject<Projects.WebApiFIFA>("backend")

    .WithReference(sql)

    .WaitFor(sql); 

The preceding code adds a SQL Server Container resource to your app and configures a connection to a database called sqldata. The Entity Framework classes you configured earlier will automatically use this connection when migrating and connecting to the database.

Run the solution

After ensuring that your Docker Desktop is running, execute this terminal window command inside the SoccerFIFA.AppHost folder:

dotnet watch

Your browser will look like this:

Click on the highlighted link above. Our application works just as it did before. The only difference is that this time we are using SQL Server running in a container:

I hope you found this useful and are able to see the possibilities of this new addition to .NET.

Extending Users and Roles with ASP.NET Identity in VS Code

In this tutorial, I will demo how to add more data fields to the standard users & roles database. In order to proceed with this tutorial, you need to have the following prerequisites:

• VS Code
• You have installed .NET 8.0
• You have installed the dotnet-ef tool
• You have installed the dotnet-aspnet-codegenerator tool

Companion Video: https://youtu.be/xo4usBberVA

Getting Started

Download the source code for an application that seeds some sample users and roles into an SQLite database from this GitHub repo:

git clone https://github.com/medhatelmasry/Code1stUsersRoles
cd Code1stUsersRoles

When you run this app, you will be able to access the privacy page (/privacy) with the following credentials:

Email	Password	Role	Page
aa@aa.aa	P@$$w0rd	Admin	/privacy
mm@mm.mm	P@$$w0rd	Member	/

This is because the PrivacyModel class in Pages/Privacy.cshtml.cs is annotated with the following:

[Authorize (Roles = "Member, Admin")]

Click on the Register link on the top-right side of your keyboard to add a new user. 

When you click on the Register button, you will receive a page that looks like this:

Click on Logout in the top-right corner.

Open the application folder in VS Code.

Suppose we want to capture more data about the user, in addition to email and password. Let us assume we want to extend user data with FirstName & LastName.

An easy way to do this is to create a new class that extends IdentityUser and adds the above properties. Create a Models folder and add a new class named CustomUser to it with the following class code: 

public class CustomUser : IdentityUser {

  public CustomUser() : base() { }

  public string? FirstName { get; set; }

  public string? LastName { get; set; }

}

We may also wish to extend the standard roles table with these properties:

Description

CreatedDate

Just as we did with users, we will also create another class for roles that inherits from IdentityRole. In the Models folder, create another class named CustomRole and add to it the following code:

public class CustomRole : IdentityRole {

  public CustomRole() : base() { }

  public CustomRole(string roleName) : base(roleName) { }

  public CustomRole(string roleName, string description,

    DateTime createdDate)

    : base(roleName) {

    base.Name = roleName;

    this.Description = description;

    this.CreatedDate = createdDate;

  }

  public string? Description { get; set; }

  public DateTime CreatedDate { get; set; }

}

Add the following to Pages/ _ViewImports.cshtml:

@using Code1stUsersRoles.Models

Edit Data/ApplicationDbContext.cs file and make ApplicationDbContext  inherit from IdentityDbContext<CustomUser, CustomRole, string>. The ApplicationDbContext class code should look like this:

public class ApplicationDbContext : IdentityDbContext<CustomUser, CustomRole, string> {

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)

        : base(options) { }

}

Modify Data/ModelBuilderExtensions.cs so that it uses CustomUser instead of IdentityUser & CustomRole instead of IdentityRole. 

• When creating a role, add data for Description and CreatedDate.
• When creating a user, add data for FirstName & LastName.

In the Program.cs class, replace IdentityUser with ApplicationUser and IdentityRole with ApplicationRole. The builder.Services.AddIdentity… statement will look like this:

builder.Services.AddIdentity<CustomUser, CustomRole>(

options => {

    options.Stores.MaxLengthForKeys = 128;

})

.AddEntityFrameworkStores<ApplicationDbContext>()

.AddDefaultUI()

.AddDefaultTokenProviders()

.AddRoles<CustomRole>();

Edit Pages/Shared/_LoginPartial.cshtml and change:

@inject SignInManager<IdentityUser> SignInManager

@inject UserManager<IdentityUser> UserManager

TO

@inject SignInManager<CustomUser> SignInManager

@inject UserManager<CustomUser> UserManager

Let us start with a clean database and migration. Therefore, delete app.db and the Data/Migrations folder. 

Then, execute the following commands from within a terminal window in the root folder of the application:

dotnet ef migrations add M1 -o Data/Migrations

dotnet ef database update

At this stage, all the database tables are created and seeded. Let us run our application.

To prove that user and role data are successfully seeded, login with any of the below credentials that were previously seeded:

Email: aa@aa.aa    Password: P@$$w0rd

Email: mm@mm.mm    Password: P@$$w0rd

The next task we need to accomplish is to modify the registration page so that the application can capture extended data such as FirstName & LastName. ASP.NET provides ASP.NET Core Identity as a Razor Class Library. This means that the registration UI is baked into the assemblies and is surfaced with the .AddDefaultUI() option with the services.AddIdentity() command in Program.cs.

We need to add some additional packages so that we can scaffold the view for account registration. From within a terminal window at the root of your application, run the following commands: 

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design

dotnet add package Microsoft.EntityFrameworkCore.Design

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

NOTE:

Visit the following site for more information on scaffolding identity views:

https://docs.microsoft.com/en-us/aspnet/core/security/authentication/scaffold-identity?view=aspnetcore-3.0&tabs=netcore-cli

If you do not already have the .NET code-generation (scaffolding) tool, execute the following command from within a terminal window:

dotnet tool install -g dotnet-aspnet-codegenerator

Here are some useful commands pertaining to the code-generation (scaffolding) tool:

Help with the tool	dotnet aspnet-codegenerator identity -h
List all the views that can be scaffolded	dotnet aspnet-codegenerator identity --listFiles
Scaffold three views	dotnet aspnet-codegenerator identity --files "Account.Register;Account.Login;Account.RegisterConfirmation"
Expose all files	dotnet aspnet-codegenerator identity

Since we need to modify the registration controller and view, we instruct the scaffolder to surface the code used for registration. To do this, we will scaffold three pages that pertain to account registration and login. Run the following command from within a terminal window:

dotnet aspnet-codegenerator identity --files "Account.Register;Account.Login;Account.RegisterConfirmation" -dc ApplicationDbContext

NOTE: If you encounter an error, temporarily comment out the statement "builder.Seed();" in ApplicationDbContext.cs and try the above command again.

The above command generates a handful of razor view pages under folder Areas/Identity/Pages/Account.

Edit the code-behind file Areas/Identity/Pages/Account/Register.cshtml.cs. 

Add the following properties to the InputModel class: 

[Required]

[DataType(DataType.Text)]

[StringLength(50, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 2)]

[Display(Name ="First Name")]

public string FirstName { get; set; }

[Required]

[DataType(DataType.Text)]

[StringLength(50, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 2)]

[Display(Name = "Last Name")]

public string LastName { get; set; }

In the same file, edit the code in the OnPostAsync() method so that line:

var user = CreateUser();

is changed to: 

var user = new CustomUser {

  UserName = Input.Email,

  Email = Input.Email,

  FirstName = Input.FirstName,

  LastName = Input.LastName

};

Next, let us update the UI. Edit razor page Areas/Identity/Pages/Account/Register.cshtml. Add the following markup to the form right before the email/username block: 

<div class="form-floating mb-3">

  <input asp-for="Input.FirstName" class="form-control" autocomplete="firstname" aria-required="true" placeholder="First Name"/>

  <label asp-for="Input.FirstName"></label>

  <span asp-validation-for="Input.FirstName" class="text-danger"></span>

</div>

<div class="form-floating mb-3">

  <input asp-for="Input.LastName" class="form-control" autocomplete="lastname" aria-required="true" placeholder="Last Name"/>

  <label asp-for="Input.LastName"></label>

  <span asp-validation-for="Input.LastName" class="text-danger"></span>

</div>

The code generator added some unnecessary code to Program.cs around line 13. Find the following code in Program.cs and comment it out or delete it:

builder.Services.AddDefaultIdentity<CustomUser>(options => options.SignIn.RequireConfirmedAccount = true).AddEntityFrameworkStores<ApplicationDbContext>();

Run the web application and click on the Register button on the top-right side.

When you click on Register, all user data will be saved in the database. 

We have succeeded in updating the registration page so that additional user data is stored. Thanks for coming this far in this tutorial.

Code First development with ASP.NET MVC

 In this tutorial, you will develop a data driven web application using ASP.NET MVC, SQL Server, and Entity Framework. We shall use Visual Studio Code for our editor. The data model will be based on a Team/Player relationship in sports. We will use SQL Server running in a Docker Container.

Source code: https://github.com/medhatelmasry/TeamPlayersMvc

Assumptions

It is assumed that you have the following installed on your computer:

• Visual Studio Code
• .NET 7.0 SDK
• Docker Desktop

Visual Studio Code Extension

Add this Visual Studio Code Extension if you do not have it already:

The Data Model

We will use the following class to represent a Team:

public class Team {

    [Key]

    public string? TeamName { get; set; }

    public string? City { get; set; }

    public string? Province { get; set; }

    public string? Country { get; set; }

    public List<Player>? Players { get; set; }

}

The primary key is the TeamName and a team has many players.

The following class represents a Player:

public class Player {

    public int PlayerId { get; set; }

    public string? FirstName { get; set; }

    public string? LastName { get; set; }

    public string?  Position { get; set; }

    public string? TeamName { get; set; }

    [ForeignKey("TeamName")]

    public Team? Team { get; set; }

}

The primary key is PlayerId and each player must belong to a team.

Getting started

In a working directory, run the following command in a terminal window to create a Razor Pages web application in a folder names TeamPlayers:

dotnet new mvc -f net7.0 -o TeamPlayersMvc

Change to the newly created folder with terminal command:

cd TeamPlayersMvc

For the application to work with SQL Server, we will need to add some packages by running the following commands:

dotnet add package Microsoft.EntityFrameworkCore

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design

 

We will use code generation to scaffold MVC controllers & views. For that purpose, you will also need to add this package:

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design

If you have not done so already, you will need to globally install the following tools for Entity Framework and Code Generation respectively:

dotnet tool install -g dotnet-aspnet-codegenerator

dotnet tool install -g dotnet-ef

NOTE: If these tools are already installed, run the above commands while replacing ‘install’ with ‘update’ to get the latest version of the tool.

Creating the model classes

Open your app in Visual Studio Code with this command:

code .

Add to the Models Teamclasses Team & Player mentioned under title “The Data Model” above.

The Context Class

We will need to create a database context class to work with relational databases using Entity Framework. To this end, create a Data folder. Inside the Data folder, create a class named ApplicationDbContext with the following code:

 

public class ApplicationDbContext : DbContext {

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)

        : base(options) {}

    public DbSet<Team>? Teams { get; set; }

    public DbSet<Player>? Players { get; set; }

}

Seeding the database with sample data

It is always useful to have some sample data to visualize what the app does. Therefore, we will create a class dedicated to seeding data. In the Data folder, create a static class named SeedData and add to it the following code that contains sample data for teams and players:

public static class SeedData {

    // this is an extension method to the ModelBuilder class

    public static void Seed(this ModelBuilder modelBuilder) {

        modelBuilder.Entity<Team>().HasData(

            GetTeams()

        );

        modelBuilder.Entity<Player>().HasData(

            GetPlayers()

        );

    }

    public static List<Team> GetTeams() {

        List<Team> teams = new List<Team>() {

            new Team() {    // 1

                TeamName="Canucks",

                City="Vancouver",

                Province="BC",

                Country="Canada",

            },

            new Team() {    //2

                TeamName="Sharks",

                City="San Jose",

                Province="CA",

                Country="USA",

            },

            new Team() {    // 3

                TeamName="Oilers",

                City="Edmonton",

                Province="AB",

                Country="Canada",

            },

            new Team() {    // 4

                TeamName="Flames",

                City="Calgary",

                Province="AB",

                Country="Canada",

            },

            new Team() {    // 5

                TeamName="Leafs",

                City="Toronto",

                Province="ON",

                Country="Canada",

            },

            new Team() {    // 6

                TeamName="Ducks",

                City="Anaheim",

                Province="CA",

                Country="USA",

            },

            new Team() {    // 7

                TeamName="Lightening",

                City="Tampa Bay",

                Province="FL",

                Country="USA",

            },

            new Team() {    // 8

                TeamName="Blackhawks",

                City="Chicago",

                Province="IL",

                Country="USA",

            },

        };

        return teams;

    }

    public static List<Player> GetPlayers() {

        List<Player> players = new List<Player>() {

            new Player {

                PlayerId = 1,

                FirstName = "Sven",

                LastName = "Baertschi",

                TeamName = "Canucks",

                Position = "Forward"

            },

            new Player {

                PlayerId = 2,

                FirstName = "Hendrik",

                LastName = "Sedin",

                TeamName = "Canucks",

                Position = "Left Wing"

            },

            new Player {

                PlayerId = 3,

                FirstName = "John",

                LastName = "Rooster",

                TeamName = "Flames",

                Position = "Right Wing"

            },

            new Player {

                PlayerId = 4,

                FirstName = "Bob",

                LastName = "Plumber",

                TeamName = "Oilers",

                Position = "Defense"

            },

        };

        return players;

    }

}

Note that the SeedData class is static because it contains an extension method named Seed() to ModelBuilder.

The Seed() method needs to be called from somewhere. The most appropriate place is the ApplicationDbContext class. Add the following OnModelCreating() method to the ApplicationDbContext class:

protected override void OnModelCreating(ModelBuilder builder)

{

    base.OnModelCreating(builder);

    builder.Entity<Player>().Property(m => m.TeamName).IsRequired();

    builder.Entity<Team>().Property(p => p.TeamName).HasMaxLength(30);

    builder.Entity<Team>().ToTable("Team");

    builder.Entity<Player>().ToTable("Player");

    builder.Seed();

} 

In addition to seeding data, the above code ensures the following:

• TeamName is required
• The maximum length of TeamName is 30 characters
• The names of the tables that get created in the database are Team & Player. Otherwise, the names get created as Teams & Players.

The database

You can use any SQL Server database you wish. In my case, so that this app works on Linux, Windows, Mac Intel, and Mac M1, I will run SQL Server in a Docker container. To run SQL Server in a Docker container, run the following command:

docker run --cap-add SYS_PTRACE -e ACCEPT_EULA=1 -e MSSQL_SA_PASSWORD=SqlPassword! -p 1444:1433 --name azsql -d mcr.microsoft.com/azure-sql-edge

The connection string to the database is setup in the appsettings.json (or appsetting.Development.json) file. Edit this file and make the following highlighted updates to it:

{

  "Logging": {

    "LogLevel": {

      "Default": "Information",

      "Microsoft.AspNetCore": "Warning"

    }

  },

  "AllowedHosts": "*",

  "ConnectionStrings": {

    "DefaultConnection": "Server=tcp:127.0.0.1,1444;Database=TeamPlayersDB;UID=sa;PWD=SqlPassword!;TrustServerCertificate=True;"

  }

}

 

To make our app work with SQL Server, you will need to add the following code to Program.cs just before “var app = builder.Build();”: 

string connStr = builder.Configuration.GetConnectionString("DefaultConnection")!;

builder.Services.AddDbContext<ApplicationDbContext>(

    options => options.UseSqlServer(connStr)

);

Migrations

We can now create a migration named m1 with:

dotnet ef migrations add M1 -o Data/Migrations

This creates a migrations file in Data/Migrations folder. To execute the migrations and create the database and seed data, run the following command:

dotnet ef database update

If all goes well and no errors are generated, we can assume that a database named TeamPlayersDB was created, and data is seeded into tables Team & Player.

NOTE: If you wish to drop the database for whatever reason, you can run command: dotnet ef database drop

 

This is what the tables in the database look like:

Scaffolding Teams & Players controller and views

To incorporate pages into our app that allow us to manage Team & Player data, we will scaffold the necessary controller & views using the aspnet-codegenerator utility. Run the following command from a terminal window in the root of the project to generate files pertaining to teams and players respectively:

dotnet aspnet-codegenerator controller -name TeamsController -outDir Controllers -m Team -dc ApplicationDbContext -udl -scripts

dotnet aspnet-codegenerator controller -name PlayersController -outDir Controllers -m Player -dc ApplicationDbContext -udl -scripts

This produces controllers in the Controllers folder and fiews files in folders Views/Teams & Views/Players respectively. To add menu items on the home page that point to Team & Player pages, edit Views/Shared/_Layout.cshtml and add the following HTML to the <ul> block around line 28:

<li class="nav-item">

    <a class="nav-link text-dark" asp-area="" asp-controller="Teams" asp-action="Index">Teams</a>

</li>

<li class="nav-item">

    <a class="nav-link text-dark" asp-area="" asp-controller="Players" asp-action="Index">Players</a>

</li>

The final product

Run the web app and notice the main menu:

Click on Teams:

Click on Players:

Conclusion

You just learned how to use the code-first database approach with ASP.NET MVC. The same priciples work with ASP.NET Razor Pages. 

Refining your ASP.NET data annotations

In this tutorial, we will learn some of the most important data annotations that are used when modeling a simple class in ASP.NET. Some of these annotations pertain to validations, others pertain to database related schemas and constraints, and yet others pertain to data formatting. Although all these concepts work for both ASP.NET MVC and Razor Pages, we will be using Razor Pages in this tutorial.

Source Code: https://github.com/medhatelmasry/AnnotationsDemo

Companion Video: https://www.youtube.com/watch?v=6_twITOH-Tc

Assumptions

It is assumed that you have the following installed on your computer:

• .NET 7.0
• “dotnet-ef” tool
• “aspnet-codegenerator” tool
• Visual Studio code

Getting Started

In a terminal window, run the following command to creates an ASP.NET Razor Pages application that user the SQLite database with individual authentication:

dotnet new razor -f net7.0 --auth individual -o AnnotationsDemo

Change directory to where the app was created with:

cd AnnotationsDemo

Add the following packages to the application:

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

In a /Models folder, create a Student class and add to it the following code:

[Table("PublicSchoolStudent")]

[Index(nameof(School))]

public class Student {

    [Key]

    [Column(Order = 1)]

    public int StudentNumber { get; set; }

    [Required(ErrorMessage = "{0} is required.")]

    [StringLength(30, ErrorMessage = "{0} must be between {2} & {1} characters."), MinLength(2)]

    [Display(Name = "First Name")]

    public string? FirstName { get; set; }

    [Required(ErrorMessage = "{0} is required.")]

    [StringLength(30, ErrorMessage = "{0} cannot exceed {1} characters.")]

    // Allow up to 40 uppercase and lowercase 

    // characters. Use custom error.

    [RegularExpression(@"^[a-zA-Z''-'\s]{1,40}$", ErrorMessage = "Only letters allowed.")]

    [Display(Name = "Last Name")]

    public string? LastName { get; set; }

    [NotMapped]

    public string FullName {

        get {

            return $"{FirstName} {LastName}";

        }

    }

    [Key]

    [Column(Order = 2)]

    [MaxLength(60), MinLength(5)]

    public string? School { get; set; }

    [Column("Note", TypeName = "NTEXT")]

    public String? Comment { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]

    [Display(Name = "Created")]

    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]

    public DateTime DateCreated { get; set; }

    [Range(10, 1000, ErrorMessage = "Value for {0} must be between {1} and {2}.")]

    [Display(Name = "Weight in Lbs.")]

    public int Weight;

    [DataType(DataType.EmailAddress)]

    public string? Email { get; set; }

    [Compare("Email")]

    [Display(Name = "Confirm Email Address.")]

    [DataType(DataType.EmailAddress)]

    public string? EmailConfirm { get; set; }

    [ScaffoldColumn(false)]

    public string? StudentPhotoFileName;

}

Here is an explanation of each annotation:

Class Level Annotations

Annotation	What it does . . .
[Table("PublicSchoolStudent")]	The database will be named PublicSchoolStudent
[Index(nameof(School))]	The School column in the database will be indexed

Column Level Annotations

Annotation	What it does . . .
[Key]	This ensures that the property is made a Primary Key
[Key] [Column(Order = 1)] public int StudentNumber { get; set; } [Key] [Column(Order = 2)] public string? School { get; set; }	This defines a composite key comprising StudentNumber and School NOTE: You must add this code to the OnModelCreating() method in the database context class for this to work: builder.Entity().HasKey(table => new {    table.PassportNumber,    table.IssuingCountry });
[MaxLength(60), MinLength(5)] public string? School { get; set; }	The maximum and minimum length of the School property is 60 and 5 respectively. Only Maxength affects the database schema.
[Required(ErrorMessage = "{0} is required.")] public string? FirstName { get; set; }	This ensures that the FirstName property must have a value. ErrorMessage is optional.
[StringLength(30, ErrorMessage = "{0} must be between {2} & {1} characters."), MinLength(5)] public string? FirstName { get; set; }	StringLength allows for the MaxLength and MinLength to be combines into one annotation such that the error message can describe both constraints.
[Display(Name = "First Name")] public string? FirstName { get; set; }	Instead of FirstName, the display name will be “First Name”.
[RegularExpression(@"^[a-zA-Z''-'\s]{1,40}$", ErrorMessage = "Only letters allowed.")] public string? LastName { get; set; }	The regular expression for LastName matches any string that contains letters or spaces.
[NotMapped] public string FullName {    get {       return $"{FirstName} {LastName}";    } }	This property will not be mapped into the database schema because it is a calculated property in the application.
[Column("Note", TypeName = "NTEXT")] public String? Comment { get; set; }	The Comment property in the application will be mapped as a Note column in the database of type NTEXT.
[DatabaseGenerated(DatabaseGeneratedOption.Computed)] public DateTime DateCreated { get; set; }	The DateCreated column will be generated by the database engine. Note: You must add the proprietary function for generating the current date in the OnModelCreating() method in the database context class. In the case of SQLite it would look like this: builder.Entity()    .Property(s => s.DateCreated)    .HasDefaultValueSql("DATE('now')"); In the case of SQL Server, it would look like this: builder.Entity()    .Property(s => s.DateCreated)    .HasDefaultValueSql("GETDATE()");
[DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")] public DateTime DateCreated { get; set; }	The display format for DateCreated will be MM/dd/yyyy. Example: 10/29/2022
[Range(10, 1000, ErrorMessage = "Value for {0} must be between {1} and {2}.")] public int Weight;	The Weight column will have a range from 10 to 1000. Both are inclusive.
[DataType(DataType.EmailAddress)] public string? Email { get; set; }	The data type for the Email property is specified as EmailAddress
public string? Email { get; set; } [Compare("Email")] public string? EmailConfirm { get; set; }	The Compare annotation makes sure that EmailConfirm is equal to Email
[ScaffoldColumn(false)] public string? StudentPhotoFileName;	The code generator will not scaffold the StudentPhotoFileName column

Add the following code to the Data/ApplicationDbContext.cs class:

protected override void OnModelCreating(ModelBuilder builder) {

  base.OnModelCreating(builder);

  builder.Entity<Student>().HasKey(table => new

  {

      table.StudentNumber,

      table.School

  });

  // GETDATE() in SQL Server

  builder.Entity<Student>()

      .Property(s => s.DateCreated)

      .HasDefaultValueSql("DATE('now')");

}

public DbSet<Student>? Students { get; set; }

We can now create and apply EF database migrations with this command:

dotnet ef migrations add m1 -o Data/Migrations

Have a look at the contents of the first *_m1.cs file in the Data/Migrations folder. This is what the command for creating the student table looks like:

protected override void Up(MigrationBuilder migrationBuilder) {

  migrationBuilder.CreateTable(

    name: "PublicSchoolStudent",

    columns: table => new {

        StudentNumber = table.Column<int>(type: "INTEGER", nullable: false),

        School = table.Column<string>(type: "TEXT", maxLength: 60, nullable: false),

        FirstName = table.Column<string>(type: "TEXT", maxLength: 30, nullable: false),

        LastName = table.Column<string>(type: "TEXT", maxLength: 30, nullable: false),

        Note = table.Column<string>(type: "NTEXT", nullable: true),

        DateCreated = table.Column<DateTime>(type: "TEXT", nullable: false, defaultValueSql: "DATE('now')"),

        Email = table.Column<string>(type: "TEXT", nullable: true),

        EmailConfirm = table.Column<string>(type: "TEXT", nullable: true)

    },

    constraints: table => {

        table.PrimaryKey("PK_PublicSchoolStudent", x => new { x.StudentNumber, x.School });

    });

  migrationBuilder.CreateIndex(

    name: "IX_PublicSchoolStudent_School",

    table: "PublicSchoolStudent",

    column: "School");

}

Note the following:

• The name of the table is PublicSchoolStudent
• All the model property MaxLength values are being applied to the database schema
• The Comment property in the Student model is called Note in the database and is set of type NTEXT
• The DateCreated column in the database has a default value generated with the DATE('now') SQLite function
• The primary key is a composite key of StudentNumber & School
• An index will be created on the School column

Apply the migrations with the following command:

dotnet ef database update

Let us scaffold the razor pages such that they are created inside the Pages/StudentsPages folder with this terminal window command:

dotnet aspnet-codegenerator razorpage -m Student -dc ApplicationDbContext -udl -outDir Pages/StudentPages --referenceScriptLibraries

Add this to the menu system in Pages/Shared/_Layout.cshtml:

<li class="nav-item">

  <a class="nav-link text-dark" asp-area="" asp-page="/StudentPages/Index">Student</a>

</li>

Start the application with:

dotnet watch

On the main menu, click on students. You will see a page that looks like this:

Click on “Create New”. This will display the form for adding data. Here’s your chance to check out all the column limitations that we put in place.

This is what the list of students looks like:

Unfortunately, clicking on Edit, Details and Delete does not work. The solution to this bug is to:

1) Edit Pages/StudentPages/Index.cshtml and replace this block:

<td>

  @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |

  @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |

  @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })

</td>

WITH

<td>

  <a asp-page="./Edit" asp-route-id="@item.StudentNumber" asp-route-school="@item.School">Edit</a> |

  <a asp-page="./Details" asp-route-id="@item.StudentNumber" asp-route-school="@item.School">Details</a> |

  <a asp-page="./Delete" asp-route-id="@item.StudentNumber" asp-route-school="@item.School">Delete</a>

</td>

2) Edit Pages/StudentPages/Edit.cshtml.cs, Pages/StudentPages/Details.cshtml.cs, and Pages/StudentPages/Delete.cshtml.cs as follows:

Change “OnGetAsync(int? id)” TO “OnGetAsync(int? id, string? school)”

Also, change 

var student =  await _context.Students.FirstOrDefaultAsync(m => m.StudentNumber == id);

TO

var student = await _context.Students.FirstOrDefaultAsync(m => m.StudentNumber == id && m.School == school);

3. Edit Pages/StudentPages/Delete.cshtml.cs. Change

OnPostAsync(int? id)

TO

OnPostAsync(int? id, string? school)

Also, change 

var student = await _context.Students.FindAsync(id);

TO

var student = await _context.Students.FirstOrDefaultAsync(m => m.StudentNumber == id && m.School == school);

The application should now work as expected.

Conclusion

This article should help you optimize your data annotations in ASP.NET