EF Code First Foreign Key Relationship

Question

I have the the following situation:

• I have a few accounts and a few gametables.
• Each gametable has a creator.
• Each accounts can sit on a table (but doesn't have to).

My model classes look like this:

public class GameTable
{ 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id
    {
        get;
        set;
    }

    [Required]
    public string Name { get; set; }

    [Required]
    public string Description { get; set; }

    public Guid CreatorId { get; set; }

    [ForeignKey(nameof(CreatorId))]
    public Account Creator { get; set; }
}

public class Account
{
    public Account()
    {
        Elo = 1000;
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required]
    [MaxLength(15)]
    [MinLength(4)]
    public string Name { get; set; }

    [Required]
    [MaxLength(32)]
    [MinLength(32)]
    public string PasswordHash { get; set; }

    public int Elo { get; set; }

    public bool IsAdmin { get; set; }

    public Guid? CurrentTableId { get; set; }

    [ForeignKey(nameof(CurrentTableId))]
    public virtual GameTable CurrentTable { get; set; }
}

My context is currently looking like this:

[Export(typeof(MyDbContext)), PartCreationPolicy(CreationPolicy.Shared)]
[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class MyDbContext : DbContext
{
    public MyDbContext() : base("DbContextCon")
    {
        Database.SetInitializer<PiksenDbContext>(new CreateDatabaseIfNotExists<PiksenDbContext>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }

    public virtual DbSet<Account> Accounts { get; set; }
    public virtual DbSet<SecurityToken> SecurityTokens { get; set; }
    public virtual DbSet<GameTable> GameTables { get; set; }
}

Unfortunately it doesn't work with both foreign key set up like this. The following message is giving my some headache at the moment:

System.InvalidOperationException: 'Unable to determine the principal end of an association between the types 'namespace.GameTable' and 'namespace.Account'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.'

However, when I comment out one of the FK-Relationships, everything is fine. It seems like there is some sort of conflict between those two.

The result I am trying to get is one nullable FK in Account called CurrentTableId and a non-nullable FK in GameTable called CreatorId.

Answer 1

On each side of the FK relationship you can (should normally) have Navigation Properties. And when there are multiple FK relationships, you can use the InversePropertyAttribute to specify which FK a pair of Navigation Properties represents.

EG

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ef62test
{
    class Program
    {

        public class GameTable
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public Guid Id
            {
                get;
                set;
            }

            [Required]
            public string Name { get; set; }

            [Required]
            public string Description { get; set; }

            public Guid CreatorId { get; set; }

            [ForeignKey(nameof(CreatorId))]
            public Account Creator { get; set; }

            [InverseProperty(nameof(Account.CurrentTable))]
            public ICollection<Account> CurrentPlayers { get; } = new HashSet<Account>();
        }

        public class Account
        {
            public Account()
            {
                Elo = 1000;
            }

            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public Guid Id { get; set; }

            [Required]
            [MaxLength(15)]
            [MinLength(4)]
            public string Name { get; set; }

            [Required]
            [MaxLength(32)]
            [MinLength(32)]
            public string PasswordHash { get; set; }

            public int Elo { get; set; }

            public bool IsAdmin { get; set; }

            public Guid? CurrentTableId { get; set; }

            [ForeignKey(nameof(CurrentTableId))]
            public virtual GameTable CurrentTable { get; set; }

            [InverseProperty(nameof(GameTable.Creator))]
            public ICollection<GameTable> CreatedTables { get; } = new HashSet<GameTable>();
        }


        public class MyDbContext : DbContext
        {
            public MyDbContext() : base("DbContextCon")
            {
                Database.SetInitializer<MyDbContext>(new CreateDatabaseIfNotExists<MyDbContext>());
            }

            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
            }

            public virtual DbSet<Account> Accounts { get; set; }
            //public virtual DbSet<SecurityToken> SecurityTokens { get; set; }
            public virtual DbSet<GameTable> GameTables { get; set; }
        }
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<MyDbContext>());

            using (var db = new MyDbContext())
            {
                db.Database.CreateIfNotExists();

            }



            Console.WriteLine("Hit any key to exit.");
            Console.ReadKey();
        }
    }
}

Answer 2

It seams you want to create a self reference GameTable table. To do this you need to add a Collection property to Account table like..

public virtual ICollection<GameTable> Children { get; set; }

Whole code block looks like

    public class GameTable
{ 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id
    {
        get;
        set;
    }

    [Required]
    public string Name { get; set; }

    [Required]
    public string Description { get; set; }

    public Guid CreatorId { get; set; }

    [ForeignKey(nameof(CreatorId))]
    public Account Creator { get; set; }
}

    public class Account
    {
    public Account()
    {
        Elo = 1000;
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required]
    [MaxLength(15)]
    [MinLength(4)]
    public string Name { get; set; }

    [Required]
    [MaxLength(32)]
    [MinLength(32)]
    public string PasswordHash { get; set; }

    public int Elo { get; set; }

    public bool IsAdmin { get; set; }

    public Guid? CurrentTableId { get; set; }

    [ForeignKey(nameof(CurrentTableId))]
    public virtual GameTable CurrentTable { get; set; }
    public virtual ICollection<GameTable> Children { get; set; }
    }

Hope it will solve your problem.