Asp users mvc linq app to join other tables
I am using the default user id asp.mvc5 to store and manage users in my database.
I have other db tables that will reference the users table.
The problem I'm running into is how to fetch user data when a quering
specific table has a foreign key ( userid
).
From what I've seen on the internet, I cannot directly query the users ( not best practice
) table .
Therefore, I have to use 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 look like this:
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 it sql
does not know about db_users
.
Is there a way to get around this problem?
source to share
You can add a user navigation property to the model where you are using UserId as a foreign key. When this particular item is requested, the user's data is contained.
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 request information from departments in any action
var _context = new ApplicationDbContext();
var department = _context.Departments.Include(c => c.User).FirstOrDefault();
Here I used FirstOrDefault()
to get single (primarily) item from db. You can use any suitable method as per your requirement.
Now in the department you can access the user information by simply going through department.User.FullName
or any other property of the required user
source to share
You shouldn't be doing this. You can materialize the users who are interested in the collection and join that (but then the join will be in memory), or you can put both tables in the same data context and join as usual. You shouldn't be joining data contexts as they can have inconsistent database views at query time ...
source to share