How to do a QueryOver in Nhibernate on child collection

Question

Hello I have one Class named Notifications which is a child class for the User.

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; }
}

Now I want to get the User By ID which will bring all the notifications for the current user.

var user = NhSession.Get<User>(userId);

But I don't want to get all the notifications. I just want to get the user with unread notifications and just want to get top 5 (Latest) notifications for the user.

I tried to achieve that by joinQueryOver but I was not able to do that. Can anyone please suggest to get this working.

Answer 1

Based on the latest update and new Entity(ies) structure, we can now profit from Pairing object, and quickly select Users which has unread Notificaitons like this

Find users who have not read notifications

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

Find 5 unread notifications per userId

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>()