Linq to Entity Join and Group By

Question

I am new to Linq to Entity and here is my test scenario:

• There are Users
• Users have Photo Albums. An album belongs to only one User
• Albums have Photos. A Photo belongs to only one Album
• Photos have Tags. A Tag may belong to many Photos

I want to write a method which displays Count and Name of Tags used in Albums of a particular User using Linq.

Here is the method that I wrote and it works fine

    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);
        }
    }

And this is the standart T-SQL query which does the same

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

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.

Is there any way to make the Linq query simpler?

UPDATE 1:

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.

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 && photo.Id == photoTag.PhotoId &&
                    tag.Id == photoTag.TagId && 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()
              };

Answer 1

If every photo has at least one tag , then try

var results = (from r in PhotoTag 
    where r.Photo.Album.UserID == userId
    group r by new { r.Photo.Album.Title, r.Tag.Name } into resultsSet
    orderby resultsSet.Key.Name 
    select new 
    { 
        AlbumTitle = resultsSet.Key.Title , 
        TagName    = resultsSet.Key.Name , 
        TagCount   = resultsSet.Count() 
    }
    );

Answer 2

First things first, you need to setup foreignkeys in your database then rebuild EF and it will 'know' (i.e. navigation properties) about the relationships, which then allows you to omit all of your joins and use something along the lines of the following:

List<AlbumTag> query = (from ps in dbSet.PhotoSet
                         where ps.Album.UserId = userId
                         group new { album, tag } by new { ps.Album.Title, ps.PhotoTag.Tag.Name } into resultSet
                         orderby resultSet.Key.Name
                         select new AlbumTag()
                         {
                           AlbumTitle = resultSet.Key.Title,
                           TagName = resultSet.Key.Name,
                           TagCount = resultSet.Count()
                         }).ToList();