NHibernate query problem

Question

Hi all,

these are my entities (simplified):

public class IdentificationRequest
{
    private int id;
        private ICollection<IdentificationRequestStateHistoryItem> stateHistoryItems;

        public virtual string Id
        {
            get { return id; }
            private set { id = value; }
        }                

        public virtual IEnumerable<IdentificationRequestStateHistoryItem> StateHistoryItems
        {
            get { return stateHistoryItems; }
            private set { stateHistoryItems = new List<IdentificationRequestStateHistoryItem>(value); }
        }

        public void ChangeState(IdentificationRequestState state)
        {
        // state transition here
            var stateHistoryItem = new IdentificationRequestStateHistoryItem(state, this);
            stateHistoryItems.Add(stateHistoryItem);
        }     
}


public class IdentificationRequestStateHistoryItem
{
    private int id;
        private IdentificationRequestState state;
        private EntityTimestamp timestamp;

        public virtual string Id
        {
            get { return id; }
            private set { id = value; }
        }                

    public virtual IdentificationRequestState State
    {
        get { return state; }
        private set { state = value; }
    }

    public virtual EntityTimestamp Timestamp
    {
        get { return timestamp; }
        private set { timestamp = value; }
        }
}



public class IdentificationRequestState
{
    // possible states are
    // Received, then id = 10
    // Finished, then id = 20   
    private int id; 

    private string name;

        public virtual string Name
        {
            get { return name; }
            private set { name = value; }
        }

        public virtual string Id
        {
            get { return id; }
            private set { id = value; }
        }        
}

public class EntityTimestamp
    {
        private DateTime createdOn;


        public virtual DateTime CreatedOn
        {
            get { return createdOn; }
            private set { createdOn = value; }
        }

        private IUser createdBy;


        public virtual IUser CreatedBy
        {
            get { return createdBy; }
            private set { createdBy = value; }
        }

        private DateTime changedOn;


        public virtual DateTime ChangedOn
        {
            get { return changedOn; }
            private set { changedOn = value; }
        }

        private IUser changedBy;


        public virtual IUser ChangedBy
        {
            get { return changedBy; }
            private set { changedBy = value; }
        }
}        

So the above reads like the following and is also designed like that in the database.

An IdentificationRequest can track its IdentificationRequestState transitions. So each time a new IdentificationRequestState is assigned, a new IdentificationRequestStateHistoryItem is created and put into a collection of multiple history items. That means, that the current IdentificationRequestState is the last added one (expressed by the createdOn date).

So now my question is, how a query would look like, that lists all IdentificationRequest's whos current state is Received, or Finished.

This is what I got so far:

private ICollection<IdentificationRequest> GetByCurrentState(IdentificationRequestState state)
{
    var session = GetCurrentSession();

    var subCriteria = DetachedCriteria.For(typeof(IdentificationRequestStateHistoryItem))            
        .SetProjection(Projections.Property("identificationRequest.Id" ));

    subCriteria.Add(Restrictions.Eq("State", state));

    var criteria = session
        .CreateCriteria(typeof (IdentificationRequest))
        .Add(Subqueries.PropertyIn("Id", subCriteria))
        .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer());

   return criteria.List<IdentificationRequest>();          
}

But when using this query and when search for an identification request with its current state Received, I also get requests with its current state Finished since the request had the state Received before. So I need to do something with Max(CreatedOn) or so...?

NHibernate mappings:

   <class name="IdentificationRequest" table="IdentificationRequest">

    <id name="Id" column="IdentificationRequestId" unsaved-value="-1">
      <generator class="identity"/>
    </id>

    <bag name="stateHistoryItems" access="field" order-by="CreatedOn desc" fetch="join" cascade="all-delete-orphan" lazy="false">
      <key column="IdentificationRequestId"/>
      <one-to-many class="IdentificationRequestStateHistoryItem"/>
    </bag>

  </class>


    <class name="IdentificationRequestStateHistoryItem" table="IdentificationRequestStateHistoryItem">

      <id name="Id" column="IdentificationRequestStateHistoryItemId" unsaved-value="-1">
        <generator class="identity"/>
      </id>

      <many-to-one name="identificationRequest" access="field" lazy="false" cascade="none" column="IdentificationRequestId" class="IdentificationRequest"></many-to-one>

      <many-to-one name="State" lazy="false" fetch="join" cascade="none" column="IdentificationRequestStateId" class="IdentificationRequestState"></many-to-one>

      <component name="Timestamp" class="EntityTimestamp">
        <property name="CreatedOn"/>
        <component name="CreatedBy" class="User">
          <property name="Name" column="CreatedBy" />
        </component>
        <property name="ChangedOn"/>
        <component name="ChangedBy" class="User">
          <property name="Name" column="ChangedBy" />
        </component>
      </component>

    </class>


  <class name="IdentificationRequestState" table="IdentificationRequestState">

    <id name="Id" column="IdentificationRequestStateId" unsaved-value="-1">
      <generator class="assigned"/>
    </id>

    <property name="Name"/>

  </class>    

Answer 1

You'd save yourself a lot of pain by having a IdentificationRequest.CurrentState property.

Your thought about max() is correct. You can add another subcriteria where you take the max(date) of all the requeststates for that request, and then add a restriction to your existing subcriteria such that its date = that max date. Gross, but that's the only way I can think of.