Nhibernate 3 Linq - Internal Connections

Question

I am testing nhibernate 3 CR but could not create the following SQL using Linq:

select   *
     from        Users               as {user}
     inner join  Test                as test  on test.UserId   = user.Id
     inner join  Release             as release on release.TestId = test.TestId
     where Release.Status = 1
     order by    count(release.Status) desc;

I don't have one yet, my current code is similar to this and gives me something completely different:

var users = from user in Session.Query<User>()
            join test in Session.Query<Test>() on user.Id equals test.User.Id
            join release in Session.Query<Release>() on test.Id equals release.Test.Id
            where release.Status == 1
            orderby release.Status
            descending 
            select user;

Are there any resources on how to use internal connections with linq? What am I supposed to do:

order by    count(release.Status)

Is this something you need to do with QueryOver?

Answer 1

Define relationships in your model first, not try to join id.

Then you can do it:

from release in session.Query<Release>()
where release.Status == 1
select release.Test.User

All that is missing is an order that I don’t think is right (you are trying to sort by order, but you are not specifying a group)

Answer 2

AFAIK, NH - linq. HQL QueryOver ( , ). :

// just to assign aliases, not for real use:
Test qtest = null;
Release qrel = null;

// do query
var data = Session.QueryOver<User>()
  .JoinAlias(quser => quser.Tests, () => qtest)
  .JoinAlias(quser => quser.Releases, () => qrel)
  .Where(() => qrel.Status == 1)
  .OrderBy(() => qrel.Status).Desc
  .List();