The steps to do this are fairly simple, but I do not have the details of your application or data model to provide you the exact code.
1. Create a view based on the RelationshipStatementPublications table. Call it ViewRelationshipStatementPublications. 2. Map it to a new class ViewPublications. Ensure the mapping is marked mutable=false. 3. Change your code for doing the count to now go against the repository for ViewPublications. You really need to do this yourself so that you learn how to do it. John Davidson On Tue, Sep 13, 2011 at 3:26 AM, csetzkorn <[email protected]> wrote: > Thanks. Could you please provide some more details for my situation > (class code, [fnh] mapping). Thanks! > > On Sep 12, 1:51 pm, John Davidson <[email protected]> wrote: > > 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. > > -- 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.
