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
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 classpublic 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() { // 1TeamName="Canucks",City="Vancouver",Province="BC",Country="Canada",},new Team() { //2TeamName="Sharks",City="San Jose",Province="CA",Country="USA",},new Team() { // 3TeamName="Oilers",City="Edmonton",Province="AB",Country="Canada",},new Team() { // 4TeamName="Flames",City="Calgary",Province="AB",Country="Canada",},new Team() { // 5TeamName="Leafs",City="Toronto",Province="ON",Country="Canada",},new Team() { // 6TeamName="Ducks",City="Anaheim",Province="CA",Country="USA",},new Team() { // 7TeamName="Lightening",City="Tampa Bay",Province="FL",Country="USA",},new Team() { // 8TeamName="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:
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.
