In the previous chapter, we learned how to use NHibernate to insert, update, and delete individual entities using ISession methods. NHibernate also allows us to perform some bulk data changes with executable HQL. In this recipe, we'll show you how to use HQL to update all of our books with a single statement.
- Add a new folder named
HqlBulkChangesto the project. - Add a new class named
Recipeto the folder:using System; using NH4CookbookHelpers.Queries; using NHibernate; namespace QueryRecipes.HqlBulkChanges { public class Recipe : QueryRecipe { protected override void Run(ISession session) { var hql = @"update Book b set b.UnitPrice = :minPrice where b.UnitPrice < :minPrice"; var updated=session.CreateQuery(hql) .SetDecimal("minPrice", 55M) .ExecuteUpdate(); Console.WriteLine("Number of books updated:" + updated); hql = @"delete from Book where UnitPrice=:minPrice"; var deleted = session.CreateQuery(hql) .SetDecimal("minPrice", 55M) .ExecuteUpdate(); Console.WriteLine("Number of books deleted:" + deleted); hql = @"insert into Book (Name,Description) select concat(Name,' - the book'), Description from Movie"; var inserted = session.CreateQuery(hql) .ExecuteUpdate(); Console.WriteLine(@"Number of movies recreated as books:" + inserted); } } } - Run the application and start the
HqlBulkChangesrecipe.
We have the following executable HQL query:
update Book b set b.UnitPrice = :minPrice where b.UnitPrice < :minPrice
We call ExecuteUpdate method of IQuery to run this statement. This results in the following SQL statement:
update Product
set UnitPrice = 55
where ProductType = 'Book'
and UnitPrice < 55 The next query performs a deletion, with exactly the same logic. We delete the book we recently updated. The following SQL statement is executed:
delete
from
Product
where
ProductType = 'Book'
and UnitPrice = 55Now that we are all out of books, let's see if we can't create a couple of new ones by inserting "recreations" of all the movies. We execute the following HQL:
insert into Book (Name,Description) select concat(Name,' - the book'),Description from Movie
The HQL function concat is used to form a new product name from the concatenation of the movie name and the " – the book" String. SQL Server receives the following statement:
insert
into
Product
( Name, Description, ProductType ) select
(movie0_.Name+' - the book') as col_0_0_,
movie0_.Description as col_1_0_,
'Book'
from
Product movie0_
where
movie0_.ProductType='Movie'Two things are worth noting here. First, the concat function is automatically translated to the correct statement for the target database, in this case using the plus operator. Also, NHibernate is clever enough to add the necessary ProductType column to the insert, so that the newly created entities will indeed be books.
These statements will only affect the database. The changes will not be reflected in the state of in-memory objects, the second-level cache or anywhere else outside the database.
We could also define these queries in the mapping and load queries like any other named queries.
As we saw in the last query, NHibernate supports bulk inserts in the following form:
insert into destinationEntity (id, prop1, prop2) select b.id, b.prop1, b.prop2 from sourceEntity b where...
There are a few things to keep in mind when considering this solution. First, property types must match exactly. While the database may be perfectly able to convert between types, such as int and long, NHibernate requires them to be the same type.
The id values are particularly limited. There are two options:
- The first option is to copy the id from a property of the source entity. This can be the source entity or any other property. Depending on your existing data, this is not always appropriate.
- The second option uses the entity's POID generator to create an identity for each newly inserted object. However, this only works when the
idis database-generated. This excludes nearly all of the preferred identity generators, such asguid.combandhilo. To use the entity'sidgenerator, simply omit theidcolumn from the list of properties to be set.