模型实体框架许多加共享关系 [英] Model Entity Framework many-many plus shared relation

问题描述

在实体框架（模型第一）中，我正在实现许多（例如Composition-Anthology）关系，并具有必须在相关集中匹配的附加关系（Composer）。

In Entity Framework (model-first), I'm implementing a many-many (e.g. Composition-Anthology) relationship, with an additional relation (Composer) that must match within related sets.

如何在EF中正确创建此模型？

我目前有两个坏主意：

坏主意＃1

Bad Idea #1

通过在Composition and Anthology复合材料上创建主键，包含ComposerId和本地身份，EF对此进行了正确的约束。但是，这会导致立即出现的问题：

By making the primary keys on Composition and Anthology composites, containing both ComposerId and the local Identity, EF constrains this correctly. However this causes immediate problems:

• 与组合与选集相关的所有表格现在也有FK的ComposerId;对于DBA而言很痛苦。


• 我根本不能使用EF 5.0的EntitySet.Find（），只需要使用唯一的身份等。

坏主意＃2

Bad Idea #2

我可以实现CompositionAnthology枢轴在设计器中添加ComposerId，并将约束直接添加到SQL中。但是这样：

I could materialize the CompositionAnthology pivot table in the designer, adding ComposerId to it, and add a constraint directly to SQL. However this:

• 打破EF数据库创建/更新


• 打破实体导航/添加

注意：我的数据实际上模拟了一个不那么直观的参与模式，但这个比喻保持得很好。

Note: My data actually models a much less intuitive "engagement" model, but this metaphor holds up quite well.

编辑：我通过请求在这里发布了我实际模型的一部分，有机会我的目标可以遇到了不同的示意图。 （为了简洁起见，我删除了HashSet作业。）逻辑上，Composition代表了这种模式的参与，因为必须有一个相关的Engagement（具有匹配的帐户）才能获得认可。

I'm posting a portion of my actual model here by request, on the chance that my goals can be met with a different schematic representation. (I removed the HashSet assignments for brevity.) Logically, Composition represents Engagement in this model, because there must be a related Engagement (with matching Account) for an Endorsement to exist.

public partial class Account
{
    public int Id { get; set; }
    public string PrimaryEmail { get; set; }

    public virtual ICollection<Endorsement> EndorsementsGiven { get; set; }
    public virtual ICollection<Endorsement> EndorsementsReceived { get; set; }
    public virtual ICollection<Engagement> Engagements { get; set; }
    public virtual ICollection<EngagementEndorsement> EngagementEndorsements { get; set; }
}

public partial class EngagementEndorsement
{
    public int Endorsement_Id { get; set; }
    public int Engagement_Id { get; set; }
    public int Account_Id { get; set; }

    public virtual Endorsement Endorsement { get; set; }
    public virtual Engagement Engagement { get; set; }
    public virtual Account Account { get; set; }
}

public partial class Engagement
{
    public int Id { get; set; }
    public System.DateTime Start { get; set; }
    public System.DateTime End { get; set; }
    public string JobFunction { get; set; }

    public virtual Account Account { get; set; }
    public virtual ICollection<EngagementEndorsement> EngagementEndorsements { get; set; }
}

public partial class Endorsement
{
    public int Id { get; set; }
    public EndorsementStatus Status { get; set; }
    public EndorserRole EndorserRole { get; set; }
    public string Note { get; set; }

    public virtual Account Endorsee { get; set; }
    public virtual Account Endorser { get; set; }
    public virtual ICollection<EngagementEndorsement> EngagementEndorsements { get; set; }
}

我目前正在做坏主意＃2（见上文） - 创建数据库后，我应用附加的关系/约束：

I'm currently doing "Bad Idea #2" (see above) - After the database is created, I apply the additional relations/constraints:

-- --------------------------------------------------
-- Ensure Engagement-to-Endorsement AccountId match
-- --------------------------------------------------

ALTER TABLE [dbo].[Engagements]
ADD CONSTRAINT [UK_EngagementIdAccountId]
    UNIQUE NONCLUSTERED
        ([Id], [Account_Id])
    WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

ALTER TABLE [dbo].[EngagementEndorsements]
ADD CONSTRAINT [FK_EngagementIdAccountId]
    FOREIGN KEY ([Engagement_Id], [Account_Id])
    REFERENCES [dbo].[Engagements]
        ([Id], [Account_Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE [dbo].[Endorsements]
ADD CONSTRAINT [UK_EndorsementIdAccountId]
    UNIQUE NONCLUSTERED
        ([Id], [Endorsee_Id])
    WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

ALTER TABLE [dbo].[EngagementEndorsements]
ADD CONSTRAINT [FK_EndorsementIdAccountId]
    FOREIGN KEY ([Endorsement_Id], [Account_Id])
    REFERENCES [dbo].[Endorsements]
        ([Id], [Endorsee_Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

推荐答案

最终，在两个相关问题中，模式反馈（和缺乏EF反馈），我非常像上面的坏主意＃2所示。

Ultimately, based on good data schema feedback (and lack of EF feedback) in two related questions, I proceeded very much as shown in "Bad Idea #2", above.

我一直在工作有了这个，它符合我目前的所有需求。

I've been working with this and it meets all my current needs.

有关实施的其他部分的更多详细信息，请参阅：

See these for further details on the other bits of the implementation:

• SQL约束： https://dba.stackexchange.com / questions / 41387 / manymany-with-shared-relation


• EF5导航：自定义实体框架多对多导航属性

• SQL constraint: https://dba.stackexchange.com/questions/41387/manymany-with-shared-relation
• EF5 navigation: Custom Entity Framework many-to-many navigation property

这篇关于模型实体框架许多加共享关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！