模型实体框架许多加共享关系

模型实体框架许多加共享关系

本文介绍了模型实体框架许多加共享关系的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在实体框架(模型第一)中,我正在实现许多(例如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约束:

  • EF5导航:

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

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

09-05 01:47