MVC modelbuilding: multiple foreign keys in table, code first EF

Question

I'm building an application revolving around a game and I'm keeping lists of 2 types of actions in my game-object:

public class Game
{
    public int Id {get;set;}

    List<Action> ActiveActionList {get;set}
    List<Action> PassiveActionList {get;set;}
    ... (several other variables)
}

public class Action
{
    public int Id {get;set;}
    Game Game {get;set;}
    ...(several other variables)
}

The idea here is that an action is either in the passive or active actionlist (never in both) and I put in a reference on the side of the action as well, so that when working with an action I can say something like:

if (currentAction.Game.Id == someVariable)
...

the problem I'm encountering is when I look to the database that was created. The 'Actions' table has all the normal variables I expected, but for the game-object references it shows the following columns:

Game_Id
Game_Id1
Game_Id2

I'm guessing I should do something in the override of the modelbuilder in DbContext to make sure there is only 1 column referencing Game_Id.

I tried to use:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Actie>().HasRequired<Spel>(s => s.Spel).WithMany(a => a.Actielijst).HasForeignKey(a => a.Spel).WillCascadeOnDelete(false);
        base.OnModelCreating(modelBuilder);
    }

based on the post here(Entity Framework 4.1 InverseProperty Attribute and ForeignKey), but now I'm getting another error:

The foreign key component 'Game' is not a declared property on type 'Action'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property.

I don't know how to fix this as I expect I'm just using the wrong configuration above in the modelbuilder, so I hope someone here can point me right :)

score 1 · Accepted Answer · edited May 23 '17 at 11:43

After some more research I found it's simply not possible to have multiple lists in one object refer to a single key in the objectype that fills the list. The way to handle this issue is to use the DataAnnotation 'InverseProperty' like shown here: https://stackoverflow.com/a/25366822/3191144

This means that it is required to have one reference-object for each list you want to keep. For my case I had to do the following:

public class Game
{
    public int Id {get;set;}

    [InverseProperty("Game")]
    List<Action> ActiveActionList {get;set;}
    [InverseProperty("PassiveGame")]
    List<Action> PassiveActionList {get;set;}
    ... (several other variables)
}

public class Action
{
    public int Id {get;set;}
    Game Game {get;set;}
    Game PassiveGame {get;set;}
    ...(several other variables)
}

Unfortunately this also means I'll have one column that is almost always null (PassiveActions are rarer than ActiveActions) but I guess that's the way it works. For more than two lists I suppose it's better to change the architecture or something and use subclasses to avoid getting a load of columns to simply keep track of which list the item is in.

Should someone find a way to have 1 column in the Action table as a link to the game and another way to keep track of the two lists, please do reply :)

MVC modelbuilding: multiple foreign keys in table, code first EF

1 Answers1