score:3

Accepted answer
var result = from a in accounts
             where a.id != 2
             select new { count = (from x in signs
                                   from y in signs
                                   where x.accountid == 2 &&
                                   y.accountid == a.id &&
                                   x.docid == y.docid
                                   select 1).count(),
                          a };

note: you could probably change the subquery to a join on docid but i've left as is, so you can see the similarity between the sql and the linq.

example with a join:

var result = from a in accounts
             where a.id != 2
             select new { count = (from x in signs
                                   join y in signs on x.docid equals y.docid
                                   where x.accountid == 2 &&
                                   y.accountid == a.id
                                   select 1).count(),
                          a };

Related Query

More Query from same tag