Entity Framework - code first - Too many navigation properties

Question

I have two tables created with Entity Framework code first that I would like some help with..!

Tables

1. AccountLinks, 3 composite keys
2. Guest, 3 composite foreign keys (?)

Table overview

SQL overview

As you can see I have ALOT of navigation properties in my database which I dont want.

Code for AccountLink

public class AccountLink
    {
        public AccountLink()
        {
            AccountLinkPermissionAccountLinkID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionAccountOwnerID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionGuestID = new HashSet<AccountLinkPermission>();
        }

        public AccountLink(int accountOwnerID, int guestID, DateTime dateCreated, DateTime dateStart, DateTime dateExpires)
        {
            AccountLinkPermissionAccountLinkID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionAccountOwnerID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionGuestID = new HashSet<AccountLinkPermission>();
            this.AccountOwnerID = accountOwnerID;
            this.GuestID = guestID;
            this.DateCreated = dateCreated;
            this.DateStart = dateStart;
            this.DateExpires = dateExpires;
        }

        [Key, Column(Order = 0)]
        public int AccountLinkID { get; set; }
        [Key, Column(Order = 1)]
        public int AccountOwnerID { get; set; }
        [Key, Column(Order = 2)]
        public int GuestID { get; set; }

        public DateTime DateCreated { get; set; }
        public DateTime DateStart { get; set; }
        public DateTime DateExpires { get; set; }

        [ForeignKey("AccountOwnerID")]
        public virtual AccountOwner AccountOwner { get; set; }

        [ForeignKey("GuestID")]
        public virtual Guest Guest { get; set; }

        public virtual ICollection<AccountLinkPermission> AccountLinkPermissionAccountLinkID { get; set; }
        public virtual ICollection<AccountLinkPermission> AccountLinkPermissionAccountOwnerID { get; set; }
        public virtual ICollection<AccountLinkPermission> AccountLinkPermissionGuestID { get; set; }
    }

Code for AccountLinkPermissions

public class AccountLinkPermission
    {
        public AccountLinkPermission()
        {

        }

        public AccountLinkPermission(int accountLinkID, int accountOwnerID, int guestID, int permissionID)
        {
            this.AccountLinkID = accountLinkID;
            this.AccountOwnerID = accountOwnerID;
            this.GuestID = guestID;
            this.PermissionID = permissionID;
        }

        [Key, Column(Order = 0)]
        public int AccountLinkID { get; set; }
        [Key, Column(Order = 1)]
        public int AccountOwnerID { get; set; }
        [Key, Column(Order = 2)]
        public int GuestID { get; set; }
        [Key, Column(Order = 3)]
        public int PermissionID { get; set; }

        [InverseProperty("AccountLinkPermissionAccountLinkID")]
        public virtual AccountLink AccountLink { get; set; }

        [InverseProperty("AccountLinkPermissionAccountOwnerID")]
        public virtual AccountLink AccountLinkAccountOwner { get; set; }

        [InverseProperty("AccountLinkPermissionGuestID")]
        public virtual AccountLink AccountLinkGuest { get; set; }

        [ForeignKey("PermissionID")]
        public virtual Permission Permission { get; set; }
    }

The reason to why I want 3 composite keys is because I want to prevent duplicates.

Why I use InverseProperty instead of ForeignKey

Because I'm using multiple foreign keys linked to the same table, EF is not able to determine by convention which navigation properties belong together. Instead of using the property [ForeignKey] I have to use [InverseProperty] which defines the navigation property on the other end of the relationship.

Code for User

public class User
    {
        public User()
        {
            UserRoles = new HashSet<UserRole>();
            AccountLinks = new HashSet<AccountLink>();
        }

        public User(string firstName, string lastName, string email,
            string password, string passwordSalt, int agreeUserAgreement)
        {
            UserRoles = new HashSet<UserRole>();
            AccountLinks = new HashSet<AccountLink>();

            this.FirstName = firstName ?? string.Empty;
            this.LastName = lastName ?? string.Empty;
            this.Email = email;
            this.DateRegistered = Helpers.TimeZoneExtension.GetCurrentDate();
            this.DateLastActive = Helpers.TimeZoneExtension.GetCurrentDate();
            this.Password = password;
            this.PasswordSalt = passwordSalt;
            this.IsDeleted = 0;
            this.AgreeUserAgreement = agreeUserAgreement;
        }

        [Key]
        public int UserID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public DateTime DateRegistered { get; set; }
        public DateTime DateLastActive { get; set; }
        public string Password { get; set; }
        public string PasswordSalt { get; set; }

        public string DefaultIpAddress { get; set; }

        public int IsDeleted { get; set; }

        public int AgreeUserAgreement { get; set; }

        public string UserRolesToString { get; set; }

        public virtual ICollection<UserRole> UserRoles { get; set; }
        public virtual ICollection<AccountLink> AccountLinks { get; set; }
    }

Code for AccountOwner

public class AccountOwner : User
{
    public AccountOwner()
    {
        UserRoles = new HashSet<UserRole>();
        AccountLinks = new HashSet<AccountLink>();
    }

    public AccountOwner(string firstName, string lastName, string email,
        string password, string passwordSalt, int agreeUserAgreement)
    {
        UserRoles = new HashSet<UserRole>();
        AccountLinks = new HashSet<AccountLink>();

        this.FirstName = firstName ?? string.Empty;
        this.LastName = lastName ?? string.Empty;
        this.Email = email;
        this.DateRegistered = DateTime.UtcNow.AddHours(1);
        this.DateLastActive = DateTime.UtcNow.AddHours(1);
        this.Password = password;
        this.PasswordSalt = passwordSalt;
        this.IsDeleted = 0;
        this.AgreeUserAgreement = agreeUserAgreement;
    }

    public virtual AccountUsagePremium AccountUsagePremium { get; set; }

    public virtual UploadDirectlyUsagePremium UploadDirectlyUsagePremium { get; set; }

    public override ICollection<UserRole> UserRoles { get; set; }
    public override ICollection<AccountLink> AccountLinks { get; set; }
}

Code for Guest

public Guest()
{
    UserRoles = new HashSet<UserRole>();
    AccountLinks = new HashSet<AccountLink>();
}

public Guest(string firstName, string lastName, string email,
    string password, string passwordSalt, int agreeUserAgreement)
{
    UserRoles = new HashSet<UserRole>();
    AccountLinks = new HashSet<AccountLink>();

    this.FirstName = firstName ?? string.Empty;
    this.LastName = lastName ?? string.Empty;
    this.Email = email;
    this.DateRegistered = TimeZoneExtension.GetCurrentDate();
    this.DateLastActive = TimeZoneExtension.GetCurrentDate();
    this.Password = password;
    this.PasswordSalt = passwordSalt;
    this.IsDeleted = 0;
    this.AgreeUserAgreement = agreeUserAgreement;
}

public override ICollection<UserRole> UserRoles { get; set; }
public override ICollection<AccountLink> AccountLinks { get; set; }

What I need help with

How do I remove all the navigation properties in my database using code first? I know I messed it up somewhere but that's all I know :)

1 Navigation property in AccountLinks, (User_UserID)

9 Navigtion properties in AccountLinkPermissions

Bonus question

In AccountLink table I have three composite keys, AccountLinkID, AccountOwnerID and GuestID. Is it possible to put auto increment, (identity seed), on AccountLinkID? How would I do that in EF code first?

Answer 1

Remove this from Guest and AccountOwner classes:

public override ICollection<UserRole> UserRoles { get; set; }
public override ICollection<AccountLink> AccountLinks { get; set; }

OnModelCreating:

modelBuilder.Entity<User>().Ignore(t => t.AccountLinks); 
modelBuilder.Entity<User>().Ignore(t => t.UserRoles); 
modelBuilder.Entity<AccountOwner>().ToTable("AccountOwners"); 
modelBuilder.Entity<Guest>().ToTable("Guests"); 

Entities:

public class AccountLink
{
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int AccountLinkID { get; set; }

    [Key, Column(Order = 1)]    
    public int AccountOwnerID { get; set; }

    [Key, Column(Order = 2)]
    public int GuestID { get; set; }

    public DateTime DateCreated { get; set; }
    public DateTime DateStart { get; set; }
    public DateTime DateExpires { get; set; }

    [ForeignKey("AccountOwnerID")]
    public virtual AccountOwner AccountOwner { get; set; }

    [ForeignKey("GuestID")]
    public virtual Guest Guest { get; set; }

    public virtual ICollection<AccountLinkPermission> AccountLinkPermissions { get; set; }
}

public class AccountLinkPermission 
{
    [Key, ForeignKey("AccountLink"), Column(Order = 0)]
    public int AccountLinkID { get; set; }

    [Key, ForeignKey("AccountLink"), Column(Order = 1)]
    public int AccountOwnerID { get; set; }

    [Key, ForeignKey("AccountLink"), Column(Order = 2)]
    public int GuestID { get; set; }

    [Key, Column(Order = 3)]
    public int PermissionID { get; set; }

    public virtual AccountLink AccountLink { get; set; }

    public virtual Permission Permission { get; set; }
}