Linq query for a nested select statement with grouping and distinct

Question

I'd like to translate the following SQL statement into a linq query:

select COUNT(*), itemid,  globalid, title, preview, previewimage, previewimage_alt, link  
from (
        select distinct Id, itemid,  globalid, title, preview, previewimage, previewimage_alt,
               (select top 1 link from LikeCounter where GlobalId=x.GlobalId) as link
        from [LikeCounter] x
        where PortalId=1 and LanguageId=1
    ) as t
GROUP BY itemid, globalid, title, preview, previewimage, previewimage_alt, link
ORDER BY COUNT(*) desc

The query is over a view that holds records of objects being "liked". Since the objects can be published in multiple places, and the view was setup to allow for filtering for a certain place, it requires a distinct before grouping the records to find out the view count (that's the reason for the additional query for the "link" column).

Is a nested SELECT statement possible in one linq statement?

The inner query is no problem:

(from x in LikeCounter
where x.PortalId==1 && x.LanguageId==1  
select new {x.Id, x.ItemId, x.GlobalId, x.LanguageId, x.Title, x.Preview, x.PreviewImage_alt, 
              Morelink=(from y in LikeCounter
              where y.GlobalId==x.GlobalId
              select y.Morelink).FirstOrDefault()
           }).Distinct()

But is there a way to extend this with the grouping of the distinct records, that results in just one query to the database ?

Thanks in advance for any input...

Nina

Edit:

the following query almost returns what I want -- but produces multiple queries to the SQL server:

(from y in 
((from x in LikeCounter
where x.PortalId==1 && x.LanguageId==1 
select new {x.Id, x.ItemId, x.GlobalId, x.LanguageId, x.Title, x.Preview, x.PreviewImage_alt, 
              Link=(from y in Xparo_LikeCounter
              where y.GlobalId==x.GlobalId
              select y.Link).FirstOrDefault()
           }).Distinct())
group y by y.GlobalId into grp
select new {Data=grp, Count= grp.Count()}).OrderByDescending (x => x.Count)

Answer 1

I Think the below should work but i can't really test it. No idea how many queries it would take either

from subq in (from x in LikeCounter
              where x.PortalId==1 && x.LanguageId==1  
              select new {x.Id, x.ItemId, x.GlobalId, x.LanguageId, x.Title, x.Preview, x.PreviewImage_alt, 
              Morelink=(from y in LikeCounter
              where y.GlobalId==x.GlobalId
              select y.Morelink).FirstOrDefault()
              }).Distinct()
group subq by new {TheCount = subq.Id.Count(), subq.Id, subq.ItemId, subq.GlobalId, subq.LanguageId, subq.Title, subq.Preview, subq.PreviewImage_alt, subq.Morelink } into grouped
order by grouped.TheCount descending;