Is it possible to reference another table so that an employee record is associated with a user record?

Question

I am attempting to build a web app that allows users to view company policies, procedures, newsletters, and their own employee information.

I have my staff table that contains all the employee information (along with related lookup tables for things like prefix, staff grade, etc)

my app uses asp-identity for the login functionality, but I want to be able to return information from the staff table that is only relevant to that particular user.

I know it is possible to extend the ASP.net users table to include custom fields, but this doesn't really suit my goal as the staff table is used in a desktop based app by the admin team.

Answer 1

Add a field to your Staff table UserId for example ALTER TABLE Staff ADD UserId NVARCHAR(256) DEFAULT NULL;

Optionally, you would reference the AspnetUsers table.

Update Staff table rows in other to set UserId values to related users ids (manually or create an action to do that)

Then, In your the controller, you can select newsletter from table where employee's user id equal connected User.Identity.Id. for example

var news = context.Newsletters.Where(n=>n.Staff.UserId==User.Identity.Id);
var infos = context.StaffInfos.Where(si=>si.Staff.UserId==User.Identity.Id);

in case the tables are not in relationship, you'll need to do like following

var employee = context.Staffs.FirstOrDefault(s => s.UserId == User.Identity.Id);
if(employee != null) {
    var infos = context.StaffInfos.Where(si=>si.IdEmployee==employee.EmployeeId);
    return View(infos);
} else {
    return Content("You don't have an account associated to your staff info...");
}

Please replace fields in these queries with the names of your fields.