NHibernate - Left joins

Question

I have the following two tables:

Jobs AreaID, JobNo (composite key)

Logs LogID, AreaID, JobNo

I need to get all jobs that don't have any logs associated with them. In SQL I could do:

SELECT Jobs.AreaID,
       Jobs.JobNo
FROM   Jobs
       LEFT JOIN Logs
           ON Jobs.AreaID = Logs.AreaID
           AND Jobs.JobNo = Logs.JobNo
WHERE  Logs.LogID is null

But I'm not sure how to accomplish this with NHibernate. Could anyone offer any pointers?

Here are my mappings:

<class name="Job" table="Jobs">
    <composite-key name="Id">
        <key-property name="JobNo"/>
        <key-many-to-one name="Area" class="Area" column="AreaID"/>
    </composite-key>
</class>

<class name="Log" table="Logs">
    <id name="Id" column="LogID">
        <generator class="identity"/>
    </id>
    <property name="JobNo"/>
    <many-to-one name="Area" class="Area" column="AreaID"/>
</class>

Thanks

Update

OK, I modified Nosila's answer slightly, and this is now doing what I wanted:

Log logs = null;

return session.QueryOver<Job>()
    .Left.JoinAlias(x => x.Logs, () => logs)
    .Where(x => logs.Id == null)
    .List<Job>();

I also had to add this to my Job mapping:

<bag name="Logs">
    <key>
        <column name="JobNo"></column>
        <column name="DivisionID"></column>
    </key>
    <one-to-many class="Log"/>
</bag>

Thanks for the help. :)

The above SQL query is currently what I'm using. I'm just learning NH at the moment and am trying to convert a small application over to using it. — Tom, Feb 15 '12 at 12:48
Do you have your mappings created? Also, someone correct me if I'm wrong but I think you need NHibernate 3.2 in order to add conditions to your join (using the `QueryOver` API, anyways). — Nosila, Feb 15 '12 at 12:52

score 10 · Accepted Answer · answered Feb 15 '12 at 12:56

I'm not familiar with composite identifiers as I don't use them so for all I know NHibernate will automatically create the proper left join. None the less, the (non-tested) query below should get you started.

Job jobAlias = null;
Log logAlias = null;
YourDto yourDto = null;

session.QueryOver<Job>()
    // Here is where we set what columns we want to project (e.g. select)
    .SelectList(x => x
        .Select(x => x.AreaID).WithAlias(() => jobAlias.AreaID)
        .Select(x => x.JobNo).WithAlias(() => jobAlias.JobNo)
    )
    .Left.JoinAlias(x => x.Logs, () => logAlias, x.JobNo == logAlias.JobNo)
    .Where(() => logAlias.LogID == null)
    // This is where NHibernate will transform what you have in your `SelectList()` to a list of objects
    .TransformUsing(Transformers.AliasToBean<YourDto>())
    .List<YourDto>();

public class YourDto
{
    public int AreaID { get; set; }
    public int JobNo { get; set; }
}

Note: You need NHibernate 3.2 in order to set join conditions.

Thanks. I'm gonna have to mull over this one (there's a few errors in it). It also seems extremely complex for such a simple query. — Tom, Feb 15 '12 at 13:25
@Tom I agree with the complexity, writing this in SQL would be much simpler. I feel the ORM abstraction is getting out of hand. — Jafin, Aug 09 '12 at 04:16

Firo · Answer 2 · 2012-02-16T05:53:00.897

5

Job job = null;
var jobsWithoutLogs = session.QueryOver(() => job)
    .WithSubquery.WhereNotExists(QueryOver.Of<Log>()
        .Where(log => log.Job == job)
        .Select(Projections.Id()))
    .List()

Update: i saw you added the mapping. The Above Code only works for the following mapping

<class name="Log" table="Logs">
    <id name="Id" column="LogID">
        <generator class="identity"/>
    </id>
    <many-to-one name="Job" >
      <column name="JobNo"/>
      <column name="AreaID"/>
    <many-to-one />
</class>

edited Feb 16 '12 at 05:53

answered Feb 15 '12 at 13:02

Firo

30,626
4
55
94

Nice. I like this solution better. – Nosila Feb 15 '12 at 13:08
This does seem easier, although I'm getting the error "Cannot use subqueries on a criteria without a projection". Also, is this going to be a more expensive query since it's using subqueries instead of a simple left join? – Tom Feb 15 '12 at 13:24
1) added projection. 2) i don't think its more expensive since its doing the same thing. you could check the query plan – Firo Feb 16 '12 at 05:54
Thanks for the suggestion, but I've used Nosila's answer and modified it slightly (see my first post). :) – Tom Feb 16 '12 at 10:54

NHibernate - Left joins

2 Answers2

Linked