transactions and delete using fluent nhibernate

Question

I am starting to play with (Fluent) nHibernate and I am wondering if someone can help with the following. I'm sure it's a total noob question.

I want to do:

   delete from TABX where name = 'abc'

where table TABX is defined as:

   ID int
   name varchar(32)
   ...

I build the code based on internet samples:

 using (ITransaction transaction = session.BeginTransaction())
                {
                    IQuery query = session.CreateQuery("FROM TABX WHERE name = :uid")
                        .SetString("uid", "abc");
                    session.Delete(query.List<Person>()[0]);
                    transaction.Commit();
                }

but alas, it's generating two queries (one select and one delete). I want to do this in a single statement, as in my original SQL. What is the correct way of doing this?

Also, I noticed that in most samples on the internet, people tend to always wrap all queries in transactions. Why is that? If I'm only running a single statement, that seems an overkill. Do people tend to just mindlessly cut and paste, or is there a reason beyond that? For example, in my query above, if I do manage it to get it from two queries down to one, i should be able to remove the begin/commit transaction, no?

if it matters, I'm using PostgreSQL for experimenting.

Answer 1

In NHibernate, I've noticed it is most common to do a delete with two queries like you see. I believe this is expected behavior. The only way around it off the top of my head is to use caching, then the first query could be loaded from the cache if it happened to be run earlier.

As far as wrapping everything in a transaction: in most databases, transactions are implicit for every query anyways. The explicit transactions are just a guarantee that the data won't be changed out from under you mid-operation.

Answer 2

Your first query comes from query.List<Person>().

Your actual delete statement comes from session.Delete(...)

Usually, when you are dealing with only one object, you will use Load() or Get().

Session.Load(type, id) will create the object for you without looking it up in the database . However, as soon as you access one of the object's properties, it will hydrate the object.

Session.Get(type, id) will actually look up the data for you.

As far as transactions, this is a good article explaining why it is good to wrap all of your nHibernate queries with transactions.

http://nhprof.com/Learn/Alerts/DoNotUseImplicitTransactions

Answer 3

You can do a delete in one step with the following code:

session.CreateQuery("DELETE TABX WHERE name = :uid")
       .SetString("uid", "abc")
       .ExecuteUpdate();

However, by doing it that way you avoid event listener calls (it's just mapped to a simple SQL call), cache updates, etc.