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

Do you know that it *is* hydrating publications? What does SQL Profiler say is actually being run on the server? — AakashM, Sep 09 '11 at 09:54
Thanks - sorry you are right. The joins have nothing to do with 'hydration'. I have edited the question and added some sql. — cs0815, Sep 09 '11 at 10:01
(not an nhibernate expert) the fact that it generates an implciti `CROSS JOIN` (tables in the `FROM` clause that are not explicitly joined) suggests NHibernate lacks some piece of knowledge about the relationships between objects; however, I would first want to check the SQL execution plans of its query and the ideal query - it may be that *SQL Server* is able to work out the easy thing. — AakashM, Sep 09 '11 at 10:05

score 3 · Answer 1 · edited May 23 '17 at 12:29

3

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

edited May 23 '17 at 12:29

Community

1
1

answered Sep 13 '11 at 22:41

Rafael Mueller

6,028
3
24
28

Thanks. Can you please elaborate this? I have a class BlaInteraction which contains an IList. My query maps certain properties of BlaInteraction to the view model and counts the number of publications of each BlaInteraction (TOP n). The reason for my appraoch is that I want to make full use of telerik's 'Linq-based expression engine' which generates all the 'stuff' for filtering, paging and sorting. – cs0815 Sep 14 '11 at 08:16
Added another example, check if its ok – Rafael Mueller Sep 14 '11 at 14:16
This does not work and I tried this before you posted it. The things is BlaInteraction is derived from statement. any statement has Ilist which is mapped using mapping.HasManyToMany(x => x.Publications).Table("RelationshipStatementPublications"); and i also have a dedicated RelationshipStatementPublications entity. so something like this should work: NoPublications = NHibernateSession.Current.QueryOver().Where(x => x.Statement.Id == oi.Id).Select(Projections.RowCount()).SingleOrDefault(), but i get a path exception. – cs0815 Sep 14 '11 at 15:15
please also have a look here: http://groups.google.com/group/nhusers/browse_thread/thread/d78756291f46e3e6 Thanks. – cs0815 Sep 14 '11 at 15:15

score 2 · Accepted Answer · answered Sep 15 '11 at 09:52

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

prevent unnecessary cross joins in count query of generated sql code

2 Answers2