CODEWITHSUNDEEP
hibernatenhibernatebatch-updates
Amit Joshi
Amit Joshi

Reputation: 16389

NHibernate - Update selected fields of multiple rows with fixed value

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:

Advantage is that, updates are reflected in cache.

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

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.

Upvotes: 1

Views: 3402

Answers (2)

S&#255;l
S&#255;l

Reputation: 623

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" });

Upvotes: 1

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

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:

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

Upvotes: 1

Related Questions