CODEWITHSUNDEEP
nhibernatenhibernate-criteria
Dharmesh
Dharmesh

Reputation: 570

NHibernate Criteria and Projections

I have my entities like below:

(1) Course (with details) (2) Student (with details) (3) StudentEnrolment (that has a one to many mapping of Student to Course)

Now I am trying to create a projection class (called CourseSummary) which has few fields of the Course along with the total number of students in that course (call it TotalEnrolments). In the below code I have included Campus of the course as well (though irrelevant to my question).

I have reached till here:

                var courseSummaries = session.CreateCriteria<Course>()
               .CreateAlias("Campus", "cmp")
               .SetProjection(Projections.ProjectionList()
                                  .Add(Projections.Property("CourseId"), "CourseId")
                                  .Add(Projections.Property("StartDate"), "StartDate")
                                  .Add(Projections.Property("EndDate"), "EndDate")
                                  .Add(Projections.Property("cmp.CampusId"), "CampusId")
                                 //What here for "TotalEnrolments"?
                                  ).SetResultTransformer(Transformers.AliasToBean<Course>())
               .List<Course>();

But how shall I join StudentEnrolment class and get total number of related records from it?

I am new to the NHibernate world hence my question can be very preliminary.

Thanks

Upvotes: 2

Views: 6498

Answers (2)

Dharmesh
Dharmesh

Reputation: 570

After going through Firo's answer and doing a bit of tweaking myself, I solved it like below:

var courseSummaries = session.CreateCriteria<Course>("c")
   .CreateAlias("Campus", "cmp")
   .SetProjection(Projections.ProjectionList()
       .Add(Projections.Property("CourseId"), "CourseId")
       .Add(Projections.Property("StartDate"), "StartDate")
       .Add(Projections.Property("EndDate"), "EndDate")
       .Add(Projections.Property("cmp.CampusId"), "CampusId")
       .Add(Projections.Subquery(DetachedCriteria.For<StudentEnrolment>("s")
           .Add(Restriction.EqProperty("s.Course.CourseId", "c.CourseId"))
           .SetProjections(Projections.RowCount()), "TotalStudents")
   )
   .SetResultTransformer(Transformers.AliasToBean<CourseSummary>())
   .List<CourseSummary>();

Upvotes: 1

Firo
Firo

Reputation: 30803

var courseSummaries = session.CreateCriteria<Course>("c")
       .CreateAlias("Campus", "cmp")
       .SetProjection(Projections.ProjectionList()
           .Add(Projections.Property("CourseId"), "CourseId")
           .Add(Projections.Property("StartDate"), "StartDate")
           .Add(Projections.Property("EndDate"), "EndDate")
           .Add(Projections.Property("cmp.CampusId"), "CampusId")
           .Add(Projections.Subquery(DetachedCriteria.For<Student>()
               .Add(Restriction.Eq("Course.Id", "c.Id"))
               .SetProjections(Projections.RowCount()), "TotalStudents")
       )
       .SetResultTransformer(Transformers.AliasToBean<CourseSummary>())
       .List<CourseSummary>();

Upvotes: 3

Related Questions