0

I have a query table like this, people let me ask how can I get the Positions data of the current user when I get the userid to query with the Document table.

var claimsIdentity = _httpContextAccessor.HttpContext.User.Identity as ClaimsIdentity;
        var userId = claimsIdentity.FindFirst(ClaimTypes.NameIdentifier)?.Value.ToString();
        var query = from c in _context.Documents
                    join u in _context.Users on c.UserID equals u.Id
                    join p in _context.Positions on u.Id equals p.UserID
                    where c.UserID.ToString() == userId
                    select new { c, u, p };
3
  • if the purpose is just to get Positions data of the current User and there is no filter related to Documents, there should be just 2 tables involved here: User & Position. So you don't need to join the Documents in this query, which may produce duplicate info of Position and make it harder to extract the final set of Positions. Removing the _context.Documents, you have the final query returning all Positions each of which is paired with the corresponding User. That's should be the data you want. Commented Mar 15, 2021 at 21:26
  • I want to query 2 table Document and Position through User Current Commented Mar 16, 2021 at 7:14
  • I've just added an answer to help you with that. I hope that's what you want. If not, just leave your comment to make it clearer. Again, with this kind of question, the chance for you to have an answer is much higher than other kinds of question. Because it's just query-related and many users love LINQ. But you should be more clear on the requirement, especially the format of data you want to have. Commented Mar 16, 2021 at 10:34

1 Answer 1

1

The data you query is almost enough, but it contains duplicate entries of Document and Position. If you want the final query to be put in a single object like this:

{
    User = ...,
    Documents = ...,
    Positions = ...
}

You just need to project it using Linq-to-object (because all the data is loaded and ready for projection on the client):

var result = (from document in _context.Documents
             join user in _context.Users on document.UserID equals user.Id
             join position in _context.Positions on user.Id equals position.UserID
             where document.UserID.ToString() == userId
             select new { document, user, position }).AsEnumerable()
             .GroupBy(e => e.user.Id)
             .Select(g => new {
                 User = g.First().user,
                 Documents = g.GroupBy(e => e.document.Id)
                              .Select(e => e.First().document),
                 Positions = g.GroupBy(e => e.position.Id)
                              .Select(e => e.First().position)
             }).FirstOrDefault();

If you don't want to fetch the user info, you don't need to join that DbSet but instead join the two Document and Position directly like this:

var result = (from document in _context.Documents
              join position in _context.Positions on document.UserID equals position.UserID
             where document.UserID.ToString() == userId
             select new { document, position }).AsEnumerable()
             .GroupBy(e => e.document.UserID)
             .Select(g => new {
                 Documents = g.GroupBy(e => e.document.Id)
                              .Select(e => e.First().document),
                 Positions = g.GroupBy(e => e.position.Id)
                              .Select(e => e.First().position)
             }).FirstOrDefault();

Note that I suppose your Document and Position both have its own primary key property of Id (adjust that to your actual design). Finally, usually if your User entity type exposes navigation collection properties to the Document and Position. We can have a better (but equal) query like this:

var user = _context.Users
                   .Include(e => e.Documents)
                   .Include(e => e.Positions)
                   .FirstOrDefault(e => e.Id.ToString() == userId);

It's much simpler because all the joining internally translated by the EFCore. The magic is embedded right into the design of navigation collection properties.

I would like to talk about the important note of the condition UserID.ToString() == userId or Id.ToString() == userId. You should avoid that because it would be translated into a query that breaks the using of index for filtering. Instead try parsing for an int userId first (looks like it's a string in your case) and use that parsed int directly for comparison in the query, like this:

if(!int.TryParse(userId, out var intUserId)){
    //return or throw exception
}
                           
//here we have an user id of int, use it directly in your query
var user = _context.Users
                   .Include(e => e.Documents)
                   .Include(e => e.Positions)
                   .FirstOrDefault(e => e.Id == intUserId);

That applies similarly to other queries as well.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.