score:3

Accepted answer

Without mappings I assume that you have the following relationships: Courses -> Tasks (1:n) and Courses -> CoursePermissions (1:n) I also assumed that you do not want the complete objects but only certain properties, so I used projections.

QueryOver version:

// the aliases are required here, so that we can reference the entities properly
Courses cAlias = null;
Tasks tAlias = null;
CoursePermissions cpAlias = null;

var result = session.QueryOver<Courses>(() => cAlias)
    .JoinAlias(() => cAlias.Tasks, () => tAlias)
    .JoinAlias(() => cAlias.CoursePermissions, () => cpAlias)
    .Where(() => cAlias.CourseId == 1)
    .Select(Projections.Property(() => cAlias.CourseId),
            Projections.Property(()  => cpAlias.BackgroundColor),
            Projections.Property(()  => tAlias.DueDate),
            Projections.Property(()  => tAlias.TaskName),
            Projections.Property(()  => tAlias.TaskId))
    .List<object[]>();

Edit start

If you need to do a WHERE IN clause, you can do this:

List<int> courseIdList = new List<int>() { 1, 2 };

var result = session.QueryOver<Courses>(() => cAlias)
    .JoinAlias(() => cAlias.Tasks, () => tAlias)
    .JoinAlias(() => cAlias.CoursePermissions, () => cpAlias)
    .Where(Restrictions.In(Projections.Property(() => cAlias.CourseId), courseIdList))
    .Select(...)
    .List<object[]>();

Edit end

Edit 2 start

If you want to transform it into a DTO:

// AliasToBeanResultTransformer is in namespace NHibernate.Transform
// we have to use .As("...") for the transformer to find the correct property-names
var result = ...
    .Select(Projections.Property(() => cAlias.CourseId).As("CourseId"),
            Projections.Property(()  => cpAlias.BackgroundColor).As("BackgroundColor"),
            Projections.Property(()  => tAlias.DueDate).As("DueDate"),
            Projections.Property(()  => tAlias.TaskName).As("TaskName"),
            Projections.Property(()  => tAlias.TaskId).As("TaskId"))
    .TransformUsing(new AliasToBeanResultTransformer(typeof(TaskAppointments)))
    .List<TaskAppointments>();

Edit 2 end

HQL version:

string hql = "select c.CourseId, cp.BackgroundColor, t.DueDate, t.TaskName, t.TaskId" 
   + " from Courses as c inner join c.Tasks as t inner join c.CoursePermissions as cp" 
   + " where c.CourseId = 1";

var result2 = session.CreateQuery(hql)
    .List<object[]>();

Be aware that this will result in a cartesian product, so for each Course you will get Tasks.Count + CoursePermissions.Count rows.


Related Articles