title: How do I optimize this SQL query?
body: <p>The following query gets all the forum posts of a category. And I also need to display the latest comment time for each post. But the query seem to take more time to make because of it. (which makes sense).</p>
  <p>But I am not able to figure out how I can optimize it, and I'm not sure where to look for information on this.
  Any tips or solutions to my problem would be greatly appreciated.</p>
  <p><strong>Query to get the initial posts for the category</strong></p>
  <pre><code>var query = context.ctm_Forum_Post.Where(x =&gt; x.Deleted == false &amp;&amp; x.FK_Categori_ID == Id)
      .Select(x =&gt; new ForumPostModel()
      {
          Id = x.Id,
          Title = x.Title,
          BodyText = x.BodyText,
          Summary = x.Summary,
          Archieved = x.Archieved,
          Created = x.Created,
          Deleted = x.Deleted,
          MemberID = x.FK_Member_ID,
          Sticky = x.Sticky,
          Updated = x.Updated,
          CategoryId = x.FK_Categori_ID
      }).ToList();
  
  foreach (var item in query)
  {
      item.LatestCommentTime = this.GetNewestCommentDateByPost(item.Id);
  }
  return query.OrderByDescending(x=&gt; x.Created);
  </code></pre>
  <p>And for each and every post I have to make one more call to the database.</p>
  <pre><code> var query = (from comments in context.ctm_Comments
                                   join posts in context.ctm_Forum_Post on comments.Page_ID equals posts.Id
                                   where posts.Id == id &amp;&amp; comments.Deleted == false
                                   orderby comments.Reqistration_timestamp descending
                                   select comments.Reqistration_timestamp).FirstOrDefault();
  </code></pre>
  <p><strong>Comments table</strong></p>
  <pre><code> public partial class ctm_Comments
      {
          public int ID { get; set; }
          public int Page_ID { get; set; }
          public int Member_ID { get; set; }
          public string Comment { get; set; }
          public Nullable&lt;System.DateTime&gt; Reqistration_timestamp { get; set; }
          public bool Deleted { get; set; }
          public Nullable&lt;System.Guid&gt; Page_Guid { get; set; }
      }
  </code></pre>
  <p><strong>Post table</strong></p>
  <pre><code>public partial class ctm_Forum_Post
      {
          public int Id { get; set; }
          public string Title { get; set; }
          public string BodyText { get; set; }
          public string Summary { get; set; }
          public int FK_Categori_ID { get; set; }
          public bool Archieved { get; set; }
          public bool Deleted { get; set; }
          public bool Sticky { get; set; }
          public int FK_Member_ID { get; set; }
          public System.DateTime Created { get; set; }
          public System.DateTime Updated { get; set; }
      
          public virtual ctm_Forum_Category ctm_Forum_Category { get; set; }
      }
  </code></pre>