Click here to Skip to main content
15,436,765 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
How I can how do I query the two document tables and Positions through the user table get the current user userid

What I have tried:

C#
public async Task<PagedResult<DocumentsVm>> GetAllByFaculty(GetDocumentsPagingRequest request)
{
    var claimsIdentity = _httpContextAccessor.HttpContext.User.Identity as ClaimsIdentity;
    var userId = claimsIdentity.FindFirst(ClaimTypes.NameIdentifier)?.Value.ToString();
    var query = from d in _context.Documents
                join u in _context.Users on d.UserID equals u.Id
                join p in _context.Positions on u.Id equals p.UserID
                where d.UserID.ToString() == userId 
                select new { d, u,p };
    if (request.DocumentId.HasValue && request.DocumentId.Value > 0)
    {
        query = query.Where(c => c.u.UserName == request.UserName);
    }
    int TotalRow = await query.CountAsync();
    var data = await query.Skip((request.PageIndex - 1) * request.PageSize)
        .Take(request.PageSize)
        .Select(x => new DocumentsVm()
        {
            ID = x.d.ID,
            UserID = x.u.Id,
            UserName = x.u.UserName,
            Caption = x.d.Caption,
            FacultyID = x.d.FacultyOfDocumentID,
            MagazineID = x.d.MagazineID,
            CreateOn = x.d.CreateOn.Date
        }).ToListAsync();
    var pagedResult = new PagedResult<DocumentsVm>()
    {
        TotalRecord = TotalRow,
        Items = data
    };
    return pagedResult;
}
Posted
Updated 15-Mar-21 22:41pm
v2
Comments
Richard Deeming 16-Mar-21 4:42am    
What's the problem with the code you've got? What have you tried, and where are you stuck?
MarcusCole6833 7-Apr-21 14:23pm     CRLF
 var query = from d in _context.Documents                join u in _context.Users on d.UserID equals u.Id                join p in _context.Positions on u.Id equals p.UserID                where d.UserID.ToString() == userId                 select new { d, u,p };
are you asking the SQL equivalent of the LINQ I would look here for a start here https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900