Hi

I have several (five) many-to-many sets in one class. I found convenient to use single "intermediate" table for this instead of five separate tables:


So instead of creating link_table1, link_table2, ..... with two columns each, I created only one link_table with columns: IdParent (not nullable), IdChild1, .... , IdChild5 (all idchild nullable)

Such a solution was performing very vell until .... I discovered that removing all the elements from one Set clears the other sets too. In generated sql I see why - until collection not becomes empty, NHibernate uses DELETE with two paramaters in WHERE to remove an item (IdParent and IdChild1 for example - "DELETE FROM link_table WHERE IdParent=x AND IdChild1=y"). When collection becomes empty the issued DELETE is "DELETE FROM link_table WHERE IdParent=x" and deletes all the links.

My question is: is there any configuration parameter or known workaround to use my solution safely (in other words to force DELETE to use two parameters always)? I would like to avoid creating separate intermadiate table for every many-to-many relation.

I would really avoid trying to use a single "link" table. In addition to confusing NHibernate, it prevents you from putting foreign keys on the parent and child ID columns. If you need to store additional information in this "link" table, such as the role that the "child" plays for the "parent", it can start getting ugly if these roles have different validation rules or should be foreign keys to different tables, depending on the type of parent and child.

Trying to use a single "link" table makes it weakly typed. Where possible you should design things strongly typed. This makes development easier and prevents many types of runtime errors. I can't think of a reason why you would want to query this link table and get back rows that refer to different parent/child types in the same query, so I don't know what advantage having a single table would give you, besides simply reducing the number of tables. In itself I don't think that's any advantage at all.

I understand the need of strongly-typed development of course, but ... :

- I cannot agree that using single-table way I cannot put foreign keys here - actually I have them already ( having NULL in the table doesn't break the relationship, at least in mssql)

- the idea of having single table comes from that, all the childs are of the same class (spreaded within several sets), so I thought it will be nice to keep the links together

But I understand that I am breaking some NHibernate rules - which can cause such problems as mine and maybe other (in futher NH versions).
I'm going to experiment with `where="arbitrary sql where condition"` parameter with collection mapping , but if it doesn't help I will follow your suggesion and abandon "single link table idea"

Thanks

Ok I did it. For those who are interested in:



and then I can work safely with "single link table" as below: