NHibernate - Update selected fields of multiple rows with fixed value

Question

How can I convert following SQL query to NHibernate?

UPDATE MyTable
SET Field1 = "NewValue1",
Field2 = "NewValue2"
WHERE Field3 = FilterValue

Assume that MyTable contains huge records and above statement affects good percentage of it. It have 50 fields (columns) and some of them hold long strings or reference to other table that I would avoid to load/update unnecessarily.

The way I implemented it now is as below:

entities = nhSession.QueryOver<T>().Where(whereJunction).List();
nhSession.SetBatchSize(batchSize);
transaction = nhSession.BeginTransaction();
foreach(entity in entities)
{
    //Modify entity here...
    //nhSession.Update(entity);//No need to call this. Commit() will do.
}
transaction.Commit();

Even though batching helps improve performance, this approach have following drawbacks:

• Multiple statements executed ultimately degrading performance.
• Long foreach loop.
• Using nhSession.Flush() does not overcome the drawbacks.

Advantage is that, updates are reflected in cache.

Another way is HQL that I am not considering for following reasons:

• Strings; I do not love them much. That is one of the reason I switched to ORM.
• Not strongly typed. Refactoring is difficult.
• Updates are not reflected in cache.
• I am not sure if this honors UoW (NHibernate Transaction).

Yet another way is raw SQL that I do not want to use. I want to stick to ORM.

References:

Link1: Question and all answers suggests the ways I have already mentioned above.

Link2: Same that I have already mentioned above.

Link3: Same that I have already mentioned above.

Note: I have also added 'hibernate' tag assuming similar solution will also available with NHibernate; they are twins by the way.

Answer 1

We have to pick up to use C# (POCO) entities and have strongly typed code - or to use HQL.

In case we want to keep the comfort coming with entities (mapped objects) - the above code (coming with Question) would do the job...

entities = nhSession... // load and TRANSFORM to C# objects
foreach(entity in entities)
{
    //Modify entity here...
}

If we are ready to use HQL (strings, or even our custom string builders to automate it more) we can profit a lot from NHibernate built in API:

13.3. DML-style operations

NHibernate – Executable DML by Ayende

And as for example shown here:

• Update Top n using NHibernate

we can do update without loading any data like this

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();
string hqlVersionedUpdate = "update versioned Customer set name = :newName where name = :oldName";
int updatedEntities = s.CreateQuery( hqlUpdate )
        .SetString( "newName", newName )
        .SetString( "oldName", oldName )
        .ExecuteUpdate();
tx.Commit();
session.Close();

other example with delete

Answer 2

You can now use Linq with NHibernate to do that!

//
// Summary:
//     Update all entities selected by the specified query. The update operation is
//     performed in the database without reading the entities out of it.
//
// Parameters:
//   source:
//     The query matching the entities to update.
//
//   expression:
//     The update setters expressed as a member initialization of updated entities,
//     e.g. x => new Dog { Name = x.Name, Age = x.Age + 5 }. Unset members are ignored
//     and left untouched.
//
// Type parameters:
//   TSource:
//     The type of the elements of source.
//
// Returns:
//     The number of updated entities.
public static int Update<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, TSource>> expression);

In your situation it would mean:

session.Query<MyTable>()
            .Where(i => i.Field3 == FilterValue)
            .Update(i => new MyTable { Field1 = "NewValue1", Field2 = "NewValue2" });