NHibernate相关子查询获取单个项目

本文介绍了NHibernate相关子查询获取单个项目的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用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>();