NHibernate相关子查询获取单个项目 [英] NHibernate correlated subquery fetching single item

问题描述

我正在使用NHibernate 3.2，并具有以下模型:

I'm using NHibernate 3.2 and have the following model:

class User
{
    public virtual int Id { get; set; }
    public virtual string Username { get; set; }
    public virtual IList<Log> Logs { get; set; }
}

class Log
{
    public virtual int Id { get; set; }
    public virtual User User { get; set; }
    public virtual DateTime Date { get; set; }
}

现在，我想向用户查询最近一次登录的日期.

Now, I want to query User with the date of ther latest Log-entry.

class UserDto
{
    public int UserId { get; set; }
    public string Username { get; set; }
    public DateTime? LastLogDate { get; set; }
}

使用NHibernate QueryOver或Linq进行查询的最有效方法是什么?

What is the most effective way to do this query using NHibernate QueryOver or Linq?

这是我要处理的SQL查询:

This is the SQL-query I'd like to prodcue:

SELECT u.Id as UserId,
       u.Username as Username,
       (SELECT TOP 1 l.Date
        FROM   [Userlog] l
        WHERE  l.User_id = u.Id) as LastLogDate
FROM   [User] u

推荐答案

您可以对聚合查询执行相同的操作...

you could do the same thing with an aggregate query...

Log logAlias = null;
UserDto dto = null;

Session.QueryOver<User>()
    .Left.JoinAlias(x => x.Logs, () => logAlias)
    .SelectList(list => list
        .SelectGroup(x => x.Id).WithAlias(() => dto.UserId)
        .SelectGroup(x => x.Username).WithAlias(() => dto.Username)
        .SelectMax(() => logAlias.Date).WithAlias(() => dto.LastLogDate))
    .TransformUsing(Transformers.AliasToBean<UserDTO>()
    .List<UserDTO>();

或带有子查询...

User userAlias = null;
UserDto dto = null;

Session.QueryOver<User>(() => userAlias)
    .SelectList(list => list
        .Select(x => x.Id).WithAlias(() => dto.UserId)
        .Select(x => x.Username).WithAlias(() => dto.Username)
        .SelectSubQuery(QueryOver.Of<Log>()
            .Where(x => x.User.Id == userAlias.Id)
            .OrderBy(x => x.Date).Desc
            .Select(x => x.Date)
            .Take(1)).WithAlias(() => dto.LastLogDate))
    .TransformUsing(Transformers.AliasToBean<UserDTO>()
    .List<UserDTO>();

这篇关于NHibernate相关子查询获取单个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！