Hi,

I am using NHibernate over a SQL Server database view rather than working directly with the underlying table. The view simply excludes objects that have their IsDeleted property set to true.

My problem arises when I update a parent object where one of the changes is that a child's IsDeleted property is now True.

It appears that NHibernate is checking whether the row was updated by looking at the affected row count but the row has been hidden by the view and so the row count comes back as 0.

Does anyuone have any suggestions on how I can get NHibernate to work over the view?

Interestingly enough, if the parent object is changed so that its IsDeleted property becomes True, then NH doesn't throw an exception.

BTW, having the view is non-negotiable.

Thanks,
Dean.

NHibernate version:
1.0.1

Code between sessionFactory.openSession() and session.close():

ISession hibernateSession = CreateHibernateSession();

IList outArray = new ArrayList();

ITransaction t = null;

try
{
t = hibernateSession.BeginTransaction();

foreach (IDomainObject domObj in objectList)
{
hibernateSession.SaveOrUpdate(domObj);
}

t.Commit();
}

Full stack trace of any exception that occurs:

NHibernate.Impl.SessionImpl [(null)] - could not synchronize database state with session
NHibernate.HibernateException: SQL insert, update or delete failed (expected affected row count: 1, actual affected row count: 0). Possible causes: the row was modified or deleted by another user, or a trigger is reporting misleading row count.
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(Int32 expectedRowCount)
at NHibernate.Collection.AbstractCollectionPersister.Recreate(PersistentCollection collection, Object id, ISessionImplementor session)
at NHibernate.Impl.ScheduledCollectionRecreate.Execute()
at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
at NHibernate.Impl.SessionImpl.Execute()

Name and version of the database you are using:
SQLServer 2000

The generated SQL (show_sql=true):
not available

Debug level Hibernate log excerpt:

Mapping documents:

NHibernate.HibernateException: SQL insert, update or delete failed (expected affected row count: 1, actual affected row count: 0). Possible causes: the row was modified or deleted by another user, or a trigger is reporting misleading row count.

You should look into INSTEAD OF triggers for SQL Server views. You may be able to control affected record count using those triggers.

For posterity I am posting the reason why my error was occuring:

It is because my mapping is not bi-directional. So this was forcing a double update of the child row. 1 to update the properties that changed (the IsDeleted property in this case) and a 2nd to re-connect the child to the parent object
.
In this case, the db view swallowed the row after the first update and this caused the 2nd update to fail.

Unfortunately I cannot implement a two-way relationship as this can cause too many objects to be retrieved. The client app is able to work in a disconnected state, so lazy loading is not an option and retrieving everything is not feasible as NH is working on an app server and I need to minimise the amount of data that is sent to the client.

Can you add to your client API the ability to specify how much data you want to retrieve? This may solve the lazy loading vs. "always get full data set" problem. It worked for me but I don't fully understand your situation.

Interesting concept; I will have a word to our architect and see if this is feasible. Thanks.