title: Linq to Entity Join and Group By
body: <p>I am new to Linq to Entity and here is my test scenario:</p>
  
  <ul>
  <li>There are Users</li>
  <li>Users have Photo Albums. An album belongs to only one User</li>
  <li>Albums have Photos. A Photo belongs to only one Album</li>
  <li>Photos have Tags. A Tag may belong to many Photos</li>
  </ul>
  
  <p><img src="https://i.stack.imgur.com/pdgTy.png" alt="Db Tables"></p>
  
  <p>I want to write a method which displays Count and Name of Tags used in Albums of a particular User using Linq.</p>
  
  <p>Here is the method that I wrote and it works fine</p>
  
  <pre><code>    public static void TestStatistics(int userId)
      {
          // select AlbumTitle, TagName, TagCount where UserId = userId
          var results = from photo in dbSet.PhotoSet
                        join album in dbSet.AlbumSet on photo.AlbumId equals album.Id into albumSet
                        from alb in albumSet
                        where alb.UserId == userId
                        join photoTag in dbSet.PhotoTagSet on photo.Id equals photoTag.PhotoId into photoTagSet
                        from pt in photoTagSet
                        join tag in dbSet.TagSet on pt.TagId equals tag.Id
                        group new { alb, tag } by new { alb.Title, tag.Name }
                            into resultSet
                            orderby resultSet.Key.Name
                            select new
                            {
                                AlbumTitle = resultSet.Key.Title,
                                TagName = resultSet.Key.Name,
                                TagCount = resultSet.Count()
                            };
  
          foreach (var item in results)
          {
              Console.WriteLine(item.AlbumTitle + "\t" + item.TagName + "\t" + item.TagCount);
          }
      }
  </code></pre>
  
  <p>And this is the standart T-SQL query which does the same</p>
  
  <pre><code>SELECT  a.Title AS AlbumTitle, t.Name AS TagName , COUNT(t.Name) AS TagCount
  FROM    TblPhoto p, TblAlbum a, TblTag t, TblPhotoTag pt
  WHERE   p.Id = pt.PhotoId AND t.Id = pt.TagId AND p.AlbumId = a.Id AND a.UserId = 1
  GROUP BY a.Title, t.Name 
  ORDER BY t.Name
  </code></pre>
  
  <p>It is pretty obvious that standard T-SQL query is much simpler than the Linq query.
  I know Linq does not supposed to be simpler than T-SQL but this complexity difference makes me think that I am doing something terribly wrong. Besides the SQL query generated by Linq is extremly complex.</p>
  
  <p>Is there any way to make the Linq query simpler?</p>
  
  <h2>UPDATE 1:</h2>
  
  <p>I made it a little simpler without using joins but using a approach like used in T-SQL. Actually it is now as simple as T-SQL. Still no navigation properties and no relations on db.</p>
  
  <pre><code>var results = from photo in dbSet.PhotoSet
                from album in dbSet.AlbumSet
                from photoTag in dbSet.PhotoTagSet
                from tag in dbSet.TagSet
                where photo.AlbumId == album.Id &amp;&amp; photo.Id == photoTag.PhotoId &amp;&amp;
                      tag.Id == photoTag.TagId &amp;&amp; album.UserId == userId
                group new { album, tag } by new { album.Title, tag.Name } into resultSet
                orderby resultSet.Key.Name
                select new {
                    AlbumTitle = resultSet.Key.Title,
                    TagName = resultSet.Key.Name,
                    TagCount = resultSet.Count()
                };
  </code></pre>