title: Linq to Entities Count subquery
body: <p>Okay so I've got a bunch of places which users can rate (give a score). When fetching places I also want to know how many of my friends rated that place. Users become friends by sending requests.</p>
  
  <pre><code>FRIENDREQID -- ID of user that sent the request.
  FRIENDRESPID -- ID of user that got the request
  STATUS -- Status of the request. (Accepted / Pending)
  </code></pre>
  
  <p>This query does the job.</p>
  
  <pre><code>select 
      p.*,
      (
          select 
              count(*) 
          from 
              UserRatings ur, 
              Friends f 
          where 
              ur.PlaceId = p.PlaceId
              and (ur.UserId = f.FriendReqId and f.FriendRespId = @myUserId ) 
              or  (ur.UserId = f.FriendRespId and f.FriendReqId = @myUserId ) 
              and f.Status = 'A' 
      ) as FriendCount
  from 
      Places p
  </code></pre>
  
  <p>I really struggled with coming up with a mutual query in linq to entities (I'm a beginner) and came up with this.</p>
  
  <pre><code> var result = (from p in db.Places
                   select new PlaceDTO
                   {
                       PlaceId = p.PlaceId,
                       FriendRatingCount = (from f in db.Friends
                                            from ur in db.UserRatings
                                            where ur.PlaceId == p.PlaceId &amp;&amp; 
                                                  (ur.UserId == f.FriendReqId &amp;&amp; f.FriendRespId == argUserId) ||
                                                  (ur.UserId == f.FriendRespId &amp;&amp; f.FriendReqId == argUserId) &amp;&amp;
                                                  f.Status == "A" 
                                            select f).Count()
                   })
  </code></pre>
  
  <p>Is it my friend system model that makes this query a bit bulky? Anyone has any tip of approvements that can be done too make it more efficient, easily read?</p>