title: LINQ query JOIN two tables for Web API controller method
body: <p>I am really poor at LINQ and can't figure out a simple problem. I have a MVC Web API that has a controller. I have a method inside the controller to return back data for comments entered by user for an item.</p>
  
  <p>The data structure is simple - Comments and User tables with UserID column acting as the foreign key</p>
  
  <p>To solve the problem, I have the following method, which has a LINQ query to do a join between Comments and User tables and return back an object in a new extended object that combines the Comments and User details. I cant seem to grab data from the User table. Can someone please help?</p>
  
  <pre><code>public IQueryable&lt;CommentsWithUserDetails&gt; GetReviewsWithUserByItem(int ID)
      {
          var query = from x in db.Comments
                      join y in db.Users on x.CommentsUserID equals y.UserID into z
                      where x.CommentsItemID.Equals(ID)
                      select new CommentsWithUserDetails
                      {
                          CommentsUserID = x.CommentsUserID,
                          CommentsText = x.CommentsText,
                          CommentsRating = x.CommentsRating,
                          CommentsDate = x.CommentsDate,
                          UserFirstName = y.FirstName,
                          UserLastName = y.LastName,
                          UserPictureURL = y.PictureURL
                      };
  
          return query;
      }
  </code></pre>