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 Query

More Query from same tag