NHibernate select with Query Over optimization for user roles base case I have a complicated select procedure that I solved with a 5 query select, so I am posting it here to get suggestions on how I can shorten it up a bit.  I am hoping that someone can help me make it a single query select.
I'm using nhibernate query over here.

I have 7 tables which are


t_user
t_category
t_event
t_announcement
t_event_follower
t_event_register
t_event_subcribe


I will represent them in the classes below:

class User {
  int id;
  string username;
  IList&lt;Registration&gt; registrations;
  IList&lt;Category&gt; subscribes;
  IList&lt;Event&gt; follows;
}

class Category {
  int id;
  string description;
  IList&lt;User&gt; subscribers;
}

class Event{
  int id;
  string title;
  Category category;
  IList&lt;Registration&gt; registrations;
  IList&lt;User&gt; followers;
  IList&lt;Announcement&gt; announcements;
}

class Announcement {
  int id;
  string title;
  Event event;
  bool isForFollower;
  bool isForRegister;
  bool isForSubscriber;
}

class Registration {
  int id;
  Event event;
  User user;
  string additionalInfo;
  RegistrationStatus status;
}

enum UserRole {
  PUBLIC,
  FOLLOWER,
  SUBSCRIBER,
  REGISTERED
}


The goal is to show all announcements in 1 event based on user roles on that event.  What I had done first is populate user role in list with the code below:

User requester = _userRepo.SearchBy(username); // 1 query for user detail

// 3 lazy load objects make nhibernate to load the object from db, i assume it takes 3 query select if objects haven't load in session
bool isFollower = requester.Follows.Any(eventz =&gt; eventz.Id == eventId);
bool isSubscriber = requester.Subscribes.Any(category =&gt; category.Events.Any(eventz =&gt; eventz.Id == eventId));
bool isRegistered = requester.Registrations.Any(registration =&gt; registration.Event.Id == eventId);
var userRoleList= new List&lt;UserRole&gt;();

if (isFollower) userRoleList.Add(UserRole.FOLLOWER);
if (isSubscriber) userRoleList.Add(UserRole.SUBSCRIBER);
if (isRegistered) userRoleList.Add(UserRole.REGISTERED);


last with code in repository layer below i create dynamic disjunction for query over :

Disjunction filterRestriction = Restrictions.Disjunction();
// this condition to select announcement which is for public not specific
filterRestriction.Add(Restrictions.Where&lt;Announcement&gt;(a =&gt; a.IsForFollower == false
                                                            &amp;&amp; a.IsForSubscriber == false
                                                            &amp;&amp; a.IsForRegistered == false));

foreach (UserRole role in userRoleList)
{
    switch (role)
    {
        case UserRole.FOLLOWER:
            filterRestriction.Add(Restrictions.Where&lt;Announcement&gt;(a =&gt; a.IsForFollower));
            break;
        case UserRole.SUBSCRIBER:
            filterRestriction.Add(Restrictions.Where&lt;Announcement&gt;(a =&gt; a.IsForSubscriber));
            break;
        case UserRole.REGISTERED:
            filterRestriction.Add(Restrictions.Where&lt;Announcement&gt;(a =&gt; a.IsForRegistered));
            break;
    }
}
//this is the last query transaction, so total 5 times select from db CMIIW, 
return _session.QueryOver&lt;Announcement&gt;()
               .Where(filterRestriction)
               .And(a =&gt; a.Event.Id == id)
               .List();


In the above code, the query result will show public announcement plus specific announcement based on role that user have in related event.

I admit this is the simplest solution I could think to solve the case.  Could someone give a suggestion or rework this just by 1 simple operation with query over or just remake it with better query?
If this is a web application, you can use an HttpModule to do your session management. Begin every web request by opening a session and beginning a transaction. At the end of every web request, commit the transaction and close the session.

As long as you're wrapping all the hits to the database in a single transaction, you don't need to worry about efficiency too much.