How to do QueryOver in Nhibernate for child collection
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 User By ID which will bring all notifications for the current user.
var user = NhSession.Get<User>(userId);
But I don't want to receive all notifications. I just want to get unread notifications from the user and I just want to get top 5 (Latest) notifications
for the user.
I tried to achieve this using joinQueryOver, but I was unable to do so. Can anyone suggest this to work.
source to share
Based on the latest version and new Entity (s) structure, we can now profit from the Pairing object and quickly select Users who have unread Notificaitons like this
Find users who haven't 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 user
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>()
source to share
session.Get<TEntity>(entityId)
to load the Ent IS AS object. This is a contract.
If we want to get filtered results, we have to use a different contract to get the data: Session.CreateCriteria()
(or any other query API, i.e. QueryOver()
)
So, in our case, we have to build a query to find the user with unread notifications:
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>();
What we can see here is the expectation (actually MUST) that the notification returns a link to its parent, the user:
public class Notification
{
...
public User User {get;set;}
}
But that shouldn't be a problem, it's just a mapping, not a change in the DB
We can use a similar request (besides the notification) to get only the first 5 of them:
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>()
;
source to share