很多人在NHibernate中删除级联

I have a scenario in a system which I've tried to simplify as best as I can. We have a table of (lets call them) artefacts, artefacts can be accessed by any number of security roles and security roles can access any number of artefacts. As such, we have 3 tables in the database - one describing artefacts, one describing roles and a many-to-many association table linking artefact ID to Role ID.

我在系统中有一个场景,我尽可能地尽量简化。我们有一个(让我们称之为)人工制品的表格,可以通过任意数量的安全角色访问人工制品,安全角色可以访问任意数量的人工制品。因此,我们在数据库中有3个表 - 一个描述人工制品,一个描述角色,一个多对多关联表将人工制品ID与角色ID相关联。

Domain wise, we have two classes - one for a role and one for an artefact. the artefact class has an IList property that returns a list of roles that can access it. (Roles however do not offer a property to get artefacts that can be accessed).

在领域方面,我们有两个类 - 一个用于角色,一个用于人工制品。 artefact类具有IList属性,该属性返回可以访问它的角色列表。 (但是角色不提供获取可以访问的人工制品的属性)。

As such, the nhibernate mapping for artefact contains the following;

因此,人工制品的nhibernate映射包含以下内容:

<bag name="AccessRoles" table="ArtefactAccess" order-by="RoleID" 
    lazy="true" access="field.camelcase-underscore" optimistic-lock="false">
    <key column="ArtefactID"/>
    <many-to-many class="Role" column="RoleID"/>
</bag>

This all works fine and if I delete an artefact, the association table is cleaned up appropriately and all references between the removed artefact and roles are removed (the role isn't deleted though, correctly - as we don't want orphans deleted).

这一切都很好,如果我删除一个人工制品,关联表将被适当地清理,删除的人工制品和角色之间的所有引用都将被删除(虽然正确地删除了该角色 - 因为我们不希望孤儿被删除)。

The problem is - how to delete a role and have it clear up the association table automatically. If I presently try to delete a role, I get a reference constraint as there are still entries in the association table for the role. The only way to successfully delete a role is to query for all artefacts that link to that role, remove the role from the artefact's role collection, update the artefacts and then delete the role - not very efficient or nice, especially when in the un-simplified system, roles can be associated with any number of other tables/objects.

问题是 - 如何删除角色并让它自动清除关联表。如果我目前尝试删除一个角色,我会得到一个引用约束,因为该角色的关联表中仍有条目。成功删除角色的唯一方法是查询链接到该角色的所有人工制品,从人工制品的角色集合中删除角色,更新人工制品然后删除角色 - 效率不高或不太好,尤其是在非简化的系统,角色可以与任何数量的其他表/对象相关联。

I need to be able to hint to NHibernate that I want this association table cleared whenever I delete a role - is this possible, and if so - how do I do it?

我需要能够向NHibernate提示我希望每当我删除一个角色时都要清除这个关联表 - 这是可能的,如果可以的话 - 我该怎么做?

Thanks for any help.

谢谢你的帮助。

5 个解决方案

#1

Since I was looking for this answer and found this thread on google (without an answer) I figured I'd post my solution to this. With three tables: Role, RolesToAccess(ManyToMany), Access.

由于我正在寻找这个答案并在谷歌上发现这个帖子(没有回答)我想我会发布我的解决方案。有三个表:Role,RolesToAccess(ManyToMany),Access。

Create the following mappings: Access:

创建以下映射:访问:

<bag name="Roles" table="RolesToAccess" cascade="none" lazy="false">
      <key column="AccessId" />
      <many-to-many column="AccessId" class="Domain.Compound,Domain" />
    </bag>

<bag name="RolesToAccess" cascade="save-update" inverse="true" lazy="false">
      <key column="AccessId" on-delete="cascade" />
      <one-to-many class="Domain.RolesToAccess,Domain" />
    </bag>

Roles:

<bag name="Accesses" table="RolesToAccess" cascade="none" lazy="false">
      <key column="RoleId" />
      <many-to-many column="RoleId" class="Domain.Compound,Domain" />
    </bag>

<bag name="RolesToAccess" cascade="save-update" inverse="true" lazy="false">
      <key column="RoleId" on-delete="cascade" />
      <one-to-many class="Domain.RolesToAccess,Domain" />
    </bag>

As mentioned above you can make the RolesToAccess properties protected so they don't pollute your model.

如上所述,您可以使RolesToAccess属性受到保护,这样它们就不会污染您的模型。

#2

What you say here:

你在这说什么

The only way to successfully delete a role is to query for all artefacts that link to that role, remove the role from the artefact's role collection, update the artefacts and then delete the role - not very efficient or nice, especially when in the un-simplified system, roles can be associated with any number of other tables/objects.

成功删除角色的唯一方法是查询链接到该角色的所有人工制品,从人工制品的角色集合中删除角色,更新人工制品然后删除角色 - 效率不高或不太好,尤其是在非简化的系统,角色可以与任何数量的其他表/对象相关联。

Is not necessary. Suppose you don't want to map the association table (make it a domain object), you can still perform deletes on both ends with minimal code.

没有必要。假设您不想映射关联表(使其成为域对象),您仍然可以使用最少的代码在两端执行删除。

Let's say there are 3 tables: Role, Artifact, and ArtifactAccess (the link table). In your mapping, you only have domain objects for Role and Artifact. Both have a bag for the many-many association.

假设有3个表:Role,Artifact和ArtifactAccess(链接表)。在映射中,您只有Role和Artifact的域对象。两者都有许多协会的包。

Role:

    <bag name="Artifacts" table="[ArtifactAccess]" schema="[Dbo]" lazy="true"
        inverse="false" cascade="none" generic="true">
        <key column="[ArtifactID]"/>

        <many-to-many column="[RoleID]" class="Role" />
    </bag>

Artifact:

    <bag name="Roles" table="[ArtifactAccess]" schema="[Dbo]" lazy="true"
        inverse="false" cascade="none" generic="true">
        <key column="[RoleID]"/>

        <many-to-many column="[ArtifactID]" class="Role" />
    </bag>

As you can see, both ends have inverse=false specified. The NHibernate documentation recommends you to choose one end of your association as the 'inverse' end, but nothing stops you from using both as 'controlling end'. When performing updates or inserts, this works from both directions without a hitch. When performing deletes of either one of the ends, you get a FK violation error because the association table is not updated, true. But you can solve this by just clearing the collection to the other end, before performing the delete, which is a lot less complex than what you do, which is looking in the 'other' end of the association if there are uses of 'this' end. If this is a bit confusing, here is a code example. If you only have one end in control, for your complex delete you need to do:

如您所见,两端都指定了inverse = false。 NHibernate文档建议您选择关联的一端作为“反向”结束,但没有什么能阻止您将两者用作“控制端”。执行更新或插入时,这可以从两个方向顺利进行。当执行任一端的删除时,您会收到FK违规错误,因为关联表未更新,为true。但是你可以通过在执行删除之前将集合清除到另一端来解决这个问题,这比你做的要简单得多,如果有'this'的用法,它会查找关联的“其他”端。 ' 结束。如果这有点令人困惑,这是一个代码示例。如果您只有一个控制端,对于复杂删除,您需要执行以下操作:

foreach(var artifact in role.Artifacts)
    foreach(var role in artifact.Roles)
        if(role == roleToDelete)
            artifact.Roles.Remove(role)
    artifact.Save();
roleToDelete.Delete();

What I do when deleting a role is something like

删除角色时我所做的就是这样

roleToDelete.Artifacts.Clear(); //removes the association record
roleToDelete.Delete(); // removes the artifact record

It's one extra line of code, but this way you don't need to make a decision on which end of the association is the inverse end. You also don't need to map the association table for full control.

这是一行额外的代码,但是这样你就不需要决定关联的哪一端是反向结束。您也不需要映射关联表以进行完全控制。

#3

You could make a mapping for the association table, and then call delete on that table where the Role_id is the value you are about to delete, and then perform the delete of the role itself. Should be fairly straightforward to do this.

您可以为关联表创建映射,然后在该表上调用delete,其中Role_id是您要删除的值,然后执行角色本身的删除。这样做应该相当简单。

#4

Although I believe NHibernate must provide a way to do this without having the collection in the roles C# class, you can always set this behaviour in SQL. Select on cascade delete for the FK in the database and it should be automatic, just watch out for NHib's cache.

虽然我相信NHibernate必须提供一种方法来实现这一点,而无需在C#类角色中使用该集合,但您始终可以在SQL中设置此行为。在数据库中为FK选择级联删除,它应该是自动的,只需要注意NHib的缓存。

But I strongly advice you to use this as a last resource.

但我强烈建议你使用它作为最后的资源。

#5

You need to create a mapping from Role to Artifact.

您需要创建从Role到Artifact的映射。

You can make it lazy-loading, and map it to a protected virtual member, so that it never actually gets accessed, but you need that mapping there for NHibernate to know that it has to delete the roles from the ArtefactAccess table

您可以使其延迟加载,并将其映射到受保护的虚拟成员,以便它实际上永远不会被访问,但您需要在那里为NHibernate进行映射以了解它必须从ArtefactAccess表中删除角色