Nhibernate 3 Linq - inner joins

Question

I'm testing out nhibernate 3 CR, but fails to 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 have not got so far, my current code is like this and gives me something complete 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;

Is there any resources on how to use inner joins with linq? And what should I do with:

order by    count(release.Status)

Is this something that should be done with QueryOver instead?

Why do you define relationships between entities in the queries instead of in the mapping? — Paco, Nov 25 '10 at 13:14
I was just testing linq on a legacy application. So this is how the SQL looked like. I can smell a rewrite of the mappings, but the point here was to see how far Linq to nhibernate had evolved. And it looks like it's not very usable. — bondehagen, Nov 26 '10 at 09:42

Diego Mijelshon · Answer 1 · 2010-11-25T14:28:20.713

6

First, define relationships in your model instead of trying to join by id.

Then you'll be able to do this:

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

All that's missing is the orderby, which I don't think is correct (you are trying to order by an aggregate, but you're not specifying a group by)

edited Nov 25 '10 at 14:28

answered Nov 25 '10 at 13:34

Diego Mijelshon

52,548
16
116
154

This causes to implicit outer join and is not efficeint as inner join – Saeed Amiri Sep 06 '11 at 12:32

score 4 · Answer 2 · answered Nov 25 '10 at 13:25

AFAIK, NH still doesn't support joins in linq well. So better to use HQL, or even QueryOver (I think for simple queries it's the best). Look at this example:

// 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();

Nhibernate 3 Linq - inner joins

2 Answers2