Click here to Skip to main content
14,870,621 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
   
 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