prevent unnecessary cross joins in count query of generated sql code

I am using this query:

return from oi in NHibernateSession.Current.Query<BlaInteraction>()
select new BlaViewModel
{
  ...

  NoPublications = oi.Publications.Count(), 

  ...
};

BlaInteraction contains an IList of publications (i.e. entities). To determine the number of publications one does not really need to do all the joins for a publication. Can I prevent nhibernate from using joins in the generated sql (e.g. using projection???) somehow?

Thanks.

Christian

PS:

This is what NH produces (slightly adapted):

select cast(count(*) as INT) from RelationshipStatementPublications publicatio21_, Publication publicatio22_ inner join Statements publicatio22_1_ on publicatio22_.StatementId=publicatio22_1_.DBId where publicatio21_.StatementId = 22762181 and publicatio21_.PublicationId=publicatio22_.StatementId

This is what would be sufficient:

select cast(count(*) as INT) from RelationshipStatementPublications publ开发者_运维百科icatio21_ where publicatio21_.StatementId = 22762181

---

Why can't you just create another query ?

Session.QueryOver<Publication>().Where(x => x.BlaInteractionId == idSentAsParameter).Select(Projections.RowCount()).SingleOrDefault<int>();

I think that's will work

return from oi in NHibernateSession.Current.Query<BlaInteraction>()
select new BlaViewModel
{
  ...
  NoPublications = Session.QueryOver<Publication>().Where(x => x.BlaInteractionId == oi.Id).Select(Projections.RowCount()).SingleOrDefault<int>();

  ...
};

Another edit, have you tried lazy="extra" ?

---

Ok the best solution I have found so far is to use a FNH Formula:

mapping.Map(x => x.NOPublications).Formula("(select count(distinct RelationshipStatementPublications.PublicationId) from RelationshipStatementPublications where RelationshipStatementPublications.StatementId = DBId)");

public virtual int NOPublications {get; private set;}

when I map from the domain to the view model I use:

NoPublications = oi.NOPublications,

Christian