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 · Accepted Answer · 2011-04-22 21:48:16Z

3

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.

edited Apr 22, 2011 at 21:48

answered Apr 22, 2011 at 20:32

Florian Lim

5,3622 gold badges29 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

chobo2 Over a year ago

@Florian Lim - What do you mean without mapping? Do I have to map joins? I jsut have stuff like References(x => x.Course).Not.Nullable();

Florian Lim Over a year ago

@chobo2 I meant there were no mappings in your question, so I assumed the most likely ones based on the SQL query.

chobo2 Over a year ago

@Florian Lim - Are we talking about the fluent mapping? I can post that if needed. So are you recommending using QueryOver way? So with this way you don't need to worry about what kind of join?

chobo2 Over a year ago

oh and what happens if say for the where caluse I need to do a collection. Like in HQL I would use SetParamterList("courseId",courseIdLIst). How would that be handled in queryOver

Florian Lim Over a year ago

@chobo2 Sorry for the misunderstanding here. All I meant is that I had to assume what the relationships and the names of the properties were, because you did not provide the (fluent) mapping in the question. So instead of a class Tasks it may only be Task. I personally prefer using QueryOver over HQL when it is possible, because with HQL you do not get Intellisense and the compiler won't help you with spelling errors. But for certain (more complex) scenarios HQL can do what Queryover can't.

|

Collectives™ on Stack Overflow

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

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related