prevent unnecessary cross joins in count query of generated sql code

Question

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 publicatio21_ where publicatio21_.StatementId = 22762181

Answer 1

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" ?