How to join two or more tables using Entity Framework in ASP.NET MVC?

Question

I am looking to join 3 tables together using entity framework, dependency injection and with SQLite.

So far I have this:

using System.Collections.Generic;
using System.Linq;

namespace MMS.Data.Models
{   
    public class User
    {           
        public int Id { get; set; }

        //Role of user

        public Role Role { get; set; }
        
        // first name of user
        public string Name { get; set; }
        
        // Date of birth 
        public string DOB { get; set; }

        public string Gender { get; set; }   
        public string Address { get; set; }

        //Telephone number
        public string MobileNumber { get; set; }

        //email address
        public string EmailAddress { get; set;}

        // EF Relationship - a user can have many bookings 
        public IList<Booking> Bookings { get; set; } = new List<Booking>();
    }

    public class Booking
    {     
        public int Id { get; set; }      

        // name of reviewer
        public string Name { get; set; }   

        // date review was made        
        public DateTime CreatedOn { get; set; }

        // reviewer comments
        public string Comment { get; set; }
    
        // EF dependent relationship booking belongs to a user
        public int UserId { get; set; }

        // Navigation property
        public User User { get; set; } 
    }
}

In terms of the services folder, I am okay with that. The thing that is bugging me is how do I create an 3rd table model and have it combined with the other two.

For example, I want the users to able to make bookings, and an admin able to come in and look and add users, delete bookings etc. This bit I understand.

How do I hook up an employee to able to look at the booking and the person etc? I hope I explained this correctly. Essentially a third table where employees can be created and pull the information of the user and the booking?

I know I could technically do this without a third table as I could just continue on and allow a user to have access to all the bookings and user information. However, I do want a certain types of employees can only see what they need to see.

Can anyone help?

Answer 1

This way employee will be binded to User table. You can access which employee makes the booking or you can just modify it.

Basically public IList<YourModel> Model {get;set;} needs to be annoated in YourModel as public YourModel YourModel{get;set;} Below is an example:

 public class Employee
    {
      public string Role { get; set;} // any property
      public IList<User> Users { get; set;}
    
    
    }
 public class User
    {           
        public int Id { get; set; }

        //Role of user

        public Role Role { get; set; }
        
        // first name of user
        public string Name { get; set; }
        
        // Date of birth 
        public string DOB { get; set; }

        public string Gender { get; set; }   
        public string Address { get; set; }

        //Telephone number
        public string MobileNumber { get; set; }

        //email address
        public string EmailAddress { get; set;}

        // EF Relationship - a user can have many bookings 
        public IList<Booking> Bookings { get; set; } = new List<Booking>();

        public Employee Employee { get; set; }
    }