How to take this sql query and turn it into nhibernate query

Question

I am trying take this sql query and make it into an nhibernate HQL query. I am using nhibernate 3 and Fluent Nhibernate 1.2

SELECT     dbo.Tasks.CourseId, dbo.CoursePermissions.BackgroundColor, dbo.Tasks.DueDate, dbo.Tasks.TaskName, dbo.Tasks.TaskId
FROM         dbo.Courses INNER JOIN
                      dbo.Tasks ON dbo.Courses.CourseId = dbo.Tasks.CourseId INNER JOIN
                      dbo.CoursePermissions ON dbo.Courses.CourseId = dbo.CoursePermissions.CourseId
WHERE     (dbo.Tasks.CourseId = 1)

I would have liked to use linq but I don't think nhibernate supports linq joins yet so I guess I am stuck with using HQL(unless someone knows a better way).

I guess I can use QueryOver or the other ways nhibernate does queries so whatever works the best. I still don't understand the difference between all the ways as if I could do everything in linq I would.

However I have no clue on how to write my query.

Thanks

Edit

I now have this(changed a bit)

Course cAlias = null; Task tAlias = null; CoursePermission cpAlias = null;

    var result = session.QueryOver<Task>(() => tAlias)
        .JoinAlias(() => tAlias.Course, () => cAlias)
        .JoinAlias(() => cAlias.CoursePermissions, () => cpAlias)
        .Where(Restrictions.In(Projections.Property(() => cAlias.Id), courseIds))
        .And(x => x.DueDate >= startDate)
        .And(x => x.DueDate <= endDate)
        .Select( Projections.Property(() => cAlias.Id),
                Projections.Property(() => cpAlias.BackgroundColor),
                Projections.Property(() => tAlias.DueDate),
                Projections.Property(() => tAlias.TaskName),
                Projections.Property(() => tAlias.TaskId))
        .List<object[]>();

I know want to map it to

public class TaskAppointments { public int Id { get; set; } public string BackgroundColor { get; set; } public DateTime DueDate { get; set; } public int TaskId { get; set; } public string TaskName { get; set; }

}

How do I do this. If this was a linq method I would do

.Select(new TaskAppointments { TaskId = Projections.Property(() => tAlias.TaskId)})

but it says it can't convert it to an int.

Edit2

This is what I came up with

Course cAlias = null; Task tAlias = null; CoursePermission cpAlias = null; TaskAppointments taskAppointments = null;

    List<TaskAppointments> result = session.QueryOver<Task>(() => tAlias)
        .JoinAlias(() => tAlias.Course, () => cAlias)
        .JoinAlias(() => cAlias.CoursePermissions, () => cpAlias)
        .Where(Restrictions.In(Projections.Property(() => cAlias.Id), courseIds))
        .And(x => x.DueDate >= startDate)
        .And(x => x.DueDate <= endDate)
        .SelectList(list => 

                            list.SelectGroup(x => x.TaskId).WithAlias(() => taskAppointments.TaskId)
                            .SelectGroup(() => cpAlias.BackgroundColor).WithAlias(() => taskAppointments.BackgroundColor)
                            .SelectGroup(x => x.DueDate).WithAlias(() => taskAppointments.DueDate)
                            .SelectGroup(x => x.TaskName).WithAlias(() => taskAppointments.TaskName)
                    )
        .TransformUsing(Transformers.AliasToBean<TaskAppointments>())
        .List<TaskAppointments>().ToList();

Florian Lim Florian Lim · Accepted Answer · 2011-04-22T20:32:07

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.

How to take this sql query and turn it into nhibernate query

1 Answers