MS SQL Server: How can i copy data from two tables into another AFTER a user registers? (ASP.NET MVC)

Question

Hello everyone! I have a web project in ASP.NET MVC and SQL Server 2019, my app already have a functional user registration and roles system, but i have to assign the roles in Managment Studio manually and then i can change them in a view in my app.

I have these tables:

User: PK UserID - FirstName - LastName - Email
RoleMaster: PK ID - RolName
UserRoleMapping: PK ID - FK UserID - FK RoleID

The RoleMaster table has three roles: Admin (ID 1), User (ID 2), and Guest (ID 3)

I need that everytime a new user registers in the system, the database assign its UserID with the Guest role in the UserRoleMapping table.

There's a way to complete the FK UserID and FK RoleID fields at database-level? Maybe a sql script or a trigger that copies the fields into the UserRoleMapping table after a user registration or something, since i just need the new UserID and RoleMaster ID:3 to be copied into the table.

Answer 1

Below is an example similar to your tables. I used the sample code from the following EF documentation. The example uses Sqlite and why the DbContext constructor has a file path rather than a connection string. The rest of the code illustrates how to configure the many-to many relationship in the OnModelCreating() method and the entity class structures.

Relationships
Keys

Models.cs

using System;  
using System.Collections.Generic;  
using Microsoft.EntityFrameworkCore;  
  
namespace EFGetStarted  
{  
    public class DemoDbContext : DbContext  
    {  
        public DbSet<User> Users { get; set; }  
        public DbSet<Role> Roles { get; set; }  
        public DbSet<UserRole> UserRoles { get; set; }  
  
        public string DbPath { get; private set; }  
  
        public DemoDbContext()  
        {  
            var folder = Environment.SpecialFolder.LocalApplicationData;  
            var path = Environment.GetFolderPath(folder);  
            DbPath = $"{path}{System.IO.Path.DirectorySeparatorChar}DemoDb.db";  
        }  
  
        protected override void OnModelCreating(ModelBuilder modelBuilder)  
        {  
            modelBuilder.Entity<UserRole>()  
                .HasKey(ur => new { ur.UserId, ur.RoleId });  
  
            modelBuilder.Entity<UserRole>()  
                .HasOne(u => u.User)  
                .WithMany(ur => ur.UserRoles)  
                .HasForeignKey(u => u.UserId);  
  
            modelBuilder.Entity<UserRole>()  
                .HasOne(r => r.Role)  
                .WithMany(ur => ur.UserRoles)  
                .HasForeignKey(r => r.RoleId);  
        }  
  
        // The following configures EF to create a Sqlite database file in the  
        // special "local" folder for your platform.  
        protected override void OnConfiguring(DbContextOptionsBuilder options)  
            => options.UseSqlite($"Data Source={DbPath}");  
    }  
  
    public class User  
    {  
        public int UserId { get; set; }  
        public string FirstName { get; set; }  
        public string LastName { get; set; }  
        public string Email { get; set; }  
        public List<UserRole> UserRoles { get; set; }  
    }  
  
    public class Role  
    {  
        public int RoleId { get; set; }  
        public string Name { get; set; }  
        public List<UserRole> UserRoles { get; set; }  
  
    }  
  
    public class UserRole  
    {  
        public int UserId { get; set; }  
        public User User { get; set; }  
        public int RoleId { get; set; }  
        public Role Role { get; set; }  
    }  
  
}  

EF Code first commands

dotnet ef migrations add InitialCreate  
dotnet ef database update  

Notice the code to create the user with the Guest role is simply populating the User entity, adding the entity, then saving.

class Program  
    {  
        static void Main(string[] args)  
        {  
            SeedRoles();  
  
            using (var db = new DemoDbContext())  
            {  
                //Save the user and role if the user does not already exist  
                if (!db.Users.Any(u => u.Email == "email@email.com"))  
                {  
                    //New user  
                    User user = new User()  
                    {  
                        FirstName = "Hello",  
                        LastName = "World",  
                        Email = "email@email.com",  
                        UserRoles = new List<UserRole>() { new UserRole() { RoleId = 3 } }  
                    };  
  
                    db.Add(user);  
                    db.SaveChanges();  
                }  
            }  
  
            FetchUserByUsername("email@email.com");  
        }  
  
        public static void SeedRoles()  
        {  
            using (var db = new DemoDbContext())  
            {  
                if (!db.Roles.Any())  
                {  
                    db.Roles.Add(new Role() { Name = "Admin" });  
                    db.Roles.Add(new Role() { Name = "User" });  
                    db.Roles.Add(new Role() { Name = "Guest" });  
                    db.SaveChanges();  
                    Console.WriteLine("Created Roles");  
                    foreach (var r in db.Roles)  
                    {  
                        Console.WriteLine($"{r.RoleId}\t{r.Name}");  
                    }  
                }  
            }  
        }  
  
        public static void FetchUserByUsername(string username)  
        {  
            using (var db = new DemoDbContext())  
            {  
                var u = db.Users  
                    .Include(ur => ur.UserRoles)  
                    .ThenInclude(r => r.Role)  
                    .FirstOrDefault(u => u.Email == username);  
  
                Console.WriteLine($"User: {u.FirstName} | {u.LastName} | {u.Email}");  
                Console.WriteLine("Roles:");  
                foreach (var ur in u.UserRoles)  
                {  
                    Console.WriteLine($"\t{ ur.Role.Name}");  
                }  
            }  
        }  
  
    }  

Answer 2

This would be better done in your application. You are already saving the user to the database, just save a role as "guest" at the same time in your application.