Didn't think what I wanted was possible using queryover but sub query
performs fine for me now. HQL is another possiblilty
Anyway SQL would be something along the lines of:-
SELECT distinct m.*
FROM Movie m
inner join Actor a
on m_.Id = a.MovieId
WHERE
a_.Name like 'm%'
rather than this (what a sub query gives you)
SELECT this_.Id as Id0_0_,
this_.Name as Name0_0_,
this_.Description as Descript4_0_0_,
this_.UnitPrice as UnitPrice0_0_,
this_.Director as Director0_0_
FROM Movie this_
WHERE this_.Id in (SELECT distinct this_0_.Id as y0_
FROM Movie this_0_
inner join Actor actor1_
on this_0_.Id = actor1_.MovieId
WHERE actor1_.Name like 'm%')
To be honest I have not actually spent much time looking at the query
plan....
Thanks
Rippo
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf
Of Groch
Sent: 28 July 2011 13:00
To: nhusers
Subject: [nhusers] Re: Use the DISTINCT keyword when using QueryOver
What sql query you want to get?
On Jul 28, 1:52 pm, "Richard Wilde" <[email protected]> wrote:
> Thanks for suggestion, however this is purley a client side fix (client
side
> being web server) and not a SQL fix.
> If I introduce paging etc then this does not have effect I am looking for.
> I personally use a sub query but looking for a optimum way (if there is
such
> a thing) whilst keeping to all the goodness QueryOver provides.
>
>
>
>
>
>
>
> -----Original Message-----
> From: [email protected] [mailto:[email protected]] On Behalf
>
> Of Groch
> Sent: 28 July 2011 12:49
> To: nhusers
> Subject: [nhusers] Re: Use the DISTINCT keyword when using QueryOver
>
> Try it:
> return Session
> .QueryOver<Movie>() <-- I want distinct Movies
> .Inner.JoinQueryOver<Actor>(a => a.ActorList)
> .Where(a => a.Name.IsLike("m%"))
> .TransformUsing(Transformers.DistinctRootEntity)
> .List();
>
> On Jul 28, 11:51 am, "Richard Wilde" <[email protected]> wrote:
> > Is it possible to introduce a DISTINCT keyword into the generated SQL
> using
> > QueryOver.
>
> > return Session
>
> > .QueryOver<Movie>() <-- I want distinct Movies
>
> > .Inner.JoinQueryOver<Actor>(a => a.ActorList)
>
> > .Where(a => a.Name.IsLike("m%"))
>
> > .List();
>
> > I suppose I am looking for something like .QueryOverDistinct<Movie>()
...
>
> > If I use Projection.Distinct and project into a DTO I lose the ability
to
> > lazy load. What are my options?
>
> > Or do I have to fall back to HQL or use a sub query?
>
> > Thanks
>
> > Rippo
>
> --
> 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
athttp://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.
