You need a new class with no joins to do what you want. No other way!! John Davidson
On Mon, Sep 12, 2011 at 8:33 AM, csetzkorn <[email protected]> wrote: > Sorry I still do not understand. This is my C# code: > > return from oi in NHibernateSession.Current.Query<BlaInteraction>() > select new BlaViewModel > { > ... > > NoPublications = oi.Publications.Count(), > > ... > }; > > I use Count() which means I do not need to populate the publications > of oi. So the following ‘sub query’ (?) which is part of the select > part of the generated sql (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 > > is far too complicated. The joins are just not necessary. Hence, > something like this sub query: > > select cast(count(*) as INT) from RelationshipStatementPublications > publicatio21_ where publicatio21_.StatementId = 22762181 > > would be enough. > > Could you please elaborate on the separate read-only class? Thanks. > > Christian > > > On Sep 12, 1:12 pm, John Davidson <[email protected]> wrote: > > The big question is why does nhibernate think that it needs to join > > statement just to count the publications and how can I prevent this? > > > > This is because you are telling it to always do the join so that you can > > navigate the hierarchy. > > > > If you do not want to navigate the hierarchy for your connoting then you > > need a separate class to do so that does not have the join mapped. It > should > > also be marked as readonly so that it does not interfere with your other > > processes. > > > > John Davidson > > > > On Mon, Sep 12, 2011 at 7:29 AM, csetzkorn <[email protected]> wrote: > > > Thanks for your reply. I am sorry but can you please elaborate on this > > > as I do not understand? I have this inheritance structure: > > > > > Statement -> Publication > > > > > Statement->X > > > > > In statement I have an IList of Publication so that any Statement > > > (e.g. X above) can be associated with a publication (obviously it > > > wouldn’t necessarily make sense to associate a publication with > > > another publication). > > > > > My fluent nhibernate mapping for statement is: > > > > > mapping.HasManyToMany(x => > > > x.Publications).Table("RelationshipStatementPublications") > > > > > I have tried things like: > > > > > mapping.HasManyToMany(x => > > > > > > x.Publications).Table("RelationshipStatementPublications").Fetch.Select().ExtraLazyLoad(); > > > > > but this did not change anything. > > > > > The big question is why does nhibernate think that it needs to join > > > statement just to count the publications and how can I prevent this? > > > IMHO it ‘thinks’ that it needs to populate the whole publication (i.e. > > > join the parent: statement) ... > > > > > Thanks. > > > > > Christian > > > > > On Sep 9, 4:20 pm, John Davidson <[email protected]> wrote: > > > > So your Publications class is mapped with a join to Statements. If > you > > > want > > > > your query to not include that inner join then you need to create > another > > > > class, say RoPublications, that is set as read only and does not have > the > > > > join, then use that class for your count. > > > > > > John Davidson > > > > > > On Fri, Sep 9, 2011 at 10:51 AM, csetzkorn <[email protected]> > wrote: > > > > > Hi, > > > > > > > Thanks for the reply. I copy and paste the question on SA below. > Not > > > > > sure why it is incomplete - please let me know why. Thanks! > > > > > > > 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 > > > > > > > On Sep 9, 2:24 pm, John Davidson <[email protected]> wrote: > > > > > > If you really want an answer here please post the details in your > > > message > > > > > > rather than in StackOverflow. I checked StackOverflow and your > query > > > > > there > > > > > > is incomplete. Please try again. > > > > > > > > John Davidson > > > > > > > > On Fri, Sep 9, 2011 at 9:01 AM, csetzkorn <[email protected]> > > > wrote: > > > > > > > Hi experts, > > > > > > > > > Could you please be so kind and have a look at this: > > > > >http://stackoverflow.com/questions/7359783/prevent-unnecessary-cross-. > > > > > .. > > > > > > > > > Thanks. > > > > > > > > > Christian > > > > > > > > > -- > > > > > > > You received this message because you are subscribed to the > Google > > > > > Groups > > > > > > > "nhusers" group. > > > > > > > To post to this group, send email to [email protected]. > > > > > > > To unsubscribe from this group, send email to > > > > > > > [email protected]. > > > > > > > For more options, visit this group at > > > > > > >http://groups.google.com/group/nhusers?hl=en. > > > > > > > -- > > > > > You received this message because you are subscribed to the Google > > > Groups > > > > > "nhusers" group. > > > > > To post to this group, send email to [email protected]. > > > > > To unsubscribe from this group, send email to > > > > > [email protected]. > > > > > For more options, visit this group at > > > > >http://groups.google.com/group/nhusers?hl=en. > > > > > -- > > > You received this message because you are subscribed to the Google > Groups > > > "nhusers" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > > [email protected]. > > > For more options, visit this group at > > >http://groups.google.com/group/nhusers?hl=en. > > > > > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
