How to define a domain model where the Primary Key is also the Foreign Key with fluent api

Question

My problem is similar to Is it possible to have a relation where the foreign key is also the primary key? but I have to do this with Fluent API.

I have basically the same situation as described in the question, but I cannot use annotations on my domain models due to our coding standards. Here is a bit of code (Clarified):

Domain Classes:

public class Table1
{
    public long ID { get; set; }
    public int SubTableType { get; set; }
    ...

    public Table2 Table2 { get; set; }
    public Table3 Table3 { get; set; }
    public List<Table4> Table4s { get; set; }
    public List<Table5> Table5s { get; set; }
}

public class Table2
{
    public long ID { get; set; }
    public string Location { get; set; }
    public string Task { get; set; }
    ...

    public Table1 Table1 { get; set; }
    public Table6 Table6 { get; set; }
    public List<Table7> Table7s { get; set; }
}

public class Table3
{
    public long ID { get; set; }
    public string DescriptionAndLocation { get; set; }
    ...

    public Table1 Table1 { get; set; }
}

Configuration Classes:

internal class Table1Configuration : EntityTypeConfiguration<Table1>
{
    public Table1Configuration()
    {
        ToTable("Table1");

        HasKey(so => so.ID);

        Property(so => so.SubTableType)
            .IsRequired();
        Property(so => so.ID)
            .IsRequired()
            .HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
        ...
    }
}

internal class Table2Configuration : EntityTypeConfiguration<Table2>
{
    public Table2Configuration()
    {
        ToTable("Table2");

        HasKey(bc => bc.ID);

        Property(bc => bc.ID)
            .IsRequired();
        Property(bc => bc.Location)
            .IsOptional()
            .HasColumnType("nvarchar")
            .HasMaxLength(50);
        Property(bc => bc.Task)
            .IsOptional()
            .HasColumnType("nvarchar")
            .HasMaxLength(4000);
        ...

        HasRequired(bc => bc.Table1)
            .WithOptional(so => so.Table2);
        HasRequired(bc => bc.Table8)
            .WithMany(bot => bot.Table2s)
            .HasForeignKey(bc => bc.Tabe8ID);
    }
}

internal class Table3Configuration : EntityTypeConfiguration<Table3>
{
    public Table3Configuration()
    {
        ToTable("Table3");

        HasKey(hic => hic.ID);

        Property(hic => hic.DescriptionAndLocation)
            .IsOptional()
            .HasColumnType("nvarchar")
            .HasMaxLength(4000);
        Property(hic => hic.ID)
            .IsRequired()
            .HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);

        HasRequired(hic => hic.Table1)
            .WithOptional(so => so.Table3);
    }
}

When I run this code I get the error:

Invalid column name 'Table2_ID'.

score 1 · Answer 1 · answered May 03 '18 at 15:49

1

I would try something like this:

modelBuilder.Entity<Table1>().HasKey(t => t.ID);
modelBuilder.Entity<Table1>().Property(t =>t.ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

modelBuilder.Entity<Table1>()
            .HasOptional(t1 => t1.Table2)
            .WithRequired(t2 => t2.Table1).Map(m => m.MapKey("ID"));

answered May 03 '18 at 15:49

sebsch

131
2
8

Thanks, but I still get the Exception "ID: Name: Each property name in a type must be unique. Property name 'ID' is already defined." – May 03 '18 at 16:01

Ivan Stoev · Accepted Answer · 2018-05-03T16:24:23.677

1

What you are asking is the so called Shared Primary Key Associations, which is the standard (and better supported) EF6 model for one-to-one relationships.

Rather than removing the ID property, you should remove the MapKey call which is used to define a shadow FK property (which you don't need).

Since the property called ID by convention is a PK and required, basically all you need is this:

HasRequired(hic => hic.Table1)
    .WithOptional(so => so.Table2); // or Table3

or the explicit equivalent of [Key] / [ForeignKey] combination:

HasKey(hic => hic.ID);

HasRequired(hic => hic.Table1)
    .WithOptional(so => so.Table2); // or Table3

Exactly as the example for Configuring a Required-to-Optional Relationship (One-to–Zero-or-One) from the documentation.

edited May 03 '18 at 16:24

answered May 03 '18 at 16:13

Ivan Stoev

195,425
15
312
343

Now I get the exception "Invalid column name 'Table2_ID'." Am I defining a 1 to 1 relationship on one to one or zero relationship? – May 03 '18 at 16:29
Yes, you do (at least with what you have shown). 1 Table1 -> 0..1 Table2. You shouldn't be getting the aforementioned exception, except if you have something not shown, like `ICollection` property in Table2. – Ivan Stoev May 03 '18 at 16:36
I'm modifying the question with some changes. – May 03 '18 at 16:40
You shouldn't have removed the navigation property. All I recommended was to remove the `MapKey` from the fluent configuration. As you can see at the end of the answer, the Table2 / Table3 should have `ID` *and* `Table1` property. Exactly as in your original explanation. – Ivan Stoev May 03 '18 at 17:20
I'm going to scrap all of my changes and do just that. – May 03 '18 at 17:25
I just changed the code to clarify the as is. All navigation properties are list, but I removed most of the fields. – May 03 '18 at 17:46
This is not gonna work. Apparently your original issue is resolved and now you are getting error from another improper mapping - could be from Table6, or Table8 etc. - the posted model and configuration are incomplete, the new issue is not reproducible, so there is nothing more I can help. Create a clean new project, add just Table1, Table2 and Table3 and relevant fluent configuration, and you'll see that there is no error. And btw, Table3 ID should not have `.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);`. Good luck. – Ivan Stoev May 03 '18 at 18:18
I removed those tables and only left tables 1, 2 and 3. I still get the Table1_ID error. I'm scrapping the entire entity and building it from scratch. I'm deleting the question and will start from scratch. – May 03 '18 at 18:23
I ended up having to make the references to Tables2 and 3 unidirectional and it worked. – May 03 '18 at 19:36

How to define a domain model where the Primary Key is also the Foreign Key with fluent api

2 Answers2