Many-to-many and HQL batch delete

Question

I have two entities with many-to-many relationship defined on them.

<set name="TreasuryCodes" table="ServiceProviderAccountTreasuryCode" lazy="true" cascade="all">
  <key column="ServiceProviderAccountId" />
  <many-to-many column="TreasuryCodeId" class="TreasuryCode" />
</set>

<set name="ServiceProviderAccounts" table="ServiceProviderAccountTreasuryCode" lazy="true" inverse="true" cascade="all">
  <key column="TreasuryCodeId" />
  <many-to-many column="ServiceProviderAccountId" class="ServiceProviderAccount" />
</set>

Now I want to delete all ServiceProviderAccounts by ServiceProviderId. I write this code:

    public void DeleteAllAccount(int serviceProviderId)
    {
        const string query = "delete ServiceProviderAccount spa where spa.ServiceProvider.Id = :serviceProviderId";
        repository.Session.CreateQuery(query)
            .SetInt32("serviceProviderId", serviceProviderId)
            .ExecuteUpdate();
        repository.Session.Flush();
    }

and I receive this exception:

Test method Test.ServiceRepositoryTest.DeleteAllAccountTest threw exception: 
NHibernate.Exceptions.GenericADOException: could not execute update query[SQL: delete from ServiceProviderAccount where ServiceProviderId=?] ---> System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FKBC88A84CB684BF79". The conflict occurred in database "Test", table "dbo.ServiceProviderAccountTreasuryCode", column 'ServiceProviderAccountId'.
The statement has been terminated.

I'm confused, as I have defined cascade on the entity, shouldn't nhibernate remove rows from ServiceProviderAccountTreasuryCode?

UPDATE

ok, looks like ExecuteUpdate is not looking for NHibernate cascade, probably because it's not loading entities before deleting it? Anyway is there any other way to delete from ServiceProviderAccountTreasuryCode table and then from ServiceProviderAccounts via HQL? I know I can use cascades on database, but I want to avoid that. What I want is to delete rows from many-to-many association table by HQl. Is it possible? Or I should use plain SQL?

Baz1nga Baz1nga · Accepted Answer · 2011-02-27T07:47:12

looks like you have a referential integrity problem i.e a foregin key relation ship where the id that you are deleting is being referenced somewhere else and that table will end up referencing nothing. if that is what you want to do then you can run the Truncate command but I am not sure why you will do that..

I would suggest you do a normal delete i.e using the nhibernate session and Linq like below:

 foreach(var sessionProvider in Session.Linq<ServiceProviderAccount >().Where(x=>x.ServiceProvider.Id==servinceProviderId))
       Session.Delete(sessionProvider);

Now note this is not at all a bad way to do your deletion as they are not fired against the dB immediately and is part of the Session till your transaction is committed and this should handle your referential integrity problems if your mappings are defined crrectly.

Hope this works..

Many-to-many and HQL batch delete

2 Answers