PROWAREtech

Blazor: Create Many-to-Many Table Join in MS SQL Server

This example uses .NET 5 but works with .NET 6 and .NET 8. This code is not backwards compatible with .NET Core 3.1 and earlier.

Have SQL Server installed and running on the development machine or on the network and have Visual Studio 2019 open with a new WebAPI project or Blazor WebAssembly project created with the "ASP.NET Core hosted" option.

Install NuGet Packages

Make sure that the NuGet packages Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Tools (all v5.x, v6.x or whatever version .NET the project is using) are installed in the server project.

SQL Server Connection String

Modify the server project's appsettings.json file to include a ConnectionStrings property. The database does not need to exist.

{
  "ConnectionStrings": {
	"Default": "Server=LOCALHOST\\SQLEXPRESS;Database=ACME;Trusted_Connection=True;MultipleActiveResultSets=True"
  },
  "Logging": {
	"LogLevel": {
	  "Default": "Information",
	  "Microsoft": "Warning",
	  "Microsoft.Hosting.Lifetime": "Information"
	}
  },
  "AllowedHosts": "*"
}

Add Group and User Classes to Project

In the root of the shared project, create a file named App.cs to contain all the group and user classes.

// App.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

public class Group
{
	[Key]
	public int Id { get; set; }

	[Required]
	[MaxLength(20)]
	public string Name { get; set; }

	public ICollection<User> Users { get; set; }
	public ICollection<UserGroup> UserGroups { get; set; }
}

public class User
{
	[Key]
	public int Id { get; set; }

	[Required]
	[EmailAddress]
	[MaxLength(100)]
	public string Email { get; set; }

	[Required]
	public string Password { get; set; }

	public DateTime Created { get; set; }

	public ICollection<Group> Groups { get; set; }
	public ICollection<UserGroup> UserGroups { get; set; }
}

public class UserGroup
{
	public Group Group { get; set; }
	public User User { get; set; }
	public DateTime Since { get; set; }
}

Create the Application Database Context

In the root of the server project, create a file named AppDbContext.cs.

// AppDbContext.cs
using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
	public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
	public DbSet<User> Users { get; set; }
	public DbSet<Group> Groups { get; set; }
	public DbSet<UserGroup> UserGroups { get; set; }

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<User>().HasMany(u => u.Groups).WithMany(g => g.Users).UsingEntity<UserGroup>(
			j => j.HasOne(m => m.Group).WithMany(g => g.UserGroups).IsRequired(),
			j => j.HasOne(m => m.User).WithMany(g => g.UserGroups).IsRequired()
		);
		modelBuilder.Entity<User>(u => u.HasIndex(e => e.Email).IsUnique());
		modelBuilder.Entity<Group>(g => g.HasIndex(e => e.Name).IsUnique());
	}
}

Modify Startup.cs

Modify Startup.cs as follows.

// Startup.cs
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

using Microsoft.EntityFrameworkCore; // ########## THIS LINE ADDED ##########

namespace BlazorApp1.Server
{
	public class Startup
	{
		public Startup(IConfiguration configuration)
		{
			Configuration = configuration;
		}

		public IConfiguration Configuration { get; }

		// This method gets called by the runtime. Use this method to add services to the container.
		// For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
		public void ConfigureServices(IServiceCollection services)
		{
			// ########### FOLLOWING FOUR LINES ADDED ###########
			services.AddDbContext<AppDbContext>(options =>
			{
				options.UseSqlServer(Configuration.GetConnectionString("Default"));
			});
			// ########### END OF ADDED CODE ###########

			services.AddControllersWithViews();
			services.AddRazorPages();
		}

		// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
		public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
		{
			if (env.IsDevelopment())
			{
				app.UseDeveloperExceptionPage();
				app.UseWebAssemblyDebugging();
			}
			else
			{
				app.UseExceptionHandler("/Error");
			}

			app.UseBlazorFrameworkFiles();
			app.UseStaticFiles();

			app.UseRouting();

			app.UseEndpoints(endpoints =>
			{
				endpoints.MapRazorPages();
				endpoints.MapControllers();
				endpoints.MapFallbackToFile("index.html");
			});
		}
	}
}

Create/Update the SQL Server Database

Now, enough has been done to create the database. Open the Package Manager Console (Tools > NuGet Package Manager > Package Manager Console) and enter the following two PM commands. NOTE: If the following commands do not work on Windows 7 (with a ScriptHalted error) then the PowerShell version may be too old. Install the latest version from HERE. These two commands will create the database and the table for the customers.

PM> add-migration AddGroupsAndUsersTables
PM> update-database

The results should look something like this:

PM> add-migration AddGroupsAndUsersTables
Build started...
Build succeeded.
To undo this action, use Remove-Migration.
PM> update-database
Build started...
Build succeeded.
Done.

Create the Web API / RESTful API.

Add a new class file in the server project's Controllers folder named GroupsController.cs

// GroupsController.cs
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using System.Collections.Generic;

namespace BlazorApp1.Server.Controllers
{
	[ApiController]
	[Route("api/[controller]")]
	public class GroupsController : ControllerBase
	{
		private readonly AppDbContext db;

		public GroupsController(AppDbContext db)
		{
			this.db = db;
		}

		[HttpPost]
		public async Task<Group> Post([FromBody] Group create)
		{
			if (db.Groups.Count(x => x.Name == create.Name) > 0)
				return null;
			EntityEntry<Group> group = await db.Groups.AddAsync(create);
			await db.SaveChangesAsync();
			return group.Entity;
		}

		[HttpGet]
		public IEnumerable<Group> Get()
		{
			return db.Groups;
		}
	}
}

Add a new class file in the server project's Controllers folder named UsersController.cs

// UsersController.cs
using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using System.Security.Cryptography;
using System.Text;
using Microsoft.EntityFrameworkCore;

namespace BlazorApp1.Server.Controllers
{
	[ApiController]
	[Route("api/[controller]")]
	public class UsersController : ControllerBase
	{
		private readonly AppDbContext db;

		private static string CreateHash(string password)
		{
			var salt = "a97eff51db1544c7a3c2ddeb2053f059";
			var md5 = new HMACSHA256(Encoding.UTF8.GetBytes(salt + password));
			byte[] data = md5.ComputeHash(Encoding.UTF8.GetBytes(password));
			password = string.Empty;
			foreach (var x in data)
				password += x.ToString("X2");
			return password;
		}

		public UsersController(AppDbContext db)
		{
			this.db = db;
		}

		[HttpPost]
		public async Task<User> Post([FromBody] User create)
		{
			if (string.IsNullOrEmpty(create.Password) || db.Users.Count(x => x.Email == create.Email) > 0)
				return null;
			create.Created = DateTime.Now;
			create.Password = CreateHash(create.Password);
			create.Groups = db.Groups.Where(x => create.Groups.Contains(x)).ToList();
			EntityEntry<User> user = await db.Users.AddAsync(create);
			if(user.Entity.UserGroups != null)
			{
				foreach (var ug in user.Entity.UserGroups)
					ug.Since = create.Created;
			}
			await db.SaveChangesAsync();
			if (user.Entity != null)
				return new User { Id = user.Entity.Id, Email = user.Entity.Email, Created = user.Entity.Created };
			return null;
		}

		[HttpGet]
		public async Task<User> Get(string email, string group)
		{
			if (string.IsNullOrEmpty(email) || string.IsNullOrEmpty(group))
				return null;
			User user = await db.Users.Where(u => u.UserGroups.Any(ug => ug.Group.Name == group)).SingleOrDefaultAsync(x => x.Email == email);
			if(user != null)
				return new User { Id = user.Id, Email = user.Email, Created = user.Created };
			return null;
		}
	}
}

Create the GroupCreate.razor Page

In the client project, create a new page in the Pages folder named GroupCreate.razor.

@page "/group/create"
@inject HttpClient Http

<p>@message</p>

<EditForm Model="group" OnValidSubmit="OnValid" style="max-width:500px;">
	<DataAnnotationsValidator />
	<ValidationSummary />
	<div class="mb-2">
		<InputText class="form-control" @bind-Value="group.Name" placeholder="Enter Group Name"></InputText>
	</div>
	<div class="mb-2 text-right">
		<button class="btn btn-secondary" disabled="@isDisabled">create group</button>
	</div>
</EditForm>

@code {
	Group group = new Group();
	string message = string.Empty;
	bool isDisabled = false;

	private async Task OnValid()
	{
		isDisabled = true;
		using (var msg = await Http.PostAsJsonAsync<Group>("/api/groups", group, System.Threading.CancellationToken.None))
		{
			isDisabled = false;
			if (msg.IsSuccessStatusCode)
			{
				if (msg.StatusCode != System.Net.HttpStatusCode.NoContent)
				{
					Group result = await msg.Content.ReadFromJsonAsync<Group>();
					if (result != null)
						message = $"Group \"{result.Name}\" created";
				}
				else
					message = $"Group \"{group.Name}\" already exists";
				group.Name = string.Empty;
			}
		}
	}
}

Create the GroupGet.razor Page

In the client project, create a new page in the Pages folder named GroupGet.razor.

@page "/group/get"
@inject HttpClient Http

@if (groups != null)
{
<ul>
	@foreach (var group in groups)
	{
		<li>@group.Name</li>
	}
</ul>
}

@code {
	Group[] groups;

	protected override async Task OnInitializedAsync()
	{
		await base.OnInitializedAsync();

		groups = await Http.GetFromJsonAsync<Group[]>("/api/groups");
	}
}

Create the UserCreate.razor Page

In the client project, create a new page in the Pages folder named UserCreate.razor.

@page "/user/create"
@inject HttpClient Http

<p>@message</p>

@if (user.Groups != null)
{
<EditForm Model="user" OnValidSubmit="OnValid" style="max-width:500px;">
	<DataAnnotationsValidator />
	<ValidationSummary />
	<div class="mb-2">
		@foreach (var g in groups)
		{
			<div class="input-group mb-2">
				<div class="input-group-prepend">
					<div class="input-group-text">
						<input type="checkbox" @onchange="(ChangeEventArgs e) => SetGroup((bool)e.Value, g)" id="chk-@g.Id" checked />
					</div>
				</div>
				<label class="form-control" for="chk-@g.Id">@g.Name</label>
			</div>
		}
	</div>
	<div class="mb-2">
		<InputText class="form-control" @bind-Value="user.Email" placeholder="Enter Email"></InputText>
	</div>
	<div class="mb-2">
		<InputText type="password" class="form-control" @bind-Value="user.Password" placeholder="Enter Password"></InputText>
	</div>
	<div class="mb-2 text-right">
		<button class="btn btn-secondary" disabled="@isDisabled">create user</button>
	</div>
</EditForm>
}

@code {
	User user = new User();
	Group[] groups = null;
	string message = string.Empty;
	bool isDisabled = false;

	private async Task SetGroup(bool chk, Group group)
	{
		await Task.Run(() =>
		{
			if (chk) // then add a group to the user
				user.Groups = groups.Where(x => user.Groups.Contains(x) || x.Name == group.Name).ToList();
			else // remove a group from the user
				user.Groups = user.Groups.Where(x => x.Name != group.Name).ToList();
		});
	}

	protected override async Task OnInitializedAsync()
	{
		await base.OnInitializedAsync();

		groups = await Http.GetFromJsonAsync<Group[]>("/api/groups");
		user.Groups = groups.ToList();
	}

	private async Task OnValid()
	{
		isDisabled = true;
		using (var msg = await Http.PostAsJsonAsync<User>("/api/users", user, System.Threading.CancellationToken.None))
		{
			isDisabled = false;
			if (msg.IsSuccessStatusCode)
			{
				if (msg.StatusCode != System.Net.HttpStatusCode.NoContent)
				{
					User result = await msg.Content.ReadFromJsonAsync<User>();
					if (result != null)
						message = $"{result.Email} created with ID of {result.Id}";
				}
				else
					message = $"{user.Email} already exists";
			}
		}
	}
}

Create the UserGet.razor Page

In the client project, create a new page in the Pages folder named UserGet.razor.

@page "/user/get"
@inject HttpClient Http

<p>@message</p>

@if (groups != null)
{
<form style="max-width:500px;" onsubmit="return false;">
	<div class="mb-2">
		@foreach (var g in groups)
		{
			<div class="input-group mb-2">
				<div class="input-group-prepend">
					<div class="input-group-text">
						<input type="radio" name="group" @onchange="(ChangeEventArgs e) => { group = (string)e.Value; }" value="@g.Name" id="chk-@g.Id" />
					</div>
				</div>
				<label class="form-control" for="chk-@g.Id">@g.Name</label>
			</div>
		}
	</div>
	<div class="mb-2">
		<input class="form-control" @bind-value="email" placeholder="Enter Email" />
	</div>
	<div class="mb-2 text-right">
		<button class="btn btn-secondary" @onclick="OnClick" type="button" disabled="@isDisabled">get user</button>
	</div>
</form>
}

@code {
	Group[] groups = null;
	string message = string.Empty, email = string.Empty, group = string.Empty;
	bool isDisabled = false;

	protected override async Task OnInitializedAsync()
	{
		await base.OnInitializedAsync();

		groups = await Http.GetFromJsonAsync<Group[]>("/api/groups");
	}

	private async Task OnClick()
	{
		try
		{
			isDisabled = true;
			User user = await Http.GetFromJsonAsync<User>($"/api/users?email={Uri.EscapeDataString(email)}&group={Uri.EscapeDataString(group)}");
			message = $"\"{user.Email}\" in \"{group}\" group created on {user.Created} with ID of {user.Id}";
			isDisabled = false;
		}
		catch
		{
			message = "not found";
			isDisabled = false;
		}
	}
}

Add Groups to the Database

Run the application and navigate to the /group/create url to add user groups to the newly-created database. Example groups are admin, user, data-entry, accounting...

Add Users to the Database

Navigate to the /user/create url to add users to the newly-created database.

Retrieve Users from the Database

Navigate to the /user/get url to retrieve the newly-created users from the database using the email and group of the user.

Coding Video

https://youtu.be/sAOolUyfl98

---