linq group by with count

Question

Hello,

Im trying to write query in linq

Select UserId, UserNumber 
FROM User
where UserNumber in
(Select UserNumber
        FROM User
        group by UserNumber
        having Count(UserId) = 1)

Aby hints ?

Answer 1

var query = from u in User
            group u by u.UserNumber into usergroup
            where usergroup.Count() == 1
            from item in usergroup
            select new { item.UserId,item.UserNumber };

Answer 2

If subqueries are your thing, you could do it this way.

var subquery =
  from u in User
  group u by u.UserNumber into g
  where g.Count() = 1
  select g.Key;

var query =
  from u in User
  where subquery.Any(num => num == u.UserNumber)
  select u;