CODEWITHSUNDEEP
nhibernate
zszep
zszep

Reputation: 4483

How can I effectively delete all parent entities that have no children via NHibernate LINQ?

I have the need to periodically purge some of my database tables. The purging consists of deleting all parents that have no children. The current implementation uses a stored procedure to complete this task. What I'd like to have is to eliminate the stored procedure and achieve the delete action via a NHibernate Linq query. E.g. I need a query that deletes all Invoices that have no items.

Upvotes: 0

Views: 142

Answers (2)

Rippo
Rippo

Reputation: 22424

Unless you want to fire lots of delete queries to your DB then the short answer using Linq or QueryOver you can't, unless you compose a list of ID's first then use that in a sub query. One shot deletes can be troublesome in any ORM

Most solutions use either a stored procedure or an embedded named query (either SQL or HQL).

Upvotes: 2

mridula
mridula

Reputation: 3281

Using a QueryOver, you can find out all the persistent objects of type Invoice that you wish to delete. But as Rippo said, this will fire several Delete queries. You can modify this a little and create an array of invoice Ids. And then create one SqlQuery that deletes all the records at once using YourSession.CreateSQLQuery.

var invoicesToDelete = YourSession.QueryOver<Invoice>().Where(invoice => invoice.items == null).List();
var invoiceIds = new List<int>();
foreach(var invoice in invoicesToDelete)
{
    invoiceIds.Add(invoice.Id);
}
string queryString = "delete from invoice where id in(";
foreach(var id in invoiceIds)
{
    queryString = queryString + id + ",";
}
queryString = queryString.TrimEnd(',');
queryString = queryString + ")";
ISQLQuery query = YourSession.CreateSQLQuery(queryString);
queryString.UniqueResult();
YourSession.Transaction.Commit();

The code looks messy, but it should be more efficient.

Upvotes: 1

Related Questions