NHibernate - create criteria based on a property that is a list.

Question

Hello, I have a class called LoanApplication, and it has a collection property set up called Workflow. In the mapping file, I am setting the order of retrieval of the Workflow records to sort by Date, so the current workflow is always the first item in the list.

Now I want to query by the current workflow to get LoanApplications that are in a specific workflow step using the Criteria API. I'm not really sure how to do this. Here is how I am mapping the Workflow collection:

<bag name="ApplicationWorkflow" table="PreApplication.ApplicationWorkflow" generic="true" inverse="true" order-by="StartDate DESC"
  cascade="all" lazy="true">
  <key column="ApplicationID" />
  <one-to-many class="ApplicationWorkflow" />
</bag>

Here is how I am retrieving applications (this is where I need to add the filter by Current Workflow functionality):

    public IList<Model.PreApplication.Application> GetCompletedApplications()
    {
        IList<Model.PreApplication.Application> result = null;

        using (ITransaction transaction = this.Session.BeginTransaction())
        {
            result = this.Session.CreateCriteria<Model.PreApplication.Application>()
                .AddOrder(new Order("EnteredDate", false))
                .List<Model.PreApplication.Application>();

            transaction.Commit();
        }

        return result;
    }

Thanks for any help!

Answer 1

I suggest to add simply reference to last/active Workflow. Criteria will be much more simpler :)

public void AddLatestWorkflow(Workflow wf)
{
    this.Workflows.Add(wf);
    this.LatestWorkflow = wf;
}

Answer 2

So you need to list Applications whose current Workflow is at a specific step? You can use a sub-query to join only the current workflow and then restrict to a specific step.

the desired SQL...

select
    ...
from
    Application app
    inner join ApplicationWorkFlow currWorkFlow on app.id = currWorkFlow.application_id
where
    currWorkFlow.id = (
        select top 1 topFlow.id
        from ApplicationWorkFlow topFlow
        where topFlow.application_id = app.id
        order by topFlow.StartedDate desc
    )
    and currWorkFlow.step = @step

the Criteria to get you there...

session.CreateCriteria<Application>("app")
    .CreateAlias("ApplicationWorkFlow", "currWorkFlow", NHibernate.SqlCommand.JoinType.InnerJoin)
    .Add(Subqueries.PropertyEq("currWorkFlow.id", 
        DetachedCriteria.For<ApplicationWorkFlow>("topFlow")
            .SetMaxResults(1)
            .SetProjection(Projections.Property("topFlow.id"))
            .AddOrder(Order.Desc("topFlow.StartDate"))
            .Add(Restrictions.EqProperty("app.id", "topFlow.Application.id"))))
    .Add(Restrictions.Eq("currWorkFlow.step", step))
    .List<Application>();

Answer 3

How about this sql query?

SELECT * FROM Application app 
JOIN 
    (SELECT * FROM ApplicationWorkFlow aflo WHERE aflo.step = @step) AS aflo 
WHERE aflo.application_id = app.id

Simplified query using criteria

var applications = 
     Session.CreateCriteria<Application>()
            .CreateAlias("ApplicationWorkFlow", "appflo", JoinType.LeftOuterJoin)
            .Add(Restrictions.Eq("appflo.Step", step)) 
            .List<Application>();

Corresponding Criteria Query using detached criteria

var detached = DetachedCriteria.For<ApplicationWorkFlow>()
                               .SetProjection(Projections.Id())
                               .Add(Restrictions.Eq("Step", step));

var applications = 
        Session.CreateCriteria<Application>()
               .CreateAlias("ApplicationWorkFlow", "appflo", JoinType.LeftOuterJoin)
               .Add(Subqueries.PropertyIn("appflo.Id", detachedCriteria))
                 .List<Application>();

Could someone please tell me if the above two queries are the same? They are generating the same sql in my case. If they are the same, why should be use the DetachedCriteria?