I reformatted the query:
SELECT Count(distinct ClientID)
FROM
Mailbox
Inner Join Message_MailBox
ON Mailbox.ID=Message_MailBox.MailBoxId AND IncomingOutgoing = 1 AND NOT ClientID IS NULL
WHERE
Message_MailBox.MessageId IN
( SELECT Message.ID
FROM Message
INNER JOIN Message_MailBox
ON Message.ID = Message_MailBox.MessageId
WHERE IncomingOutgoing = 2
AND MailBoxId IN
( SELECT Id FROM Mailbox
WHERE NOT MatchMakerID IS NULL)
AND ( SentOn BETWEEN'2011-01-31' AND '2011-09-08' )
)
You have to divide an conquer. Your query is not that complicated, you just need what to use. For example:
SELECT Count(distinct ClientID)
From this you know you are fetching an aggregate. So you can use GetScalar.
Inner Join Message_MailBox
ON Mailbox.ID=Message_MailBox.MailBoxId AND IncomingOutgoing = 1 AND NOT ClientID IS NULL
This is a special relation because you are adding a custom condition. So here you need Custom filters for EntityRelations
Message_MailBox.MessageId IN
( SELECT Message.ID
FROM Message
INNER JOIN Message_MailBox
ON Message.ID = Message_MailBox.MessageId
WHERE IncomingOutgoing = 2
...
This is a FieldCompraSetPredicate.
... AND MailBoxId IN
( SELECT Id FROM Mailbox
WHERE NOT MatchMakerID IS NULL)
AND ( SentOn BETWEEN'2011-01-31' AND '2011-09-08' )
Finally, this is another FieldCompareSetPredicate but inside the subfilter in the above filter. So you will end with a nested FieldCompareSetPredicate.
So, give it a try with those hints. If you get stuck somewhere post the code you wrote so far and we will help you to finish it.
