ASP.NET MVC application users linq to join to other tables

Question

I am using default ASP.NET MVC 5 users identity to store and manage users in my database.

I have other database tables that will be referencing the users table.

The problem I am facing is how to extract user details when querying a certain table that has a foreign key (userid).

From what I have seen online, I cannot directly query the users table ("not best practice").

So I must use the ApplicationDbContext to get the list of users:

var userContext = new ApplicationDbContext();
var db_users = userContext.Users.Select(x => new UserSearchResult() 
{
  ApplicationUserId = x.Id,
  Email = x.Email,
  Username = x.UserName,
  Fullname = x.FullName
});

Then my linq query would be for example:

var query = (from dep in Dbcontext.Departments
             from usr in db_users.Where(x => x.ApplicationUserId == dep.HodUserId).DefaultIfEmpty()
             join cat in Dbcontext.Categories on dep.CategoryId equals cat.CategoryId
             select new DepartmentSearchResult() 
             {
               DepartmentId = dep.DepartmentId,
               DepartmentName = dep.DepartmentName,
               HodName = usr.Fullname,
               CategoryName = cat.CategoryName
             });

However, the above will not work as SQL does not know about the db_users.

Is there a way to get around this issue?

Answer 1

You can add a navigation property of the User in the model you are using UserId as the foreign key. When querying that particular item include the user details.

say in your Department model

public class Department
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ApplicationUser User { get; set; } //Navigation Property
        public string UserId { get; set; }  //Foreign Key
    }

When you are querying Departments information in any action

var _context = new ApplicationDbContext();
var department = _context.Departments.Include(c => c.User).FirstOrDefault();

Here I used FirstOrDefault() to get a single(first to be exact) item from db. You can use any appropriate method as per your requirement.

Now in department, you can access User information by simply going through department.User.FullName or whatever property of the user you need