Find rows in one table and move it to a second table with NHibernate
<p>I have a SQL Server 2014 database with a table with a lot of data (at full speed it could be millions of rows) named <code>Positions</code>. The <code>Id</code> is a primary key, and there aren't other keys or foreign relationships on this table. </p>

<p>Suppose I have a list of <code>n</code> elements like <code>[{a1, b1, c1}, ... , {an; bn; cn}]</code>, I have to make a search inside <code>Positions</code> table and get all the rows that match:</p>

<pre><code>(columnA == a1 &amp;&amp; columnB == b1 &amp;&amp; columnC == c1)
OR 
(columnA == an &amp;&amp; columnB == bn &amp;&amp; columnC == cn)
</code></pre>

<p>This query could produce from 0 to n rows (i.e. There aren't two rows with same <code>{a, b c}</code>) The resulting rows, should be deleted from <code>Partitions</code> tables and inserted into a second table named <code>DeletedPositions</code> and everything should be done in atomic manner.</p>

<p>I should do this in NHibernate, now since I don't know NHibernate:</p>

<ol>
<li>Do I have some facility for this type of operations?</li>
<li>Should I use NHibernate or a batched SQL command will perform better?</li>
<li>How Can I ensure that all (or nothing) rows will be moved?</li>
</ol>

<p>This do the Job, how can I improve it to make related insert?</p>

<pre><code>            var configuration = new Configuration().DataBaseIntegration(db =&gt;
            {

                db.ConnectionString = @"Server=***;Database=***;Uid=***;Pwd=***;";
                db.Dialect&lt;MsSql2008Dialect&gt;();
                db.BatchSize = 700;
            });

            var mapper = new ModelMapper();
            mapper.AddMappings(Assembly.GetExecutingAssembly().GetExportedTypes());

            HbmMapping mapping = mapper.CompileMappingForAllExplicitlyAddedEntities();
            configuration.AddMapping(mapping);

            var sessionFactory = configuration.BuildSessionFactory();

            IList&lt;PositionDTO&gt; p = null;

            using (IStatelessSession statelessSession = sessionFactory.OpenStatelessSession()) {
                p = statelessSession
                    .QueryOver&lt;PositionDTO&gt;()
                    .Where(Restrictions.On&lt;PositionDTO&gt;(y =&gt; y.ColumnA).IsIn(a))
                    .And(Restrictions.On&lt;PositionDTO&gt;(y =&gt; y.ColumnB).IsIn(b))
                    .And(Restrictions.On&lt;PositionDTO&gt;(y =&gt; y.ColumnC).IsIn(c))
                    .List&lt;PositionDTO&gt;();


                using (ITransaction transaction = statelessSession.BeginTransaction())
                {
                    foreach (var t in p)
                    {
                        statelessSession.Delete(t);
                    }

                    transaction.Commit();
                }
            }
</code></pre>