4

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.

Moiz
  • 2,409
  • 5
  • 27
  • 50

2 Answers2

4

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>()
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
1

The session.Get<TEntity>(entityId) is there for us to load the Entity AS IS mapped. That's the contract.

If we want to get filtered results, we have to use another contract to recive the data: Session.CreateCriteria() (or any other querying API i.e. QueryOver())

So in our case, we should build the query to find 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 expectations (well in fact a MUST) that notification has back reference to its parent, to user:

public class Notification
{
    ...
    public User User {get;set;}
}

But that should not be an issue, it is just a mapping, not change in DB

Similar query (on top of Notification) we can use to get only 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>()
;
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • I see you have created a new queryOver for the notification, but what if I want to take only 5 notification in the main query ? Also getting the top five order by date. – Moiz Dec 15 '14 at 08:31
  • Also the reference to its parent, I don't have public User User {get;set;} But I have ICollection because I have many to many condition with User and notification – Moiz Dec 15 '14 at 08:34
  • Seems I failed to explain ;( so: 1) If you want find users with only unread notifications, use my first query and subquery. At the end you have clean list of users. 2) for each user loaded this way, create second query (I showed you in my answer) to load just 5 notifications (you can again filter them to load only unread). 3) The mapping from Notification to User is a must, if you want to filter over them. Hope now it is more clear... – Radim Köhler Dec 15 '14 at 08:46
  • I understand your point, but the problem is I have ICollection in the Notification. I have updated my domains. Please have a look – Moiz Dec 15 '14 at 09:05
  • I see now! Believe or not we can still make it with my approach. Just the queries will be a bit more complex... Simply, principle will remain - just queries have to handle with `many-to-many`, *(which I personally strongly discourage: [see](http://stackoverflow.com/questions/15510748/) )*. – Radim Köhler Dec 15 '14 at 09:08
  • Yes I see its getting very complex, So I am planning to change my concept of fetching the notifications. I would simply fetch the notifications based on userId either by SQLQuery – Moiz Dec 15 '14 at 09:09
  • That could work, BUT! Please, take some time and think about my suggestion: Introduce the pairing object UserNotifciation. As a first level citizen. Create mapping many-to-one one-to-many and you will be rewarded by very powerful querying options. Please, try to re-think that... Good luck with NHibernate ;) – Radim Köhler Dec 15 '14 at 09:10
  • Thanks a lot, yes you are correct I didn't knew about this approach and I think this will solve lot of problems. that was very helpful. – Moiz Dec 15 '14 at 09:14
  • Enjoy NHibernate. I am sure that you will be awarded. There are many similar answers, which shows the power you gain (e.g. [here](http://stackoverflow.com/q/23772548/1679310)) Enjoy awesome NHibernate ;) – Radim Köhler Dec 15 '14 at 09:15
  • Thanks:) also If I insert paired object, then will that be ICollection in both classes ? – Moiz Dec 15 '14 at 09:26
  • Exactly this is how it will work *(except with NHibrnate more native is `IList<>`)* as I shown in mayn answers here, your queries will then become easy to do... good luck – Radim Köhler Dec 15 '14 at 09:29
  • yes okay. BTW- I don't use ICollection, I use ICG.ISet for Iesi collection. – Moiz Dec 15 '14 at 09:34
  • can you please modify your query based on the paired object. I have updated the domain – Moiz Dec 15 '14 at 10:08
  • I added new answer, related to the laste mapping. It should cover all the stuff discussed ;) – Radim Köhler Dec 15 '14 at 11:17