How to delete data in DB efficiently using LinQ to NHibernate (one-shot-delete)

Producing software for customers, mostly using MS SQL but some Oracle, a decision was made to plunge into Nhibernate (and C#).

The task is to delete efficiently e.g. 10 000 rows from 100 000 and still stay sticked to ORM.

I've tried named queries - link already,

IQuery sql = s.GetNamedQuery("native-delete-car").SetString(0, "Kirsten");

sql.ExecuteUpdate();

but the best I have ever found seems to be:

 using (ITransaction tx = _session.BeginTransaction())
        {
            try
            {
                str开发者_StackOverflow中文版ing cmd = "delete from Customer where Id < GetSomeId()";

                var count = _session.CreateSQLQuery(cmd).ExecuteUpdate();
                ...

Since it may not get into dB to get all complete rows before deleting them.

My questions are:

If there is a better way for this kind of delete.

If there is a possibility to get the Where condition for Delete like this:

Having a select statement (using LinQ to NHibernate) => which will generate appropriate SQL for DB => we get that Where condition and use it for Delete.

---

If there is a better way for this kind of delete.

Yes, you could use HQL instead of SQL.

If there is a possibility to get the Where condition for Delete [using Expressions]:

No, AFAIK that's not implemented. Since NHibernate is an open source project, I encourage you to find out if anyone has proposed this, and/or discuss it on the mailing list.

---

Thanks for your quick reply. Now I've probably got the difference.

session.CreateSQLQuery(cmd).ExecuteUpdate();

must have cmd with Delete From DbTable. On the contrary the HQL way

session.CreateQuery(cmd).ExecuteUpdate();

needs cmd with Delete From MappedCollectionOfObjects.

In that case it possibly solves my other question as well.

---

There now is a better way with NHibernate 5.0:

var biggestId = GetSomeId();
session.Query<Customer>()
            .Where(c => c.Id < biggestId)
            .Delete();

Documentation:

    //
    // Summary:
    //     Delete all entities selected by the specified query. The delete operation is
    //     performed in the database without reading the entities out of it.
    //
    // Parameters:
    //   source:
    //     The query matching the entities to delete.
    //
    // Type parameters:
    //   TSource:
    //     The type of the elements of source.
    //
    // Returns:
    //     The number of deleted entities.
    public static int Delete<TSource>(this IQueryable<TSource> source);