Nhibernate query issue

Question

Hi !

Considering the following mapping file where TemporaryAccessOpenCommand and TemporaryAccessCloseCommand both inherit from base class Command

<class name="Command" table="xxx_Commands" lazy="false">
<id name="Id" type="Int32" unsaved-value="0">
  <generator class="identity"/>
</id>
<property name="BeginDate" />
<property name="EndDate" />
<component name="Result" class="CommandResult">
  <property name="Status" column="ResultStatus"/>
  <property name="Details" column="ResultDetails" />
</component>

<many-to-one name="Requestor" class="xxx.Domain.SessionInfo, xxx.Domain" column="SessionInfoId" lazy="false" />

<joined-subclass name="xxx.TemporaryAccess.Commands.TemporaryAccessCloseCommand, xxx.TemporaryAccess" table="xxx_Commands_TemporaryAccess_Close">
  <key column="CommandId"/>
</joined-subclass>

<joined-subclass name="xxx.TemporaryAccess.Commands.TemporaryAccessOpenCommand, xxx.TemporaryAccess" table="xxx_Commands_TemporaryAccess_Open">
  <key column="CommandId"/>
  <many-to-one name="EndUser" ...... />
  <property name="Reason"/>
  <property name="AccessRight"/>
  <property name="AccessType"/>
  <bag name="CloseAccessCommands" cascade="all" lazy="false">
    <key column="OpenCommandId"/>
    <one-to-many class="xxx.TemporaryAccess.Commands.TemporaryAccessCloseCommand, xxx.TemporaryAccess"/>
  </bag>
</joined-subclass>

What would be the nhibernate query to retrieve every OpenAccessCommand having no succeeded CloseAccessCommand ?

I tried this :

    public IList<TemporaryAccessOpenCommand> FindOpenCommandsWithoutSucceededCloseCommand()
    {
        return this.HibernateTemplate.ExecuteFind<TemporaryAccessOpenCommand>(delegate(ISession session)
        {
            return session.CreateCriteria(typeof(TemporaryAccessOpenCommand))
                .CreateCriteria("CloseAccessCommands")
                .Add(Expression.Not(Expression.Eq("Result.Status", CommandStatus.Succeeded)))
                .List<TemporaryAccessOpenCommand>();
        });
    }

But it will return an OpenAccessCommand that has tow CloseCommand (one failed and one succeeded) when it should return an empty list.

Thanks for your help (and excuse my poor english)

Answer 1

I managed to get something working this way (I'm open to suggestions if it's not the best way)

public IList<TemporaryAccessOpenCommand> FindOpenCommandsWithoutSucceededCloseCommand()
    {
        return this.HibernateTemplate.ExecuteFind<TemporaryAccessOpenCommand>(delegate(ISession session)
        {
            string sql = string.Format(@"
                                         (
                                             SELECT temp_c.OpenCommandId
                                             FROM VSA2_Commands_TemporaryAccess_Close temp_c
                                                    INNER JOIN VSA2_Commands c 
                                                        ON temp_c.CommandId = c.Id
                                             WHERE c.ResultStatus = {0}
                                         ) AS OpenCommandId"

                                        , (int)CommandStatus.Succeeded);

            var subCriteria = DetachedCriteria.For<TemporaryAccessCloseCommand>();
            subCriteria = subCriteria.SetProjection(Projections.SqlProjection(sql, new string[] { "OpenCommandID" }, new IType[] { NHibernateUtil.Int32 }));


            return session.CreateCriteria(typeof(TemporaryAccessOpenCommand))
                .Add(Expression.Eq("Result.Status", CommandStatus.Succeeded))
                .Add(Subqueries.PropertyNotIn("Id", subCriteria))
                .List<TemporaryAccessOpenCommand>();
        });
    }