title: Get UserIdentity and Role with related tables in one SQL query in ASP .NET Core 3.1
body: <p>I'm trying to get data from some tables in one SQL query and fill in a model.
  Things that are important:</p>
  <ul>
  <li>Count of all blogs</li>
  <li>List of all blogs created by the user</li>
  <li>Count of all interviews</li>
  <li>List of all interviews created by the user</li>
  <li>Count of all candidates assigned to the Candidate role</li>
  <li>List of all candidates created by the user</li>
  </ul>
  <pre><code>public ApplicationUserActivitiesVM GetUserActivities(string userId)
  {
      var result = from dummyRow in new List&lt;string&gt; { &quot;X&quot; }
                      join blog in _context.Blog.Where(w =&gt; w.Activity == true) on 1 equals 1 into bg
                      join ublog in _context.Blog.Where(w =&gt; w.Activity == true &amp;&amp; w.AuthorId == userId) on 1 equals 1 into ubg
                      join interview in _context.CandidateInterview.Where(w =&gt; w.Activity == true) on 1 equals 1 into iw
                      join uinterview in _context.CandidateInterview.Where(w =&gt; w.Activity == true &amp;&amp; (w.CreatorId == userId || w.InterviewerId == userId)).Include(i =&gt; i.Candidate) on 1 equals 1 into uiw
                      join candidate in _context.UserRoles.Where(w =&gt; w.RoleId == &quot;candidate role id&quot;) on 1 equals 1 into ce
                      join ucandidate in _context.Users.Where(w =&gt; w.UserCreatorId == userId &amp;&amp; w.Activity == true).Include(i =&gt; i.CandidateInterviewCandidates).ThenInclude(t =&gt; t.Candidate) on 1 equals 1 into uce
                      select new ApplicationUserActivitiesVM()
                      {
                          BlogsCount = bg.Count(),
                          CandidatesCount = ce.Count(),
                          InterviewsCount = iw.Count(),
                          UserBlogsCount = ubg.Count(),
                          UserInterviewsCount = uiw.Count(),
                          UserCandidatesCount = uce.Where(w =&gt; ce.Any(c =&gt; c.UserId == w.Id)).Count(),
                          UserInterviews = uiw.Select(s =&gt; new UserActivityInterview(s)).ToList(),
                          UserBlogs = ubg.Select(s =&gt; new UserActivityBlog(s)).ToList(),
                          UserCandidates = uce.Where(w =&gt; ce.Any(c =&gt; c.UserId == w.Id)).Select(s =&gt; new UserActivityCandidate(s)).ToList()
                      };
  
      return result.FirstOrDefault();
  }
  </code></pre>
  <p>the last join prepares all users created by the user I pass to the method but I would like to get all users in candidate role that were created by the given userId.</p>
  <p><strong>Is it any way to improve my query?</strong></p>
  <p>To reach that goal I used <code>Where(w =&gt; ce.Any(c =&gt; c.UserId == w.Id))</code> twice in select segment. I think it is not an optimise way.</p>
  <p>Any help in optimizing the code will be appreciated.</p>
  <p><strong>After reading the comments, I edited the code as follows:</strong></p>
  <p>ViewModel that I use on a page:</p>
  <pre><code>public class ApplicationUserActivitiesVM
  {
      public int BlogsCount { get; set; }
      public int CandidatesCount { get; set; }
      public int InterviewsCount { get; set; }
          
      public IList&lt;UserActivityBlog&gt; UserBlogs { get; set; }
      public IList&lt;UserActivityCandidate&gt; UserCandidates { get; set; }
      public IList&lt;UserActivityInterview&gt; UserInterviews { get; set; }
  
      public int UserBlogsCount =&gt; UserBlogs.Count;
      public int UserCandidatesCount =&gt; UserCandidates.Count;
      public int UserInterviewsCount =&gt; UserInterviews.Count;
  }
  </code></pre>
  <p>Method that I edited:</p>
  <pre><code>public ApplicationUserActivitiesVM GetUserActivities(string userId)
  {
      var blogsCount = _context.Blog.Where(w =&gt; w.Activity == true).Count();
      var userBlogs = _context.Blog.Where(w =&gt; w.Activity == true &amp;&amp; w.AuthorId == userId).Select(s =&gt; new UserActivityBlog(s)).ToList();
      var interviewsCount = _context.CandidateInterview.Where(w =&gt; w.Activity == true).Count();
      var userInterviews = _context.CandidateInterview.Where(w =&gt; w.Activity == true &amp;&amp; (w.CreatorId == userId || w.InterviewerId == userId)).Include(i =&gt; i.Candidate).Select(s =&gt; new UserActivityInterview(s)).ToList();
      var candidates = _context.UserRoles.Where(w =&gt; w.RoleId == &quot;candidate role id&quot;);
      var candidatesCount = candidates.Count();
      var userCandidates = _context.Users.Where(w =&gt; w.UserCreatorId == userId &amp;&amp; w.Activity == true).Where(w =&gt; candidates.Any(a =&gt; a.UserId == w.Id)).Include(i =&gt; i.CandidateInterviewCandidates).ThenInclude(t =&gt; t.Candidate).Select(s =&gt; new UserActivityCandidate(s)).ToList();
  
      var result = new ApplicationUserActivitiesVM
      {
          BlogsCount = blogsCount,
          UserBlogs = userBlogs,
          InterviewsCount = interviewsCount,
          UserInterviews = userInterviews,
          CandidatesCount = candidatesCount,
          UserCandidates = userCandidates
      };
  
      return result;
  }
  </code></pre>
  <p>In terms of performance, I compared the two methods and discovered that they are nearly identical; however, given the database currently has little data, it may need to be enhanced in the future and compared again.</p>
  <p>Please let me know if there is a better way.</p>