Two tables that have keys to each other on Entity Framework

Question

I have the following classes:

public class Condition 
{
    public int Id {get;set; }
    public Condition ParentCondition {get;set;}
    public Action ParentAction {get;set;}
    public string Whatever {get;set;}
}

public class Action 
{
    public int Id {get;set; }
    public Condition ParentCondition {get;set;}
    public Action ParentAction {get;set;}
    public string Whatever {get;set;}
}

So both, the condition and the action may have either a condition or an action as a parent (or none if both are null)

I tried to put this onto a SQL database but lost somehow. So something I would want to achieve would be something like (pseudo code)

TABLE Condition:

int ConditionParent
int ActionParent

Table Action:

int ConditionParent
int ActoinParent

I tried using fluent api:

  modelBuilder.Entity<Condition>()
                .ToTable("Condition")
                .HasOptional(c => c.ParentAction)
                .WithRequired(a => a.ParentCondition);

But when I do this, the Action table looks fine, but the migration code for the condition has no column for an action parent at all.

What has to be changed?

my bet is to make use of `Entity Framework Power Tools` instead of researching for solutions; Create tables and click the options inside your Visual Studio, you should know what you should write :) https://msdn.microsoft.com/en-us/data/jj593170.aspx — techspider, May 06 '16 at 19:16
That seems a bit like surrendering. I don't want to create a database to create code to create a database afterwards. — Ole Albers, May 06 '16 at 20:12
yes, i agree but there are certain scenarios which saves your time from researching code what has to be written :) — techspider, May 06 '16 at 21:05
@CodeNotFound Sorry. Mistyping. By reducing my realworld-problem to the required bits I forgot about renaming that class. Fixed it — Ole Albers, May 07 '16 at 09:32

score 1 · Accepted Answer · answered May 07 '16 at 16:17

I solved this by explicitly adding Ids:

public class Condition 
{
    public int Id {get;set; }
    public int? ParentConditionId {get;set;}
    public virtual Condition ParentCondition {get;set;}
    public int? ParentActionId {get;set; }
    public virtual Action ParentAction {get;set;}
    public string Whatever {get;set;}
}

public class Action 
{
    public int Id {get;set; }
    public int ParentConditionId {get;set;}
    public virtual Condition ParentCondition {get;set;}
    public int? ParentActionId {get;set;}
    public virtual Action ParentAction {get;set;}
    public string Whatever {get;set;}
}

If you didn't change the mapping this doesn't solve anything. The column isn't used by EF. — Gert Arnold, May 10 '16 at 12:19

score 0 · Answer 2 · answered May 07 '16 at 10:36

The Conditions table does have a column for its parent action! It's the primary key, that is a foreign key to Action at the same time.

Look at the (essential) creation code for the tables:

CREATE TABLE [dbo].[Actions](
    [Id] [int] NOT NULL,
    [ParentAction_Id] [int] NULL,
 CONSTRAINT [PK_dbo.Actions] PRIMARY KEY CLUSTERED ([Id]))

ALTER TABLE [dbo].[Actions] ADD CONSTRAINT [FK_dbo.Actions_dbo.Actions_ParentAction_Id]
    FOREIGN KEY([ParentAction_Id]) REFERENCES [dbo].[Actions] ([Id])

-- Here: primary key Id is foreign key to Condition
ALTER TABLE [dbo].[Actions] ADD CONSTRAINT [FK_dbo.Actions_dbo.Conditions_Id]
    FOREIGN KEY([Id]) REFERENCES [dbo].[Conditions] ([Id])

CREATE TABLE [dbo].[Conditions](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentCondition_Id] [int] NULL,
 CONSTRAINT [PK_dbo.Conditions] PRIMARY KEY CLUSTERED ([Id])
)

ALTER TABLE [dbo].[Conditions] ADD CONSTRAINT [FK_dbo.Conditions_dbo.Conditions_ParentCondition_Id]
    FOREIGN KEY([ParentCondition_Id]) REFERENCES [dbo].[Conditions] ([Id])

This is because Action-Condition is a 1:1 association. EF always implements required 1:1 associations by this PK/FK construction. It makes sense: an Action can't exist without its required Condition parent. The best way to guarantee that is for Action to "borrow" Condition's PK value.

Two tables that have keys to each other on Entity Framework

2 Answers2