如何在 Nhibernate 中对子集合进行查询
本文关键字:子集合 查询 Nhibernate | 更新日期: 2023-09-27 18:34:21
您好,我有一个名为通知的类,它是用户的子类。
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public string UserName { get; set; }
public ICollection<UserNotification> UserNotifications { get; set; }
}
public class Notification
{
public int Id { get; set; }
public ICollection<UserNotification> UserNotifications { get; set; }
public string Title { get; set; }
public string Message { get; set; }
public bool IsRead { get; set; }
public DateTime CreatedDate { get; set; }
}
public class UserNotification
{
public User User { get; set; }
public Notification Notification { get; set; }
}
现在我想获取用户按 ID,这将带来当前用户的所有通知。
var user = NhSession.Get<User>(userId);
但我不想收到所有的通知。我只想向用户获取未读通知,只想为用户获取top 5 (Latest) notifications
。
我试图通过joinQueryOver来实现这一点,但我无法做到这一点。任何人都可以建议让它工作。
基于最新的更新和新的实体结构,我们现在可以从配对对象中获利,并快速选择具有未读通知的用户,例如
查找未阅读通知的用户
var session = NHSession.GetCurrent();
Notification notification = null;
UserNotification pair = null;
User user = null;
var subquery = QueryOver.Of<UserNotification>(() => pair)
// this will give us access to notification
// see we can filter only these which are NOT read
.JoinQueryOver(() => pair.Notification, () => notification)
// here is the filter
.Where(() => !notification.IsRead)
// now the trick to take only related to our user
.Where(() => pair.User.Id == user.Id)
// and get the user Id
.Select(x => pair.User.Id);
var listOfUsers = session.QueryOver<User>(() => user)
.WithSubquery
.WhereProperty(() => user.Id)
.In(subquery)
// paging
.Take(10)
.Skip(10)
.List<User>();
每个用户 ID 查找 5 个未读通知
var userId = 1;
var subqueryByUser = QueryOver.Of<UserNotification>(() => pair)
// now we do not need any kind of a join
// just have to filter these pairs related to user
.Where(() => pair.User.Id == userId)
// and get the notification Id
.Select(x => pair.Notification.Id);
var notificationsPerUser = session.QueryOver<Notification>(() => notification)
.WithSubquery
.WhereProperty(() => notification.Id)
.In(subqueryByUser)
.Where(() => !notification.IsRead)
// if needed we can order
// .OrderBy(...
.Take(5)
.List<Notification>()
session.Get<TEntity>(entityId)
我们在那里加载映射的实体。这就是合同。
如果我们想得到过滤后的结果,我们必须使用另一个合约来接收数据:Session.CreateCriteria()
(或任何其他查询API,即 QueryOver()
)
因此,在我们的例子中,我们应该构建查询来查找具有未读通知的用户:
Occupation Notification= null;
User user = null;
var subquery = QueryOver.Of<Notification>(() => notification)
.Where(() => !notification.IsRead )
// just related to the user, from outer query
.Where(() => notification.User.ID == user.ID)
.Select(x => notification.User.ID);
var list = session.QueryOver<User>(() => user)
.WithSubquery
.WhereProperty(() => user.ID)
.In(subquery)
// paging
.Take(10)
.Skip(10)
.List<User>();
我们在这里可以看到的是期望(实际上是必须的),即通知对其父级、用户有回传:
public class Notification
{
...
public User User {get;set;}
}
但这应该不是问题,它只是一个映射,而不是数据库中的更改
类似的查询(在通知之上)我们只能用于获取其中的前 5 个:
var notifications = session.QueryOver<Notification>(() => notification)
// here is a userId for a specific user.
// we can use IN() to load for more of them
.Where(() => notification.User.ID != userId)
.Take(5)
.List<Notification>()
;