LINQ-Entities Group By With Range Variable Query

Question

Here's my T-SQL which i'm trying to convert to a LINQ-Entities query....

SELECT  [a].[ModLimit], [b].[NumberOfMods]
FROM    [dbo].[Site][a]
INNER JOIN
(
    SELECT          [SiteId], ISNULL(COUNT(*),0) AS [NumberOfMods]
    FROM            [dbo].[Users][a]
    WHERE           [a].[IsMod] = 1
    GROUP BY        [SiteId]
) [b] ON [a].[SiteId] = [b].[SiteId]
WHERE   [a].[SiteId] = 1

What i'm working with so far (not compiling...)

from u in db.Set<User>()
join s in db.Set<Site>() on u.SiteId equals s.SiteId
let x = t.ModLimit
where s.SiteId == 1
group u by u.SiteId into g
select new { g,x});

Ideas?

Cutdown Structure of tables:

Site

SiteId int PK

ModLimit int

Users

UserId int PK

SiteId int FK IsMod bit

Answer 1

Here is my best attempt so far (Could you perhaps supply the structure of dbo.site & dbo.users & or some test data of what you expect to be the input?)

            List<User> users = new List<User>();
            users.Add(new User() { SiteId = 0, IsMod = 1, ModLimit = 3 });
            users.Add(new User() { SiteId = 1, IsMod = null, ModLimit = 2 });
            users.Add(new User() { SiteId = 1, IsMod = 1, ModLimit = 2 });
            users.Add(new User() { SiteId = 1, IsMod = 1, ModLimit = 2 });

            var innerSelect = users
                    .Where(o => o.IsMod != null && o.IsMod > 0)
                    .GroupBy(o => o.SiteId)
                    .Select(o => new User
                     {
                         SiteId = o.Key,
                         Counter = o.Count()
                     })
                     .ToList();

            var result = (from u in users
                          join s in innerSelect on u.SiteId equals s.SiteId
                          where u.SiteId == 1
                          select new
                          {
                              u.ModLimit,
                              NumberOfMods = s.Counter
                          })
                          .Distinct()
                          .ToList();