Converting HQL Query to Criteria generates invalid SQL

Question

I am attempting to convert the following HQL query to Criteria.

from SubportfolioAudit as a
where a.ID in (select max(a2.ID)
       from SubportfolioAudit as a2
                where a2.EffectiveDate = :EffectiveDate
                    and a.Subportfolio.ID = a2.Subportfolio.ID
                group by a2.Subportfolio.ID)
and a.Subportfolio.ID in (:SubportfolioList)

So far I have the following:

var crit =  Session.CreateCriteria(typeof(SubportfolioAudit));
var currentAuditByEffectiveDate = DetachedCriteria.For(typeof(SubportfolioAudit))
             .SetProjection(Projections.ProjectionList()
                .Add(Projections.Max("ID"))
                .Add(Projections.GroupProperty("Subportfolio.ID")))
             .Add(Expression.Eq("EffectiveDate", effectiveDate));

crit.Add(Subqueries.PropertyIn("ID", currentAuditByEffectiveDate));
crit.Add(Expression.In("Subportfolio.ID", subportfolioList.Select(x => x.ID).ToArray()));

return crit.List<SubportfolioAudit>();

Which generates an invalid sub query, you can see it here:

SELECT this_.SubportfolioAuditId        as Subportf1_21_6_,
       this_.Event                      as Event21_6_,
       this_.CreatedDate                as CreatedD3_21_6_,
       this_.[User]                     as User4_21_6_,
       this_.EffectiveDate              as Effectiv5_21_6_,
       this_.SubportfolioId             as Subportf6_21_6_,
       subportfol2_.SubportfolioId      as Subportf1_12_0_,
       subportfol2_.PACERCode           as PACERCode12_0_,
       subportfol2_.HedgeRatio          as HedgeRatio12_0_,
       subportfol2_.Name                as Name12_0_,
       subportfol2_.Strategy            as Strategy12_0_,
       subportfol2_.BasketId            as BasketId12_0_,
       subportfol2_.PortfolioId         as Portfoli7_12_0_,
       subportfol2_.ReferenceBasketId   as Referenc8_12_0_,
       (SELECT CASE 
                 WHEN Count(* ) > 0
                 THEN 1
                 ELSE 0
               END
        FROM   Asset
        WHERE  Asset.SubportfolioId = subportfol2_.SubportfolioId) as formula1_0_,
       basket3_.BasketId                as BasketId7_1_,
       basket3_.Name                    as Name7_1_,
       basket3_.CatsBenchmarkCode       as CatsBenc4_7_1_,
       basket3_.Description             as Descript5_7_1_,
       basket3_.BaseBasketId            as BaseBask6_7_1_,
       basket3_.Filename                as Filename7_1_,
       basket3_.Type                    as Type7_1_,
       basket4_.BasketId                as BasketId7_2_,
       basket4_.Name                    as Name7_2_,
       basket4_.CatsBenchmarkCode       as CatsBenc4_7_2_,
       basket4_.Description             as Descript5_7_2_,
       basket4_.BaseBasketId            as BaseBask6_7_2_,
       basket4_.Filename                as Filename7_2_,
       basket4_.Type                    as Type7_2_,
       portfolio5_.PortfolioId          as Portfoli1_5_3_,
       portfolio5_.BaseCurrencyCode     as BaseCurr2_5_3_,
       portfolio5_.TradingAccountNumber as TradingA3_5_3_,
       portfolio5_.Name                 as Name5_3_,
       portfolio5_.ClientId             as ClientId5_3_,
       client6_.ClientId                as ClientId4_4_,
       client6_.ExplicitFee             as Explicit2_4_4_,
       client6_.Affiliated              as Affiliated4_4_,
       client6_.ClientGroup             as ClientGr4_4_4_,
       client6_.RCODA                   as RCODA4_4_,
       client6_.Name                    as Name4_4_,
       client6_.BaseCurrencyCode        as BaseCurr7_4_4_,
       client6_.Region                  as Region4_4_,
       basket7_.BasketId                as BasketId7_5_,
       basket7_.Name                    as Name7_5_,
       basket7_.CatsBenchmarkCode       as CatsBenc4_7_5_,
       basket7_.Description             as Descript5_7_5_,
       basket7_.BaseBasketId            as BaseBask6_7_5_,
       basket7_.Filename                as Filename7_5_,
       basket7_.Type                    as Type7_5_
FROM   dbo.SubportfolioAudit this_
       inner join dbo.Subportfolio subportfol2_
         on this_.SubportfolioId = subportfol2_.SubportfolioId
       left outer join dbo.Basket basket3_
         on subportfol2_.BasketId = basket3_.BasketId
       left outer join dbo.Basket basket4_
         on basket3_.BaseBasketId = basket4_.BasketId
       left outer join dbo.Portfolio portfolio5_
         on subportfol2_.PortfolioId = portfolio5_.PortfolioId
       left outer join dbo.Client client6_
         on portfolio5_.ClientId = client6_.ClientId
       left outer join dbo.Basket basket7_
         on subportfol2_.ReferenceBasketId = basket7_.BasketId
WHERE  this_.SubportfolioAuditId in (SELECT   max(this_0_.SubportfolioAuditId) as y0_,
                                              this_0_.SubportfolioId           as y1_
                                     FROM     dbo.SubportfolioAudit this_0_
                                     WHERE    this_0_.EffectiveDate = '2009-09-11T00:00:00.00' /* @p0 */
                                     GROUP BY this_0_.SubportfolioId)
       and this_.SubportfolioId in (13 /* @p1 */,14 /* @p2 */,15 /* @p3 */,16 /* @p4 */,
                                    17 /* @p5 */,18 /* @p6 */,19 /* @p7 */,20 /* @p8 */,
                                    21 /* @p9 */)

I know that the Projections.GroupProperty() is causing the problem, but I cannot seem to find another way to accomplish what I want.

Answer 1

I believe (and correct me if I am wrong) that the sub query is invalid because you are missing the second where clause you have set in your HQL (... and a.Subportfolio.ID = a2.Subportfolio.ID).

Converting your Criteria query as below I believe (cannot verify as I cannot test your code) that it will do the trick.

var crit = Session.CreateCriteria(typeof(SubportfolioAudit), "mainQuery");
var currentAuditByEffectiveDate = DetachedCriteria.For(typeof(SubportfolioAudit),"subQuery")
              .SetProjection(Projections.ProjectionList()
                                .Add(Projections.Max("ID"))
                                .Add(Projections.GroupProperty("Subportfolio.ID")))
              .Add(Expression.Eq("EffectiveDate", effectiveDate));
              .Add(Expression.EqProperty("subQuery.ID", "mainQuery.ID"));

crit.Add(Subqueries.PropertyIn("mainQuery.ID", currentAuditByEffectiveDate));
crit.Add(Expression.In("mainQuery.Subportfolio.ID", subportfolioList.Select(x => x.ID).ToArray()));

return crit.List<SubportfolioAudit>();

What I have done is to add an extra where clause in the DetachedCriteria that 'connects' the SubportfoliAudit.ID column of the subquery with the outer main query. I have also provided aliases to name the queries.