NHibernate one-shot delete example

Question

I'm trying to get NHibernate to delete a collection issuing either a single delete statement or multiple statements. The problem is that either it generates the delete statements AFTER generating some "get current state" queries, or it just sets the children's parent_id to null and doesn't remove them (and still generates those reads).

Tables: TestP ([id] int not null; [name] nvarchar(50) ) TestC ([id] int not null; [id_p] int null; [other] nvarchar(50) )

Initial data

insert into testp (id, name) values(1,'dad')

delete from testc

insert into testc (id, id_p, other) values(1, 1,'son 1')
insert into testc (id, id_p, other) values(2, 1,'son 2')

Entities

public class Testp
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }

    public virtual ICollection<Testc> Children { get; set; }
}

public class Testc
{
    public virtual int Id { get; set; }
    public virtual int IdP { get; set; }
    public virtual string Other { get; set; }
}

Mappings

public class TestpMap : ClassMapping<Testp>
{

    public TestpMap()
    {
        Schema("dbo");
        Lazy(true);
        Id(x => x.Id, map => map.Generator(Generators.Assigned));
        Property(x => x.Name);

        Bag(x => x.Children, mapping =>
        {
            mapping.Key(k => k.Column("id_p"));
            mapping.Cascade(Cascade.DeleteOrphans|Cascade.All);
        },
        r => r.OneToMany());
    }
}

public class TestcMap : ClassMapping<Testc>
{
    public TestcMap()
    {
        Schema("dbo");
        Lazy(true);
        Id(x => x.Id, map => map.Generator(Generators.Assigned));
        Property(x => x.Other);
    }
}

Test 1

using (var tx = session.BeginTransaction())
        {
            var test = session.Query<Testp>().First();
            test.Children.Clear();

            test.Children.Add(new Testc(){Id=103,Other="nuevo"});

            tx.Commit();
        }

This generates the following SQL:

/* [expression] */select TOP (1) testp0_.Id as Id1_, testp0_.Name as Name1_ from dbo.Testp testp0_
go
exec sp_executesql N'/* load one-to-many engage5_html_generator.Testp.Children */ SELECT children0_.id_p as id3_1_, children0_.Id as Id1_, children0_.Id as Id0_0_, children0_.Other as Other0_0_ FROM dbo.Testc children0_ WHERE children0_.id_p=@p0',N'@p0 int',@p0=1
go
exec sp_executesql N'/* get current state engage5_html_generator.Testc */ SELECT testc_.Id, testc_.Other as Other0_ FROM dbo.Testc testc_ WHERE testc_.Id=@p0',N'@p0 int',@p0=103
go
exec sp_executesql N'/* insert engage5_html_generator.Testc */ INSERT INTO dbo.Testc (Other, Id) VALUES (@p0, @p1)',N'@p0 nvarchar(4000),@p1 int',@p0=N'nuevo',@p1=103
go
exec sp_executesql N'/* delete one-to-many row engage5_html_generator.Testp.Children */ UPDATE dbo.Testc SET id_p = null WHERE id_p = @p0 AND Id = @p1',N'@p0 int,@p1 int',@p0=1,@p1=1
go
exec sp_executesql N'/* delete one-to-many row engage5_html_generator.Testp.Children */ UPDATE dbo.Testc SET id_p = null WHERE id_p = @p0 AND Id = @p1',N'@p0 int,@p1 int',@p0=1,@p1=2
go
exec sp_executesql N'/* create one-to-many row engage5_html_generator.Testp.Children */ UPDATE dbo.Testc SET id_p = @p0 WHERE Id = @p1',N'@p0 int,@p1 int',@p0=1,@p1=103
go
exec sp_executesql N'/* delete engage5_html_generator.Testc */ DELETE FROM dbo.Testc WHERE Id = @p0',N'@p0 int',@p0=1
go
exec sp_executesql N'/* delete engage5_html_generator.Testc */ DELETE FROM dbo.Testc WHERE Id = @p0',N'@p0 int',@p0=2

(sorry for the wall of text) That is, one select, one insert, three updates and two deletes.

I was expecting one delete and one insert.

Test 2: NHibernate documentation says that you can force it to issue a delete "by discarding (ie. dereferencing) the original collection and returning a newly instantiated collection with all the current elements."

Doing test.Children = new List<Testc>(); as-is results in an exception: "A collection with cascade="all-delete-orphan" was no longer referenced by the owning entity instance"

Changing cascade options to All (mapping.Cascade(Cascade.All);) fixes it somewhat. The following SQL is generated:

exec sp_executesql N'/* load one-to-many engage5_html_generator.Testp.Children */ SELECT children0_.id_p as id3_1_, children0_.Id as Id1_, children0_.Id as Id0_0_, children0_.Other as Other0_0_, children0_.id_p as id3_0_0_ FROM dbo.Testc children0_ WHERE children0_.id_p=@p0',N'@p0 int',@p0=1
go
exec sp_executesql N'/* get current state engage5_html_generator.Testc */ SELECT testc_.Id, testc_.Other as Other0_, testc_.id_p as id3_0_ FROM dbo.Testc testc_ WHERE testc_.Id=@p0',N'@p0 int',@p0=102
go
exec sp_executesql N'/* insert engage5_html_generator.Testc */ INSERT INTO dbo.Testc (Other, id_p, Id) VALUES (@p0, @p1, @p2)',N'@p0 nvarchar(4000),@p1 int,@p2 int',@p0=N'nuevo',@p1=1,@p2=102
go
exec sp_executesql N'/* delete one-to-many engage5_html_generator.Testp.Children */ UPDATE dbo.Testc SET id_p = null WHERE id_p = @p0',N'@p0 int',@p0=1
go
exec sp_executesql N'/* create one-to-many row engage5_html_generator.Testp.Children */ UPDATE dbo.Testc SET id_p = @p0 WHERE Id = @p1',N'@p0 int,@p1 int',@p0=1,@p1=102
go

2 selects, 1 insert, 2 updates and NO deletes. This was a 'soft delete'.

Can anyone help me find a working example of this? I've been everywhere in the last 4 days trying to make it work. This is what I want: clearing the collection and adding items should result in one delete and many inserts.

Thank you very much.

Radim Köhler Radim Köhler · Accepted Answer · 2014-09-08T06:05:51

...That is, one select, one insert, three updates and two deletes.
I was expecting one delete and one insert...

This result is related to the mapping and we can improve it. The trick is inverse="true" setting of the collection mapping. Please, follow this link to get detailed understanding:

inverse = “true” example and explanation - y mkyong (Hibernate/Java, but the meaning is the same)

How to apply this setting with mapping by code we can observe here:

Mapping-by-Code - Set and Bag - by Adam Bar

i.e.:

Bag(x => x.Children, mapping =>
{
    ...
    mapping.Inverse(true);

EXTEND - one shot delete

As discussed in comments, it is our choice 1) to use bi-directional mapping and profit from inverese setting or 2) not. But it still won't be one delete statement only, because NHibernate does handle these steps from different point of view then "super simplification/optimization of WRITE SQL statements".

On the other hand, NHibernate was extended and provides more powerfule features for us, to step into that process and do these "smart bulk" WRITE statements on our own. This features family is called:

13.3. DML-style operations (13.3. DML-style operations)
NHibernate – Executable DML by Ayende

some cites:

This is a new feature of NHibernate that Fabio has recently ported... The feature is basically this, NHibernate can now execute set based operation on your model. This include all Data Modification Language operations, so we are talking about Update, Insert and Delete.

...

To execute an HQL DELETE, use the same IQuery.ExecuteUpdate() method:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();

String hqlDelete = "delete Customer c where c.name = :oldName";
// or String hqlDelete = "delete Customer where name = :oldName";
int deletedEntities = s.CreateQuery( hqlDelete )
        .SetString( "oldName", oldName )
        .ExecuteUpdate();
tx.Commit();
session.Close();

So, this way we can use our 1) Domain model, 2) HQL and 3) pretty powerful bulk operations...

NHibernate one-shot delete example

1 Answers