The silence was deafening!
Here's the solution that I implemented (SQL Server only but easy to adapt for another RDBMS).
I should have been able to do is use an <idbag> rather than a <bag>…but due to a bug (
http://jira.nhibernate.org/browse/NH-364 - please vote for this) they don’t work. The workaround is to set inverse=”true” on both sides the <many-to-many> relationship. As a result NHibernate will no longer try to persist changes. Now we can use a custom ISQLQuery to manage the association more efficiently. I chose to use a stored procedure to which we can pass XML that contains the current associations.
Here’s an example (I know that strictly there is no parent and child in a many to many but I did not want to tie the example to a specific domain):
Code:
CREATE PROCEDURE update_parent_child
@ParentID INT,
@XMLDoc NTEXT
AS
SET NOCOUNT ON
DECLARE @XMLHndl INT
EXEC sp_xml_preparedocument @XMLHndl OUTPUT, @XMLDoc
-- Delete associations that no longer exist
DELETE FROM parent_child
WHERE parent_id = @ParentID
AND child_id NOT IN
(
SELECT xmlID
FROM OPENXML (@XMLHndl, '//children/child', 2)
WITH (xmlID INT '@id')
)
-- Create new associations
INSERT INTO parent_child
(parent_id, child_id)
SELECT @ParentID,
xmlID,
FROM OPENXML (@XMLHndl, '//children/child', 2)
WITH (xmlID INT '@id')
WHERE xmlID NOT IN
(
SELECT child_id
FROM parent_child
WHERE parent_id = @ParentID
)
Note that for this process to be efficient both foreign keys in the many to many resolution table must be on the left hand side of an index (this would also have applied with the <idbag>).
Calling the SP is trivial:
Code:
public void ChangeParentChildAssociations(Parent parent)
{
ISQLQuery q = session.CreateSQLQuery("exec update_parent_child ?, ?");
q.SetInt32(0, parent.ID);
q.SetString(1, GetChildXml(parent));
// Must define a query result
q.AddScalar("ignored", NHibernateUtil.Int32);
q.UniqueResult();
}
public string GetChildXml(Parent parent){
StringBuilder tmp = new StringBuilder();
foreach (Child child in parent.Children)
{
tmp.Append("<child id=\"" + child.ID + "\"/>");
}
return "<children>" + tmp + "</children>";
}
Code generating this is trivial.
Login
FAQ
Search
