NHibernate efficient Delete using LINQ Where condition

Question

Having a repository for NHibernate with LINQ queries like this

var q = from x in SomeIQueryable<SomeEntity> where x.A1 == a1 && x.B1 == b1 select x;

Is there a solution how to get this WHERE filter and apply it for "one-shot-delete" which seems to be only possible through HQL:

var cmd = string.Format("delete from SomeEntity where x.A1 = '{0}' and x.B1 = {1}", a1, b1);
session.CreateQuery(cmd).ExecuteUpdate();

score 13 · Answer 1 · answered Nov 01 '17 at 11:50

13

It is now possible with Nhibernate 5.0:

session.Query<SomeIQueryable>()
            .Where(x => x.A1 == a1 && x.B1 == b1)
            .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);

answered Nov 01 '17 at 11:50

Sÿl

585
6
8

The problem with this is that it appears to fail if there are mapped children of the entity being deleted, such as a bag (one-to-many). I get a FK constraint violation because the children have a FK to their parent, the entity being deleted. To get cascade deletes, I have to load the entity first, then use `session.Delete(entity)` – E-Riz Mar 04 '20 at 16:26
@E-Riz you can try "Inverse"ing your mapping with your child collection to change this behavior https://stackoverflow.com/questions/713637/inverse-attribute-in-nhibernate – Sÿl Apr 27 '20 at 10:47
How can I change the `Where` condition to `IN` clause instead of `==`? Say something like WHERE ID IN [1,2,3...]. – Amit Joshi Jun 24 '22 at 10:19
1

@AmitJoshi ``var ids = new int[] {1, 2, 3, ...};`` ``.Where(x => ids.Contains(x.Id))`` – Sÿl Jun 26 '22 at 21:17

score 6 · Answer 2 · answered Aug 11 '11 at 19:48

6

NH LINQ provider and the criteria/queryover API do not support conditional deletes/updates. HQL or raw SQL are the only options unless you are looking into extending NHibernate.

answered Aug 11 '11 at 19:48

Dmitry S.

8,373
2
39
49

That was right at the time of the answer, but there's a way using Linq to Sql now. See below – Sÿl Apr 11 '19 at 13:31

score 1 · Answer 3 · answered Oct 11 '13 at 22:19

1

(from x in NHSession.Query<SomeEntity>()
                  where x.A1 == a1 && x.B1 == b1 
                  select x).ForEach(y => { NHSession.Delete(y) });
        NHSession.Flush();

answered Oct 11 '13 at 22:19

Paul Aicher

19
2

1

Please post some explanation; code-only answers don't say much. – rgettman Oct 11 '13 at 22:42
1

The problem with this is that it loads all entities on the way just to delete them. – Ricardo Peres Nov 12 '14 at 12:21
2

This is not what we call efficient – Beatles1692 Dec 23 '14 at 11:05
The subject asks how to do "one-shot-delete", your solution will execute a DELETE statement for each instance of "SomeEntity" that matches the condition – Sÿl Jan 09 '19 at 14:28

score 1 · Answer 4 · answered Oct 01 '14 at 04:09

1

Currently, as of NH 4.0.1, it is not possible. There is, however, an open issue at Jira (NH-3659, https://nhibernate.jira.com/browse/NH-3659). There is an hackish solution based on a custom interceptor and SQL replacement described at http://weblogs.asp.net/ricardoperes/strongly-typed-delete-with-nhibernate, but I am working on a clean solution and will eventually submit a pull request.

answered Oct 01 '14 at 04:09

Ricardo Peres

13,724
5
57
74

I forgot to say that I am working on NH-3659, but so far have nothing to show. There are a couple of alternatives mentioned in this post: https://weblogs.asp.net/ricardoperes/deleting-entities-in-nhibernate (disclaimer: my own) – Ricardo Peres Nov 12 '14 at 12:20
I have submitted a pull request for NH-3659 - Strongly Typed Delete. The link is available at https://nhibernate.jira.com/browse/NH-3659. – Ricardo Peres Nov 12 '14 at 19:35

NHibernate efficient Delete using LINQ Where condition

4 Answers4