NHibernate - Projections.Property on detached criteria with Alias

Question

I am building a semi-complex report query (may not be the best way, but it works fine up until this issue).

// total appointments
var appts = DetachedCriteria.For<Appointment>("appt")
    .CreateAlias("Lead", "lead")
    .CreateAlias("lead.Promoter", "leadPromoter", JoinType.LeftOuterJoin)
    .Add(Restrictions.EqProperty("leadPromoter.Id", "promoter.Id"))
    .SetProjection(Projections.ProjectionList()
                                   .Add(Projections.CountDistinct("appt.Id"));

// total sales
var sales = DetachedCriteria.For<Appointment>("sales")
    .CreateAlias("Lead", "lead")
    .CreateAlias("Sale", "sale")
    .CreateAlias("lead.Promoter", "leadPromoter", JoinType.LeftOuterJoin)
    .Add(Restrictions.EqProperty("leadPromoter.Id", "promoter.Id"))
    .SetProjection(Projections.ProjectionList()
                                   .Add(Projections.CountDistinct("sales.Id"));

var projections = Projections.ProjectionList()
    .Add(Projections.SubQuery(appts), "Appointments")
    .Add(Projections.SubQuery(sales), "Sales");


var reports = Session.CreateCriteria<Promoter>("promoter")
                .SetProjection(projections)
                .SetResultTransformer(Transformers.AliasToBean(typeof(PromoterReportDto)))
                .List<PromoterReportDto>();

This works fine and brings back correct results, however now I need to introduce a where clause on each of the projections (num appointments, num sales etc) on the week day of each projection.

To do this I was adding this to my projections:

// total appointments
var appts = DetachedCriteria.For<Appointment>("appt")
    .CreateAlias("Lead", "lead")
    .CreateAlias("lead.Promoter", "leadPromoter", JoinType.LeftOuterJoin)
    .Add(Restrictions.EqProperty("leadPromoter.Id", "promoter.Id"))
    .Add(Restrictions.Eq(
                        Projections.SqlFunction("day", NHibernateUtil.DateTime, Projections.Property("appt.AppointmentDate")), selectedDayOfWeek)
                    )
    .SetProjection(Projections.ProjectionList()
                                   .Add(Projections.CountDistinct("appt.Id"));

// total sales
var sales = DetachedCriteria.For<Appointment>("sales")
    .CreateAlias("Lead", "lead")
    .CreateAlias("Sale", "sale")
    .CreateAlias("lead.Promoter", "leadPromoter", JoinType.LeftOuterJoin)
    .Add(Restrictions.EqProperty("leadPromoter.Id", "promoter.Id"))
    .Add(Restrictions.Eq(
                        Projections.SqlFunction("day", NHibernateUtil.DateTime, Projections.Property("sales.AppointmentDate")), selectedDayOfWeek)
                    )
    .SetProjection(Projections.ProjectionList()
                                   .Add(Projections.CountDistinct("sales.Id"));

;

However, it is complaining with this error:

NHibernate.QueryException: Could not find property sales.AppointmentDate

The property definetly exists, if i remove the alias in the Projections.Property (Projections.Property("AppointmentDate")), it works, however it produces this SQL:

       and datepart(day, this_0_.AppointmentDate) = 0 /* @p4 */
       and datepart(day, this_0_.AppointmentDate) = 0 /* @p5 */
       and datepart(day, this_0_.AppointmentDate) = 0 /* @p6 */
       and datepart(day, this_0_.AppointmentDate) = 0 /* @p7 */
       and datepart(day, this_0_.AppointmentDate) = 0 /* @p8 */
       and datepart(day, this_0_.AppointmentDate) = 0 /* @p9 */
       and datepart(day, this_0_.AppointmentDate) = 0 /* @p10 */) as y0_,

As you can see it's using the first instance of my entity, rather than the entity for each specific detached criteria.

Sorry for the long question, i'm not quite sure how to explain the issue without all the code etc.

I can paste up some more code / SQL if needed.

Paul

Answer 1

I think the problem may be that you are aliasing your subquery as "Sales" but you already defined the alias "sales" in the detached criteria. Sql certainly isn't case-sensitive, (and I don't think NHibernate aliases are?)

In any case, I would try changing the alias in the projection list, something like

var projections = Projections.ProjectionList()
   .Add(Projections.SubQuery(appts), "Appointments")
   .Add(Projections.SubQuery(sales), "MySales");

So that you don't have two potentially conflicting aliases.