Q:

NHibernate - How do I update more than one boolean field at a time

I am using NHibernate in a web application I'm building. The user can subscript to zero or more mailing-lists (there are a total of 8). This is represented on the screen with a checkbox for each mailing-list.
I would like to use NHibernate to update these in one go. A very simple sql query would be:
update mail_subscriptions set subscribed = true where mailing_list_id in (21,14,15,19) and user_id = 'me'

What is the cleanest way to perform this update via NHibernate so that I can make a single round trip to the database?
Thanks in advance
JP

A:

NHibernate might not be able to update the mail_subscriptions in the way you have shown above but it can do it in a single round trip to the DB using batched queries.
This example considers Subscriptions mapped as a HasMany using Component although roughly the same technique can be used if the mapping was just a plain HasMany. I am also assuming that each user already has rows in the mail_subscriptions table for each mailing list set to false for subscribed.
public class User{
    public virtual string Id {get; set;}
    public virtual IList<MailSubscription> Subscriptions {get; set;}
}

public class MailSubscription{
    public virtual int ListId {get; set;}
    public virtual bool Subscribed {get; set;}
}

public void UpdateSubscriptions(string userid, int[] mailingListIds){
    var user = session.Get<User>(userid);
    foreach(var sub in 
       user.Subscriptions.Where(x=> mailingListIds.Contains(x.ListId))){
        sub.Subscribed=true;
    }
    session.Update(user);
}

Now when the unit of work completes you should see SQL like this produced sent as a single round trip to the DB.
update mail_subscriptions set subscribed=true where user_id='me' and listid=21
update mail_subscriptions set subscribed=true where user_id='me' and listid=14
update mail_subscriptions set subscribed=true where user_id='me' and listid=15
update mail_subscriptions set subscribed=true where user_id='me' and listid=19

