Maarten Balliauw {blog}

Great... I've been working with NHibernate and MySQL for a while now, without having any strange problems. For a project I'm working on, I'm using SqlClient instead of MySQL now, and strangeness occurs. When I try to Flush() a NHibernate session, here's what is thrown:

Unexpected row count: 0; expected: 1
at NHibernate.AdoNet.Expectations.BasicExpectation.VerifyOutcomeNonBatched(Int32 rowCount, IDbCommand statement)
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Boolean hasDirtyCollection, Object[] oldFields, Object oldVersion, Object obj, ISessionImplementor session)
at NHibernate.Impl.ScheduledUpdate.Execute()
at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
at NHibernate.Impl.SessionImpl.Execute()
at NHibernate.Impl.SessionImpl.Flush()
at NHibernate.Transaction.AdoTransaction.Commit()

The problem seems to be a combination of things. First, there's my mapping file:

Second, I use _session.SaveOrUpdate(o). SaveOrUpdate() tries to use the NHibernate baked-in generator assigned in the mapping file (in my case: "assigned"). Since my Hash column is filled by hand, using a source-code algorithm, NHibernate can't re-assign the identifier column using the generator, resulting in the above error.

Solution: do NOT assign identifier columns, NHibernate will do this for you! The hash column was thus removed as an identifier, and a normal identifier column has been added. Resulting in a working piece of code. Here's the new mapping:

For everyone who has read my article on NHibernate, here's a story for you...

When building an application, everyone comes to a point where one needs to batch-update records in a database table, based on one or more criteria. Let's say, for example, there's a table "User" containing an activation date. And you want to remove all users that have activated in 1999. In a regular database environment, or when using ADO coding, one would write a DbCommand "DELETE FROM User WHERE activationdate < '2000-01-01'".

This can also be done using NHibernate, by fetching an IList<User> from your database, and calling session.Delete(user); for each user in the list. Another idea is to use a HQL query: session.Delete("from User u where u.ActivationDate < '2000-01-01'"); A good thing about NHibernate is that it supports caching of data, but for this batch-delete purpose, it sucks. NHibernate will, in both previous cases, fetch all affected data, map it to objects, store it in first-level cache, ... Overhead galore!

Luckily, I saw a blog post on this by jlockwood. He simply tells to enlist a regular SQL statement in a NHibernate transaction, and you're ready to go. His code isn't provider-independent, so here's an improved version:

The new .NET magazine Belgium/Netherlands has been distributed to all subscribers. For the latest edition, #15, I've written an article on the basic principles and usage of NHibernate, an ORM-mapper for the .NET platform. It's written in Dutch, so I must disappoint my English blog readers...

A PDF version and example code can be downloaded on the .NET magazine #15 homepage. Comments can be posted on this blog post.