Updating an empty component in a <join> element results in INSERT instead of UPDATE
Description
NHibernate creates wrong SQL statements if you have an entity that has an optional <join> element that contains a component.
1. When the entity is inserted, a row for the <join> element is created even if the component in the <join> is completely empty (so the row in the join-table contains only NULLs other than the key-column). 2. When this entity is selected again, NHibernate doesn't create a component (like it doesn't create empty components outside of <join> elements). 3. When you then assign a new component to the entity that has some data, NHibernate tries to insert a row in the join-table again which fails because there will be a unique or primary key on the join-table.
At first there seems to be an easy solution: Don't insert rows in the join-table if all the columns would be NULL. But that would still fail if some external application creates such an empty row. I think a solution would be to always create and assign the join-component if there is a row in the join-table, even if all data columns are NULL (so this would be an exception to the usual "empty component = null" rule). Then the update code would know to create a UPDATE statement.
NHibernate creates wrong SQL statements if you have an entity that has an optional <join> element that contains a component.
1. When the entity is inserted, a row for the <join> element is created even if the component in the <join> is completely empty (so the row in the join-table contains only NULLs other than the key-column).
2. When this entity is selected again, NHibernate doesn't create a component (like it doesn't create empty components outside of <join> elements).
3. When you then assign a new component to the entity that has some data, NHibernate tries to insert a row in the join-table again which fails because there will be a unique or primary key on the join-table.
At first there seems to be an easy solution: Don't insert rows in the join-table if all the columns would be NULL. But that would still fail if some external application creates such an empty row.
I think a solution would be to always create and assign the join-component if there is a row in the join-table, even if all data columns are NULL (so this would be an exception to the usual "empty component = null" rule). Then the update code would know to create a UPDATE statement.