Selecting a SubQuery from a DetachedCriteria

Viewed 32

I have the following criteria and detachedCriteria.

    var criteria = Session.CreateCriteria<ItemAnalysis>("ia");
                criteria.CreateAlias("ia.ItemInstance", "ii");
                criteria.CreateAlias("ii.ScoreAdministration", "sa");
                criteria.Add(Restrictions.Eq("ii.ItemId", itemId));

    var status = DetachedCriteria.For<ItemAnalysis>("ia_")
                        .CreateAlias("ia_.ItemInstance", "ii_")
                        .CreateAlias("ii_.ScoreAdministration", "sa_")
                        .Add(Restrictions.Eq("ii_.ItemId", itemId))
                        .SetProjection(
                            Projections.SqlProjection
                            (
                                "ia_.CTTItemStatId, RANK() OVER(Partition BY 
                                    sa_.ExamSeriesCode ORDER BY ia_.StatDate DESC) AS RowNm",
                                new string[] { "CTTItemStatId", "RowNm" },
                                new IType[] { NHibernate.NHibernateUtil.Int32, 
                                                  NHibernate.NHibernateUtil.Int32 }
                            )
                         );

I need a way to get a projection or second subquery from the detached query that has just that property and adds a restriction of RowNm=1. I looked at DetachedCriteria.CreateCriteria but that requires an Association Path. I haven't found any similar examples. I did try

 // what parameters should I be using here??  
  var subQuery = status.CreateCriteria(?, ?)  // expects an association path and an alias
                    .Add(Restrictions.Eq("RowNm", 1))
                    .SetProjection(
                        Projections.Property("CTTItemStatId")
                    );

  criteria = criteria.Add(Subqueries.PropertyEq("CTTItemStatId", subQuery));
0 Answers
Related