NHibernate QueryOver with leftjoins

Question

This thing is keeping me busy for days and I hope someone of the NHibernate gurus can help me out. I've got a query set up which is working in SQL Server and I want to get the same in NHibernate. But all my tries (did a lot of googeling and browsing in stackoverflow) failed so far. Here's the query:

Select
  j.id,
  j.title,
  j.company,
  jt.name as category,
  loc.city,
  je.is_assigned,
FROM job j
  LEFT JOIN location loc ON loc.id = j.location
  LEFT JOIN job_tag jt ON jt.job = j.id
    and jt.name in (SELECT name FROM tag WHERE tag_category=''Jobtype'')
  LEFT JOIN job_employee je ON je.job = j.id
    and je.employee_uid = <string>

I would prefer to use QueryOver but would be happy to get recommendations from you guys!

EDIT: What I do have so far is pretty simple...

JobEmployee jobEmployee = null;
Location loc = null;
JobTag jobTag = null;

session.QueryOver<Job>()
  .JoinAlias(x => x.location, () => loc)
  .JoinAlias(x => x.tags, () => jobTag, JoinType.LeftOuterJoin,
     Restrictions.On(jobTag.name).IsIn(<subquery>))
  .List();

But there are errors like "delegate .... does not take 1 arguments".

Best regards, Martin

Look, query over syntax for this scenario is pretty well described here nhforge.org/doc/nh/en/index.html#queryqueryover. It would be nice if you can do something. Basic JOIN, SELECT, WHERE - show what you have, and describe what is not working ... if anything. It'll be a much more easier for someone NHibernate-experienced to give you help. — Radim Köhler
I have no issue with basic NHibernate usage. I know how to set up selects, where clauses etc. But I don't have an idea how to set the condition to the left join. Hope this makes sense to you. — Martin Horvath
Great job. More errors would help, but let me check this. Or maybe some other will give the answer... — Radim Köhler

Radim Köhler Radim Köhler · Accepted Answer · 2014-03-25T15:43:31

Here is a draft of the solution... First the subquery:

var subquery = QueryOver
    .Of<Tag>()
    .Where(t => t.TagCategory == "Jobtype")
    .Select(t => t.Name);

And now, we will use the subquery in the withClause:

JobEmployee jobEmployee = null;
Location loc = null;
JobTag jobTag = null;

var list = session
  .QueryOver<Job>()
  .JoinAlias(x => x.location, () => loc)
  .JoinAlias(x => x.tags, () => jobTag, JoinType.LeftOuterJoin,

  // instead of this
  // Restrictions.On(jobTag.name).IsIn(<subquery>))

  // use this
    Subqueries.WhereProperty(() => jobTag.name).In(subquery)
  )
  .List();

So, we filtered the JOIN using the withClause and Subqueries

NHibernate QueryOver with leftjoins

1 Answers