I tend to overcomplicate things, I think that I do it subconsciously in an attempt to make sure I give due diligence in the search for the best solution.
I had a parent child relationship mapped out using NHibernate. Something like the below where there is a one-to-many bag in the parent class and a many-to-one relationship in the child class.
Parent class:
<bag name="Child" inverse="true" cascade="all" >
<key column="FK_IDXYZ" foreign-key="FK_Child_Parent" />
<one-to-many class="Namespace.Directory.ChildClass" />
</bag>
Child class:
<many-to-one name="Parent" column="FK_IDXYZ" />
Saving the Parent and Child to the database was done in a standard object oriented manner, shown below.
Parent p = new Parent { Name = "Ben" };
p.Child.Add(new Child { Name = "Anne", Parent = p });
session.Save(p);
Updating a parent or a child was and is relatively straight forward. Simply save the class. However, I needed to update all the children for a specific parent. I thought of a number of possibilities.
The first solution, shown below was to retrieve all the Children in a List and loop through each child and perform an update. This seemed, at least initially, like the most programmatic way to implement it. I mean, I was using NHibernate to retrieve the children and then NHibernate and the class to update the children. However, this resulted in 1 update per child. NOT GOOD!!
using (ISession session = factory.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
string hqlQuery = "from Child where fk_idxyz = :parentId";
IQuery ChildQuery = session.CreateQuery(hqlQuery)
.SetInt32("parentId", 12345);
IList<Child> ChildList = ChildQuery.List<Child>();
foreach (var item in ChildList)
{
item.Happy = true;
}
transaction.Commit();
}
Note that in my current situation I needed to update a Boolean from false to true for all children.
I thought, wait a minute man, why not just use HQL like this:
string hql = "update child set happy = true" +
" where fk_idxyz = :parentId";
IQuery query = session.CreateQuery(hql).SetInt32("parentId", 12345);
int UpdatedCount = query.ExecuteUpdate();
It just seemed too simple and too SQL like, especially based on the coolness of the initial Parent Child save logic. Some reading resulted in my initial thought that the ExecuteUpdate() only works with SQL and that I was simply running a SQL query through the IQUery interface somehow. However, further analysis did support that the above was in fact an HQL query and it was being converted into an SQL query using the mapping files.
I looked in the NHibernate source code (NHibernate.IQuery.cs) at the description for the ExecuteUpdate() method. It says “Execute the update or delete statement”. I also looked at its implementation in the NHibernate.Impl.QueryImpl.cs file to try and determine if this had any negative effects. I didn’t find any.
I did find another interface, IQueryTranslator which defined an ExecuteUpdate() method like “Perform a bulk update/delete operation given the underlying query definition”.
My decision is to use the ExecuteUpdate() method, because I am updating data in bulk, it doesn’t seem optimal to perform an update per child when another option like the ExecuteUpdate() method exists.