本文介绍了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>();更多推荐
NHibernate相关子查询获取单个项目
发布评论