configure related entities with the same key

Question

I have an entity type MyEntity that has a primary key string MyEntityCode

I want to make a second entity MyEntityInfo that are extended properties that some MyEntity's are logically associated.

That makes the relationship between these entities one-to-one, with one end optional -- MyEntity logically optionally has a MyEntityInfo, without a navigation property, and MyEntityInfo is required to have a single MyEntity (with a navigation property).

I want to encode this in SQL as MyEntityInfo having a primary key BaseEntityCode that's also a foreign key to MyEntity's MyEntityCode.

How do I configure this encoding in EF6 fluent configuration API.

Sample code

public class MyEntity {
  public string MyEntityCode {get; set;}
  public int SomeProperty {get; set;}
}

public class MyEntityInfo {
  public MyEntity BaseEntity {get; set;}
  public string BaseEntityCode {get; set;}
  public int OtherInfo {get; set;}
}

public MyEntityConfiguration : EntityConfiguration<MyEntity> {
  public MyEntityConfiguration(){
    HasKey(e => e.MyEntityCode);
  }
}

I thought I could configure MyEntityInfo as

public MyEntityInfoConfiguration : EntityConfiguration<MyEntityInfo> {
  public MyEntityInfoConfiguration(){
    HasKey(e => e.BaseEntityCode);
    HasRequired(e => e.BaseEntity).WithOptional().WithForeignKey(e => BaseEntityCode);
  }
}

but WithOptional() doesn't allow chaining to WithForeignKey

Doing the same but with WithMany() so that a foreign key is possible, the multiplicity constraint of one is violated:

Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be '1'.

Why don’t you have MyEntity be the base class of MyEntityInfo? — Neil.Work, Jul 19 '19 at 03:57
@Neil.Work I prefer not to use class inheritance to encode relationships between data. If that's the only way to get the schema I want, then that's how it's going to be, but the encoding rarely has advantages over compositional alternatives IMO. — Martijn, Jul 19 '19 at 08:43
@Neil.Work in this specific example, it seems really cumbersome to change an existing MyEntity, and add MyEntityInfo. You'd need a new instance because you need to construct a new subtype to replace the old supertype. — Martijn, Jul 19 '19 at 12:36
What about this? https://stackoverflow.com/questions/5668801/entity-framework-code-first-null-foreign-key — Ziv Weissman, Jul 21 '19 at 20:13

Gert Arnold · Accepted Answer · 2019-07-21T20:13:00.077

I thought I could configure MyEntityInfo as

   public class MyEntityInfoConfiguration : EntityTypeConfiguration<MyEntityInfo>
   {
      public MyEntityInfoConfiguration(){
        HasKey(e => e.BaseEntityCode);
        HasRequired(e => e.BaseEntity).WithOptional().WithForeignKey(e => BaseEntityCode);
      }
    }

Well, almost, just remove the WithForeignKey call!

public class MyEntityInfoConfiguration : EntityTypeConfiguration<MyEntityInfo>
{
    public MyEntityInfoConfiguration()
    {
        HasKey(e => e.BaseEntityCode);
        HasRequired(e => e.BaseEntity).WithOptional();
    }
}

Entity Framework 6 has only one implementation of 1:1 associations: the primary key of the dependent entity (here: MyEntityInfo) is the foreign key to the principal entity (here: MyEntity).

There is no WithForeignKey method because with your proposed mapping (without WithForeignKey) EF knows all it needs to know now for the only implementation of 1:1 it has in store.

The produced database model shows the primary key/foreign key dual role of BaseEntityCode:

CREATE TABLE [dbo].[MyEntities] (
    [MyEntityCode] [nvarchar](128) NOT NULL,
    [SomeProperty] [int] NOT NULL,
    CONSTRAINT [PK_dbo.MyEntities] PRIMARY KEY ([MyEntityCode])
)

CREATE TABLE [dbo].[MyEntityInfoes] (
    [BaseEntityCode] [nvarchar](128) NOT NULL,
    [OtherInfo] [int] NOT NULL,
    CONSTRAINT [PK_dbo.MyEntityInfoes] PRIMARY KEY ([BaseEntityCode])
)
...
ALTER TABLE [dbo].[MyEntityInfoes] 
    ADD CONSTRAINT [FK_dbo.MyEntityInfoes_dbo.MyEntities_BaseEntityCode]
    FOREIGN KEY ([BaseEntityCode]) REFERENCES [dbo].[MyEntities] ([MyEntityCode])

configure related entities with the same key

1 Answers1