CODEWITHSUNDEEP
nhibernate
JP.
JP.

Reputation: 5606

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

Upvotes: 0

Views: 278

Answers (2)

Ian Nelson
Ian Nelson

Reputation: 58733

I think the NHibernate feature you seek is known as Executable DML.

Ayende has a blog post giving an example at http://ayende.com/blog/4037/nhibernate-executable-dml .

Depending on your names of your entities and their properties, and assuming you have an ISession instance variable called session, you would need to execute an HQL query something like:

session.CreateQuery("update MailSubscriptions set Subscribed = true where MailingList.Id in (21,14,15,19) and User.Id = 'me'")
    .ExecuteUpdate();

Now, having said that, I think in the use case you describe (updating a handful of entries within a collection on a single aggregate root), there is no need to use Executable DML. Mark Perry has the right idea - you should simply modify the booleans on the appropriate entities and flush the session in the usual way. If ADO.NET batching is configured appropriately, then the child entries will cause multiple update statements to be sent to the RDBMS in a single database call.

Upvotes: 2

Mark Perry
Mark Perry

Reputation: 1735

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

Upvotes: 2

Related Questions