Multi table COUNT in NHibernate with joined subclasses

Question

I have been trying to find a way to do this query using a NHibernate criteria (preferred) or HQL, with no luck.

Here is the query:

select COUNT(sa.Id) from Accounts a
join Sources s on a.Id = s.Account_Id
join SpecialArticles sa on sa.SpecialSource_Id = s.Id

Notes:

1. SpecialSource is subclass of Source and is mapped as a JoinedSubClass
2. SpecialArticle is a subclass of Article and is mapped as a JoinedSubClass

Here is the class model psudo-code:

class Account
{
    Guid _id;
}

class Source
{
    Guid _id;
    Account _account;
}

class Article
{
    Guid _id;
    Source _source;
}

class SpecialSource : Source
{
    Guid _id;
    // ... subclass specific fields
}

class SpecialArticle : Article
{
    Guid _id;
    // ... subclass specific fields
}

And the aim is basically to count the number SpecialArticles for an Account.

Any help is appreciated!

Answer 1

If the aim is to count the number of SpecialArticles for a given Account you may try this query:

int count = session
    .CreateCriteria<SpecialArticle>()
    .CreateAlias("Source", "source")
    .CreateAlias("source.Account", "account")
    .Add(Expression.Eq("account.Id", accountId))
    .SetProjection(Projections.Count(Projections.Id()))
    .UniqueResult<int>();