NHibernate - Update selected fields of multiple rows with fixed value
<p>How can I convert following SQL query to NHibernate?</p>
<pre><code>UPDATE MyTable
SET Field1 = &quot;NewValue1&quot;,
Field2 = &quot;NewValue2&quot;
WHERE Field3 = FilterValue
</code></pre>
<p>Assume that <code>MyTable</code> 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.</p>
<p>The way I implemented it now is as below:</p>
<pre><code>entities = nhSession.QueryOver&lt;T&gt;().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();
</code></pre>
<p>Even though batching helps improve performance, this approach have following drawbacks:</p>
<ul>
<li>Multiple statements executed ultimately degrading performance.</li>
<li>Long <code>foreach</code> loop.</li>
<li>Using <code>nhSession.Flush()</code> does not overcome the drawbacks.</li>
</ul>
<p>Advantage is that, updates are reflected in cache.</p>
<p>Another way is <code>HQL</code> that I am not considering for following reasons:</p>
<ul>
<li>Strings; I do not love them much. That is one of the reason I switched to <code>ORM</code>.</li>
<li>Not strongly typed. Refactoring is difficult.</li>
<li>Updates are not reflected in cache.</li>
<li>I am not sure if this honors UoW (NHibernate Transaction).</li>
</ul>
<p>Yet another way is raw <code>SQL</code> that I do not want to use. I want to stick to ORM.</p>
<h1>References:</h1>
<p><a href="https://stackoverflow.com/questions/780940/batch-update-in-nhibernate">Link1</a>: Question and all answers suggests the ways I have already mentioned above.</p>
<p><a href="https://stackoverflow.com/questions/8047597/fluent-nhibernate-batch-save">Link2</a>: Same that I have already mentioned above.</p>
<p><a href="http://docs.jboss.org/hibernate/orm/4.0/manual/en-US/html/batch.html" rel="nofollow noreferrer">Link3</a>: Same that I have already mentioned above.</p>
<p><strong>Note:</strong> I have also added 'hibernate' tag assuming similar solution will also available with NHibernate; they are twins by the way.</p>