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.
Positionsdata of the current User and there is no filter related toDocuments, there should be just 2 tables involved here:User&Position. So you don't need to join theDocumentsin this query, which may produce duplicate info ofPositionand make it harder to extract the final set ofPositions. Removing the_context.Documents, you have the final query returning allPositionseach of which is paired with the correspondingUser. That's should be the data you want.