问题描述
在实体框架(模型第一)中,我正在实现许多(例如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
这篇关于模型实体框架许多加共享关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!