1

How can I make NHibernate produce this t-sql request ?

Update top (n) Tasks set MODIFICATIONS where CONDITION

I don't want to do a Transaction + batch update.

user2425133
  • 13
  • 2

1 Answers1

0

Please, check this:

a cited code snippet:

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();

So, this way, we can defin UPDATE with HQL (using our domain model) and executing the udpate directly at once on DB side

NOTE: for more details also check this blog post: NHibernate – Executable DML by Ayende

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • I don't see the "Top" command here, I need to update juste 5 elements, I know I can do ExecuteSqlQuery, but then I'll have to check the DataProvider because the Top command is T-sql specific, in MySql we use Limit, I would liked that Nhibernate generate the specific query for me so it would work on all data providers. – user2425133 Sep 11 '14 at 13:31
  • I would say, we can have that! ;) Check this line in the doc: *"...Sub-queries may be used in the where-clause; the subqueries, themselves, may contain joins..."* use some TOP 5 query (subquery) to return proper set of IDs to be updated... and then use DML! ;) powerful NHibernate - check http://nhforge.org/doc/nh/en/index.html#queryhql-subqueries – Radim Köhler Sep 11 '14 at 13:34
  • Thank you very much ! I will put it in a transaction for safety, one HQL query may ( or may not ) produce many sql queries ( if we query on a non-mapped base class for example ). – user2425133 Sep 11 '14 at 13:42
  • Enjoy NHibernate, sir. It is amazing tool ;) – Radim Köhler Sep 11 '14 at 14:37