How to tell NHibernate that a trigger updates another table?

Question

I just got a TooManyRowsAffectedException while working with NHibernate and I've seen workarounds for that by injecting a different batcher like here TooManyRowsAffectedException with encrypted triggers, or by modifying the triggers in the database to use SET NOCOUNT ON (I can't use this one since I don't want to modify the database -- it is very complex with more than a hundred tables all related together and I don't want to go mess with it since other applications use it). What I don't understand is why this exception happens. All I do is that I have a Sample object that's got a couple of values that I check, and if the values fit with given criteria, I set the Sample.IsDone row to 'Y' (in our database all booleans are represented by a char Y or N). The code is very simple:

IQueryable<Sample> samples = session.Query<Sample>().Where(s => s.Value == desiredValue);
foreach (Sample sample in samples)
{
  sample.IsDone = 'Y';
  session.Flush(); // Throws TooManyRowsAffectedException
}
session.Flush(); // Throws TooManyRowsAffectedException

The Flush call throws whether I put it inside the loop or outside. Is there something I am doing wrong or is it only related with the way the database is made? I tried calling SaveOrUpdate() on the sample before Flush(), but it didn't change anything. I know I can work around this exception, but I'd prefer understanding the source of the problem.

Note: In the exception it tells me the actual row count is 2 and the expected is 1. Why does it update 2 rows since I only change 1 row?

Thanks all for your help!

EDIT:

I was able to find out that the cause of that is because there is a trigger in the database updating a row in the Container table (Containers contain Samples) when the sample is updated. Is there a way to configure NHibernate so that it knows about this trigger and expects the right number of rows to get updated?

Sixto Saez Sixto Saez · Accepted Answer · 2011-07-06T15:17:42

The only work around I've found is shown in the code snippet below using Fluent NHibernate. It is ugly since it is hard coding SQL into your mapping but it does work. The Check property is set to None so that the counts are ignored. I don't know if you can accomplish this using straight HBM files but there probably is a way. It would be great is there was a configuration option in NHibernate (or Fluent NH) to either set the expected updated row count or ignore it when needed.

public class OrderMap : ClassMap<Order>
{
    public OrderMap()
    {
        Id(c => c.Id, "order_id").GeneratedBy.Native();

        Table("order");

        Map(c => c.GroupId, "group_id");
        Map(c => c.Status, "status");
        Map(c => c.LocationNumber, "location");

        SqlInsert("insert into order (group_id, status, location) values (?, ?, ?)").Check.None();
        SqlUpdate("update order set group_id = ?, status = ?, location = ? where order_id = ?")).Check.None();
        SqlDelete("delete order where order_id = ?").Check.None();
    }
}

EDIT: For those unfortunate folks that aren't aware of Fluent NHibernate or love painful generation of HBM files by hand, here is the HBM file for this sample mapping:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property" auto-import="true" default-cascade="none" default-lazy="true">
  <class xmlns="urn:nhibernate-mapping-2.2" mutable="true" name="Your.DomainModel.Entities.Order, Your.DomainModel, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="order">
    <id name="Id" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" unsaved-value="0">
      <column name="order_id" />
      <generator class="identity" />
    </id>
    <property name="GroupId" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="group_id" />
    </property>
    <property name="Status" type="System.Int16, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="status" />
    </property>
    <property name="LocationNumber" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="loc_num" />
    </property>
    <sql-insert check="none">insert into order (group_id, status, location) values (?, ?, ?)</sql-insert>
    <sql-update check="none">update order set group_id = ?, status = ?, location = ? where order_id = ?</sql-update>
    <sql-delete check="none">delete order where order_id = ?</sql-delete>
  </class>
</hibernate-mapping>

How to tell NHibernate that a trigger updates another table?

3 Answers