Delete only relation no entities

jta · **Posted:** Tue Sep 18, 2007 5:39 am

I have a small problem in my use of wonderful nHibernate 1.2 against MSSQL 2005 :-)

I have searched the forum and the net and found the only usefull answer to implement this logic using SQL/Stored procedures.

I have an application where users can "tag" items, so the database structure is:

Item *---1 TagItems 1---* Tag

And in my model i have a collection of tags on an Item and a collection of Items on a Tag

Now i need to remove an tag from an item and still keep both entities in the database, in other words i want to delete from the TagItems table.

The only solution i found was using:

Code:

myItem.Tags.Remove(myTag);
myTag.Items.Remove(myItem);
mySession.SaveOrUpdate(myItem); //with cascade save-update on item

However i would like to avoid fetching all the data to do this "simple" operation, but specifying a query for the Delete method on ISession isn't possible as i am not deleting a mapped entitiy(if it is please enlighten me).

The other thing i tried to explore is specifying a delete cascade, but this seems like a bad idea when dealing with at many-to-many relation as no cascadestyle looks like they preserve both entities when the association is removed from one side.

As i sidenote is it possible to implement a custom cascade, and is this in anyway "plugable"/configurable or should i compile my own nhibernate version?

Any help is appreciated, even if its just a confirmation that i have to implement it using a custom SQL/Stored procedure

xasp · **Posted:** Tue Sep 18, 2007 5:47 am

Hello,

When using a many-to-many collection, I don't think it's even possible to make NHibernate delete the entities on the other side of the collection with cascade Delete (or other).

Just try it out: set your cascade to Delete (or All) and remove the entity from the collection. I think the TagItems record will be deleted, but the entity will remain untouched.

(as a precaution, take a backup of your data before actually testing this ;-) )

jta · **Posted:** Tue Sep 18, 2007 7:44 am

xasp wrote:

Just try it out: set your cascade to Delete (or All) and remove the entity from the collection. I think the TagItems record will be deleted, but the entity will remain untouched.

Hmmm.. that worked. Nice, but not quite what i expected but logical enough.

So the solution would be something like this:

Code:

Tag tagToDeleteFrom = sess.Get<Tag>(TagIdentifier);
Item itemToDelete = 
sess.CreateFilter(tagToDeleteFrom.Items, "where this.Id = ?").
SetInt32(0,ItemIdentifier)
.UniqueResult<Item>();

tagToDeleteFrom.Items.Remove(itemToDelete);
sess.SaveOrUpdate(tagToDeleteFrom);

I will keep it in the DB for now to avoid the overhead in fetching, but good to learn a little bit more about many-to-many mappings and cascade

xasp wrote:

(as a precaution, take a backup of your data before actually testing this ;-) )

excellent advice ;-)

thanks for your reply

jlockwood · **Posted:** Tue Sep 18, 2007 9:56 am

You may want to wrap your collections in custom collection so that your code looks a bit cleaner. That way you could call with something like this:

Code:

myItem.Tags.RemoveTag(myTag);
session.save(myItem);


public void RemoveTag(Tag tag)
{
   this.Tags.Remove(tag); 
   tag.Items.Remove(this); 
}

Just a thought.