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 Articles