CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
Thierry
Thierry

Reputation: 6457

How to use multiple tables with the same foreign key with Entity Framework

I need to define my 3 classes as follows:

public class Table1
{
   public Guid Table1CoreId {get;set;}
   public string Name {get;set;}

   public Table2 Table2 {get;set;}
   public Table3 Table3 {get;set;}
}

public class Table2
{
   Guid Table1CoreId {get;set;}
}

public class Table3
{
   Guid Table1CoreId {get;set;}
}

As you can see, I have Table2 and Table3 defined as properties in Table1.

I've tried to use EF fluent API to define how these should be created but I'm not getting the result I want. I need my table to be defined as follows:

Table1:

Table2:

Table3:

Table2 has a 1-to-1 relationship with Table1 while Table3 has a 1-to-M relationship. Both entries in Table2 and Table3 are optional.

I've tried the following: in Table2 & 3 definition classes I'm using, I used the following:

this.HasRequired(t => t.Table1)
    .WithMany()
    .HasForeignKey(t => t.Table1CoreId)
    .WillCascadeOnDelete(false);

But when I create a migration and update my database, it creates additional columns in Table1:

Table2_Table1CoreId
Table2_Id
Table3_Table1CoreId
Table3_Id

All the primary keys and foreign key are created in the various tables as far as I can see but I just don't want these additional columns to be created in Table1.

I hope this makes sense and someone can point me in the right direction on how to resolve this.

Upvotes: 1

Views: 1792

Answers (1)

Thierry
Thierry

Reputation: 6457

After discussing the issue further with my colleague, we ended up normalizing our table even further, so we introduced one additional table and we also removed a requirement of having one of the table having a 1-to-m relation, so all tables are now 1-to-1. All these changes made it a lot easier to define our classes and also made it a lot easier to resolved our EF issues.

The final changes are as follows:

public class Table1
{
    public Guid Table1CoreId {get;set;}
    public string Name {get;set;}
}

public class Table2
{
   Guid Table1CoreId {get;set;}
   int  Table2CoreId {get;set;}
}

public class Table3
{
   Guid Table2CoreId {get;set;}
}

public class Table4
{
   Guid Table2CoreId {get;set;}
}

In Table2 configuration class, I added the following:

public class Table2Configuration : EntityTypeConfiguration<Table2>
{
    ...
    this.HasOptional(t => t.Table3)
        .WithRequired(o => o.Table2)
        .WillCascadeOnDelete(true);

    this.HasOptional(t => t.Table4)
        .WithRequired(o => o.Table2)
        .WillCascadeOnDelete(true);
}

and our tables now look like this:

Table1:

  • Table1CoreId (PK)
  • Name

Table2:

  • Table1CoreId (PK, FK)
  • Table2CoreId

Table3:

  • Table2CoreId (PK, FK)
  • Id (PK)

Table4:

  • Table2CoreId (PK, FK)
  • Id (PK)

And as originally requested, there are no additional fields added in Table1.

Note: Based on my original question, I did manage to resolve the additional fields issue by adding the [ForeignKey("Table1CoreId, Id")] attribute to both Navigation properties (i.e. Table2 and Table3) defined in Table1 but never got to resolve it using EF Fluent API as we ended up redesigning the classes and tables.

Upvotes: 1

Related Questions