score:1

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

Related Query

More Query from same tag