Convert SQL statement with "having" clause to Linq

I've been converting my manual SQL repository that uses SqlCommand and SqlConnection to use EF4 and so far so good apart from I have got stuck trying to right a SQL statement as Linq.

I've been trying for some time and I'm not getting the right results back as when I run the query itself ( also the SQL coming from EF4 is crazy ).

Here is the query:

SELECT * FROM [Accounts] [a]
INNER JOIN (
 SELECT [a].[Id]
 FROM [Accounts] [a]
 INNER JOIN [People] [b] ON [a].[PersonId] = [b].[Id]
 INNER JOIN [Companies] [d] ON [b].[CompanyId] = [d].[Id]
 LEFT OUTER JOIN [AccountOrganizationalUnit] [c] ON [a].[Id] = [c].[AccountId]
 LEFT OUTER JOIN [OrganizationalUnits] [e] ON [c].[OrganizationalUnitId] = [e].[Id]
 WHERE [d].[GroupId] = @GroupId
 GROUP BY [a].[Id]
 HAVING SUM(CASE [e].[CompanyId] WHEN @CompanyId THEN 1 ELSE 0 END) = 0
) [t2] ON [a].[Id] = [t2].[Id]

The "having" clause is causing some trouble as I cant seem to get EF4 to create similiar SQL. @GroupId is 开发者_JS百科a Guid and CompanyId is an int.

Thanks for any help/guidance as to how I can write this in Linq! If you need more info just let me know.

Cheers,

Richard.