Linq to Nhibernate Bulk Update Query Equivelent?

Question

Hi,

Not sure if I'm missing anything here. Basically, I am looking for Linq to Nhibernate to do the following SQL statement:

update SomeTable
set SomeInteger = (SomeInteger + 1)
where SomeInteger > @NotSoMagicNumber

Is there any way to do that?

Thanks!

Answer 1

Like most (if not all) LINQ providers, LINQ to NHibernate only comes in useful in reading data.

To achieve what you want to do in NHibernate with the help of LINQ, you will want to fetch all of the relevant objects & update each one. Something like:

//NHibernate session initialisation & finalisation skipped for brevity

var relevantObjects = from i in session.Linq<SomeObject>()
                      where i.SomeInteger > notSoMagicNumber
                      select i;

foreach (SomeObject item in relevantObjects)
{
    i.SomeInteger++;
    session.Update(item);
}

Make sure you flush your session after all this, & wrap it all in a transaction to minimise the number of database updates.

All this said, depending on the size of your data, you may run into performance issues in using NHibernate for bulk operations. Using an IStatelessSession may help for that purpose, but I haven't tried it out myself.

UPDATE Turns out if you wrap it up in a transaction, you don't need to do session.Update or flush the session.

Answer 2

Linq (not Linq to NHibernate, Linq in general) does not have a bulk update verb like SQL has. If you need the efficiency of the bulk update statement like yours, I'd just stick to SQL.