如何使用此sql查询并将其转换为非hibernate查询

Question

我正在尝试将此sql查询并使其成为一个非hibernate HQL查询。我正在使用nhibernate 3和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)

我本想使用linq，但我不认为nhibernate支持linq连接，所以我想我还是坚持使用HQL(除非有人知道更好的方法)。

我想我可以使用QueryOver或其他方式nhibernate做查询所以什么工作最好。我仍然不明白所有方式之间的区别，好像我可以用linq做我想做的一切。

然而，我不知道如何写我的查询。

感谢

编辑

我现在有了这个(稍微改变了一下)

Course calas = 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[]>();

我知道我想映射到

公共类TaskAppointments｛公共int Id{获取;设置;｝公共字符串BackgroundColor{获取;设置;｝公共日期时间DueDate {get;设置;｝public int TaskId {get;设置;｝公共字符串TaskName{获取;设置;}

}

我该怎么做呢?如果这是一个linq方法，我会使用

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

但是它说不能转换成int型

Edit2

这就是我想出来的

Course calas = null;Task tAlias = null;CoursePermission cpAlias = null;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();

Answer 1

如果没有映射，我假设您有以下关系:Courses -> Tasks (1:n)和Courses -> CoursePermissions (1:n)我还假设您不想要完整的对象，而只想要某些属性，所以我使用投影。

QueryOver版本:

// 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[]>();

编辑开始

如果你需要使用WHERE IN子句，你可以这样做:

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[]>();

<<p> 编辑结束/strong>

Edit 2 start

如果你想把它转换成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版本:

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[]>();

请注意，这将导致笛卡尔积，因此对于每个课程，您将获得任务。Count + CoursePermissions。数行。