RE: [nhusers] using isLike in a subquery for QueryOver

Richard Wilde Mon, 07 Feb 2011 01:49:40 -0800

Many thanks Richard, you were right with the distinct, thanks for pointing
this out.


I was missing the "WhereProperty" syntax in my attempts.

 

Have a great day.

 

From: [email protected] [mailto:[email protected]] On Behalf
Of Richard Brown (gmail)
Sent: 07 February 2011 09:23
To: [email protected]
Subject: Re: [nhusers] using isLike in a subquery for QueryOver

 

I think the following would work:

 

var moviesWithActor =

    QueryOver.Of<Movie>()

        .JoinQueryOver<Actor>(m => m.ActorSet)

            .Where(a => a.Name.IsLike("m%"))

            .Select(Projections.Distinct(Projections.Id()));

 

var movies =

    session.QueryOver<Movie>()

        .WithSubquery.WhereProperty(m => m.Id).In(moviesWithActor)

        .List();

 

Also, I don't think you need the 'distinct' because you're using a
sub-query, so I suspect you could write:

 

var moviesWithActor =

    QueryOver.Of<Movie>()

        .JoinQueryOver<Actor>(m => m.ActorSet)

            .Where(a => a.Name.IsLike("m%"))

            .Select(m => m.Id);

 

Let me know how you get on.

 

From: Richard Wilde <mailto:[email protected]>  

Sent: Monday, February 07, 2011 8:00 AM

To: [email protected] 

Subject: [nhusers] using isLike in a subquery for QueryOver

 

Hi,

 

I have the following detached criteria that I to convert (if possible) to
QueryOver.

Basically I am trying to get distinct movies where an actor starts with the
letter M.

The following works great

 

var ids = DetachedCriteria.For<Movie>()

     .CreateCriteria("ActorSet")

     .Add(Restrictions.Like("Name", "m%"))

     .SetProjection(Projections.Distinct(Projections.Id()));

 

var movies = session

           .CreateCriteria(typeof(Movie), "a")

           .Add(Subqueries.PropertyIn("a.id", ids))

           .List<Movie>();

 

However I am struggling to do convert this to QueryOver, can anyone help?

 

The above converts into this sql.

 

SELECT this_.Id            as Id0_0_,

       this_1_.Name        as Name0_0_,

       this_1_.Description as Descript3_0_0_,

       this_1_.UnitPrice   as UnitPrice0_0_,

       this_.Director      as Director4_0_

FROM   Movie this_

       inner join Product this_1_

         on this_.Id = this_1_.Id

WHERE  this_.Id in (SELECT distinct this_0_.Id as y0_

                    FROM   Movie this_0_

                           inner join Product this_0_1_

                             on this_0_.Id = this_0_1_.Id

                           inner join Actor actor1_

                             on this_0_.Id = actor1_.MovieId

                    WHERE  actor1_.Name like 'm%' /* @p0 */)

 

I can not seem to fathom how to include the Name.IsLike("m%")

 

Can any one help?

Thanks

Richartd

-- 
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.

RE: [nhusers] using isLike in a subquery for QueryOver

Reply via email to