7
\$\begingroup\$

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<Registration> registrations;
  IList<Category> subscribes;
  IList<Event> follows;
}

class Category {
  int id;
  string description;
  IList<User> subscribers;
}

class Event{
  int id;
  string title;
  Category category;
  IList<Registration> registrations;
  IList<User> followers;
  IList<Announcement> 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 => eventz.Id == eventId);
bool isSubscriber = requester.Subscribes.Any(category => category.Events.Any(eventz => eventz.Id == eventId));
bool isRegistered = requester.Registrations.Any(registration => registration.Event.Id == eventId);
var userRoleList= new List<UserRole>();

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<Announcement>(a => a.IsForFollower == false
                                                            && a.IsForSubscriber == false
                                                            && a.IsForRegistered == false));

foreach (UserRole role in userRoleList)
{
    switch (role)
    {
        case UserRole.FOLLOWER:
            filterRestriction.Add(Restrictions.Where<Announcement>(a => a.IsForFollower));
            break;
        case UserRole.SUBSCRIBER:
            filterRestriction.Add(Restrictions.Where<Announcement>(a => a.IsForSubscriber));
            break;
        case UserRole.REGISTERED:
            filterRestriction.Add(Restrictions.Where<Announcement>(a => a.IsForRegistered));
            break;
    }
}
//this is the last query transaction, so total 5 times select from db CMIIW, 
return _session.QueryOver<Announcement>()
               .Where(filterRestriction)
               .And(a => 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?

\$\endgroup\$
  • \$\begingroup\$ Are you stuck with an NHibernate solution? Can you do a stored procedure/view/dynamic sql statement instead? \$\endgroup\$ – jlnorsworthy Jan 25 '14 at 4:15
  • \$\begingroup\$ nope, i knew there is a lot of alternatives like you said.. I just wanna get a better solution with nhibernate api. Because i lack of experience with nhibernate api, hoping someone else can provide something different or maybe a better point of view for this case. \$\endgroup\$ – demongill Jan 25 '14 at 5:05
  • 1
    \$\begingroup\$ Maybe I misunderstood what you mean by transaction, but if your only goal is to make all queries in one transaction, why not use NHibernate transaction support? Use of implicit transactions is discouraged according to NHibernate blog post. But I probably misunderstood you, you probably already have one transaction, and what you really want is to make fewer database queries in it, right? \$\endgroup\$ – Maros Feb 18 '14 at 22:45
  • \$\begingroup\$ That's right, I want to make it to be a fewer query as for now it does 5 select to db. Is it possible? \$\endgroup\$ – demongill Nov 24 '14 at 17:27
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Yes, I've been applying this module you talked about in every project. The problem is I want to know if there is a way to make my queries simpler than like mine above? \$\endgroup\$ – demongill Nov 24 '14 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.