Ordering a joined collection

[reach4thelasers]

I'm in the process of re-implementing all my data queries to use
NHLambdaExtensions. I have another problem.

I have a entity 'Person' a person has a collection of Friends (also
Person entities)

I want to get the first n Friends of a particular person, ordered by
LatestLogin.

My best effort is:

public static IList<Person> GetFriends(Person person, int
count)
{
Person personAlias = null;
Person friendAlias = null;

ICriteria criteria =
NHibernateSessionManager.Instance.GetSession()
.CreateCriteria(typeof (Person), () => personAlias)
.CreateCriteria(() => personAlias.Friends, () =>
friendAlias, JoinType.LeftOuterJoin)
.AddOrder(() => friendAlias.LatestLogin, Order.Desc)
.Add<Person>(p => p.ID == person.ID)
.SetMaxResults(count);
return criteria.List<Person>();
}



In an unrelated query, why doesn't .Add<Person>(p => p == person) work
for NHLambdaExtensions?
I had to write .Add<Person>(p => p.ID == person.ID)

[Richard Brown (gmail)]

> In an unrelated query, why doesn't .Add<Person>(p => p == person) work
> for NHLambdaExtensions?
> I had to write .Add<Person>(p => p.ID == person.ID)

You have to consider how this looked in the original ICriteria (without
extensions). For example, how would you write .Add<Person>(p => p ==
person) in regular ICriteria. (I don't think you can, but I could be
wrong.)

You could try:
.Add<Person>(() => personAlias == person)
(If you meant .Add(Restrictions.Eq("personAlias", person)))

Also, just a passing point, but if you're using .Net 3.5, then the next
release of NH (3.0) will have QueryOver which has a nicer syntax again.
(But requires you to use the current 'trunk' of NH just now though.)

Hope that helps a little.

Richard

--------------------------------------------------
From: "reach4thelasers" <kev.m....@googlemail.com>
Sent: Saturday, November 07, 2009 6:04 PM
To: "NhLambdaExtensionsUsers" <nhlambdaext...@googlegroups.com>
Subject: Ordering a joined collection

[Richard Brown (gmail)]

> You could try:
> .Add<Person>(() => personAlias == person)

Actually, thinking about this a little more, I don't think that'll work
either ... I think you still need to compare by ID (because ICriteria
doesn't allow comparison of 'this'.)

The extensions allow you to write the query type safely ... but don't allow
anything you can't already do in the ICriteria API. If you can write the
query in the ICriteria API it should be more obvious how to convert it to
LambdaExtensions.

Richard

[reach4thelasers]

yeah it kinda makes sense, but its one of these things that i'd expect
something like NHlambdaextensions to consider....yes it makes sense
for Criteria queries, but for a lambda database query i'd expect p=>
p==p2

to convert to something like:

return (bool) object => object.GeyType() == object2.GetType() &&
object.object.Primarykey == object2.PrimaryKey

any ideas for my original query anyone, I'm still stuck on this.

I can do it perfectly in HQL which is

select friends from Person person inner join person.Friends friends
where person = :person order by friends.LatestLogin desc

how do I do this in Criteria/NHLambdas?!

On Nov 7, 7:59 pm, "Richard Brown \(gmail\)" <fluke...@googlemail.com>
wrote:

[Richard Brown (gmail)]

> but for a lambda database query i'd expect p=>p==p2
>
> to convert to something like:
>
> return (bool) object => object.GeyType() == object2.GetType() &&
> object.object.Primarykey == object2.PrimaryKey

I understand. However, NhLambdaExtensions sits on top of the ICriteria API
behind the scenes (so it can't easily do something not supported directly in
ICriteria). I'll have a think and see if I can figure a way to do that
though.

> any ideas for my original query anyone, I'm still stuck on this.
>

> select friends from Person person inner join person.Friends friends
> where person = :person order by friends.LatestLogin desc

I don't think that's trivial, because of the projection onto the associated
collection. It's possible with a subquery:

In ICriteria, it would be:

DetachedCriteria personFriends =
DetachedCriteria.For<Person>()
.Add(Restrictions.Eq("Id", person.Id))
.CreateAlias("Friends", "friendAlias")
.SetProjection(Projections.Property("friendAlias.Id"));

s.CreateCriteria(typeof(Person))
.Add(Subqueries.PropertyIn("Id", personFriends))
.AddOrder(Order.Desc("LastLogin"))
.List();

In NhLambdaExtensions, it would be:

Person friendAlias = null;
DetachedCriteria personFriendsLambda =
DetachedCriteria.For<Person>()
.Add<Person>(p => p.Id == person.Id)
.CreateAlias<Person>(p => p.Friends, () => friendAlias)
.SetProjection(LambdaProjection.Property(() => friendAlias.Id));

s.CreateCriteria(typeof(Person))
.Add(LambdaSubquery.Property<Person>(p => p.Id).In(personFriendsLambda))
.AddOrder<Person>(p => p.LastLogin, Order.Desc)
.List();

If that still doesn't work, then I might need to look at the mappings you're
using. If you have a back-pointer in your collection mapping, then it might
be considerable easier, but I'd need to see the mappings. It might be
simpler to consider sticking with HQL for this query.

Also, if you're still struggling to get the ICriteria to work (rather than
the syntax of NhLambdaExtensions) it might be worth posting to nhusers:
http://groups.google.com/group/nhusers nhu...@googlegroups.com

Let me know how you get on.

Cheers,
Richard