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,
Maybe that's what you mean?
Christian
On Sep 15, 7:13 am, csetzkorn <[email protected]> wrote:
> Thanks Gunnar. I think that's what John Davidson was getting at as
> well. Could you please be so kind and provide more details on this or
> any other refrences.
>
> I find that the problem with nhibernate is that you get simple things
> to work just fine. However, as soon as it gets a bit more complicated
> you are lost between lots of highly scatered information on the
> Internet. Any offical books and even the online documentation only
> show very simple examples. Sorry about the rant but that's why some
> more feedback from you experts would be very good.
>
> Thanks.
>
> Christian
>
> On Sep 14, 5:00 pm, Gunnar Liljas <[email protected]> wrote:
>
> > No, having RelationshipStatementPublication as an entity doesn't harm, but
> > you're not actually using it anywhere, are you? It seems to be completely
> > unreferenced.
>
> > Sure,
>
> > select cast(count(*) as INT) from
> > RelationshipStatementPublicati
> > ons publicatio21_ where
> > publicatio21_.StatementId = 22762181
>
> > ...would be enough, but it's not correct. The LINQ query acts upon
> > BlaInteractions, and that means that it will and should honor the
> > referential integrity that the mapping implies.
>
> > If you really want it to query like that, add a mapping of
> > RelationshipStatementPublication, make a readonly mapping of its statementid
> > column and query on that.
>
> > /G
>
> > 2011/9/14 csetzkorn <[email protected]>
>
> > > Thanks for the reply.
>
> > > "child of statement" - yes I meant “derives from statement”
>
> > > I don't think having RelationshipStatementPublication as entity
> > > harms ... or does it? Or maybe that’s the whole problem see sql below?
>
> > > In terms of hydrating ... I said this because nhibernate seem to make
> > > all these unnecessary joins (to hydrate publications?) just for a
> > > count. Looking at the original sub query (part of the select
> > > statement):
>
> > > 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
>
> > > it joins RelationshipStatementPublications to publication and so on
> > > (Publication also derives from statement by the way).
>
> > > This would be enough:
>
> > > select cast(count(*) as INT) from
> > > RelationshipStatementPublications publicatio21_ where
> > > publicatio21_.StatementId = 22762181
>
> > > On Sep 14, 12:11 pm, Gunnar Liljas <[email protected]> wrote:
> > > > When you say "child of statement", do you mean that it "derives from
> > > > statement"?
>
> > > > The RelationshipStatementPublication class (in strict terms, not an
> > > entity)
> > > > seems unnecessary now. According to the mapping , it seems you are not
> > > using
> > > > it anyway.
>
> > > > Whey you say hydrates, do you really mean that? The initial SQL query
> > > > you
> > > > showed does not indicate any hydrating.
>
> > > > /G
>
> > > > 2011/9/14 csetzkorn <[email protected]>
>
> > > > > Ok let me try to explain this again – maybe I was not very clear at
> > > > > the beginning. Sorry!
>
> > > > > I have a class BlaInteraction which is a child of statement. Each
> > > > > statement has an IList<Publication>. This relationship is also part of
> > > > > the domain model to make it future proved:
>
> > > > > public class RelationshipStatementPublication : Entity
> > > > > {
> > > > > public virtual Statement Statement { get; set; }
> > > > > public virtual Publication Publication { get; set; }
> > > > > }
>
> > > > > In my statement mapping I have:
>
> > > > > mapping.HasManyToMany(x =>
> > > > > x.Publications).Table("RelationshipStatementPublications");
>
> > > > > My ‘naive’ approach to map a BlaInteraction to a viewmodel and count
> > > > > the number of publications for each BlaInteraction is this:
> > > > > return from oi in NHibernateSession.Current.Query<BlaInteraction>()
> > > > > select new BlaViewModel
> > > > > {
> > > > > ...
> > > > > NoPublications = oi.Publications.Count(),
> > > > > ...
> > > > > };
>
> > > > > This does not scale as hnibernate creates sql code that hydrates each
> > > > > publication of each (TOP n) BlaInteraction to JUST count the number of
> > > > > publications. This is what I would like to prevent as removing the
> > > > > ‘hydration joins’ by hand optimising the sql code scales things.
>
> > > > > Please note that I do not want to use SQL as I am trying to use IQuery
> > > > > to make use of Telerik's 'Linq-based expression engine' which
> > > > > generates all the 'stuff' for filtering, paging and sorting.
>
> > > > > Another recent (failed) attempt was this:
>
> > > > > return from oi in NHibernateSession.Current.Query<BlaInteraction>()
> > > > > select new BlaViewModel
> > > > > {
> > > > > ...
> > > > > //NoPublications =
> > > > > NHibernateSession.Current.QueryOver<BlaInteraction>().Where(x =>
> > > > > x.Statement.Id ==
> > > > > oi.Id).Select(Projections.RowCount()).SingleOrDefault<int>(),
> > > > > NoPublications = NHibernateSession.Current.CreateSQLQuery("select
> > > > > count(distinct PublicationId) from
> > > > > dbo.RelationshipStatementPublications where StatementId
> > > > > = :StatementId").SetParameter("StatementId",
> > > > > oi.Id).UniqueResult<int>(),
> > > > > ...
> > > > > };
>
> > > > > The stuff commented out works but does not make sense. I tried this
> > > > > due to a recent SO comment.
>
> > > > > Thanks.
>
> > > > > Christian
>
> > > > > On Sep 14, 11:08 am, Gunnar Liljas <[email protected]> wrote:
> > > > > > 1. The join is correct. You ask for the number of publications
> > > belonging
> > > > > to
> > > > > > a specific BlaInteraction. That is what the SQL expresses.
> > > > > > 2. Is it really important?
> > > > > > 3. If it is, add the interaction id as a readonly property on the
> > > > > > publication, and query on that.
>
> > > > > > /G
>
> > > > > > 2011/9/9 csetzkorn <[email protected]>
>
> > > > > > > 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.
