EF Framework Code First - Not Generating the Correct DB Script

I have created my context classes and in EF.Below is the two entities I have trouble when the scripts are created from EF.

To explain the relationship between Account and the Post, basically 1 Account can have 0 or many Posts.

Below are the 3 scenarios I am specifically after, as the EF is not generating the script properly(as I understand).

1 Account can create many Posts.
1 Account can modify many Posts.
1 Account can publish many Posts.

Below is my code for the two classes;

[Table("Account")]
public class Account : IModificationHistory
{
    [Key]
    public int ID { get; set; }

    [Required]
    [Column("firstName")] //db column name
    [MaxLength(255, ErrorMessage = "First name should be 255 characters or less")]
    [Display(Name = "First Name")] //displayed in UI
    public string FirstName { get; set; }

    [Required]
    [Column("lastName")] //db column name
    [MaxLength(255, ErrorMessage = "Last name should be 255 characters or less")]
    [Display(Name = "Last Name")] //displayed in UI
    public string LastName { get; set; }

    [Required] 
    [Column("emailAddress")] //db column name
    [MaxLength(255, ErrorMessage = "Email address should be 255 characters or less")]
    [DataType(DataType.EmailAddress)] //for UI presentation only
    [Display(Name = "Email Address")] //displayed in UI
    [EmailAddress(ErrorMessage = "Invalid email address format")]
    public string EmailAddress { get; set; }

    [Required]
    [Column("password")]
    [DataType(DataType.Password)]
    public string Password { get; set; }

    [Column("status")]
    public int Status { get; set; }

    [Column("role")]
    public int Role { get; set; }

    [Column("salt")]
    public string Salt { get; set; }

    [Column("dateCreated")]
    public DateTime DateCreated { get; set; }

    [Column("dateModified")]
    public DateTime DateModified { get; set; }


    public List<Post> ListPost { get; set; }

}

[Table("Post")]
public class Post : IModificationHistory
{
    [Key]
    public int ID { get; set; }

    [Required(ErrorMessage = "Title is mandatory")]
    [Column("title")]
    [MaxLength(255, ErrorMessage = "Title should be 255 characters or less")]
    public string Title { get; set; }

    [Required(ErrorMessage = "Content is mandatory")]
    [Column("content")]
    public string Content { get; set; }

    [Column("incidentID")]
    [Display(Name = "Related Incident")]
    public string IncidentID { get; set; }

    [Column("tagID")]
    [Display(Name = "Tag")]
    public string TagID { get; set; }

    [Column("dateModified")]
    public DateTime DateModified { get; set; }

    [Column("dateCreated")]
    public DateTime DateCreated { get; set; }

    [Column("datePublished")]
    public DateTime DatePublished { get; set; }

    [Column("status")]
    public int Status { get; set; }

    [Column("contactGroupID")]
    public int ContactGroupID { get; set; }

    //foreign key reference to Account table
    [Column("createdByAccountID")]
    public int CreatedByAcccountID { get; set; }

    //foreign key reference to Account table
    [Column("modifiedByAccountID")]
    public int ModifiedByAccountID { get; set; }        

    //foreign key reference to Account table
    [Column("publishedByAccountID")]
    public int PublishedByAccountID { get; set; }


    public Account ModifiedByAccount { get; set; }

    public Account CreatedByAccount { get; set; }

    public Account PublishedByAccount { get; set; }

}

when I run the add-migration command and following is the script generated by it

CreateTable(
            "dbo.Account",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    firstName = c.String(nullable: false, maxLength: 255),
                    lastName = c.String(nullable: false, maxLength: 255),
                    emailAddress = c.String(nullable: false, maxLength: 255),
                    password = c.String(nullable: false),
                    status = c.Int(nullable: false),
                    role = c.Int(nullable: false),
                    salt = c.String(),
                    dateCreated = c.DateTime(nullable: false),
                    dateModified = c.DateTime(nullable: false),
                })
            .PrimaryKey(t => t.ID);
        
        CreateTable(
            "dbo.Post",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    title = c.String(nullable: false, maxLength: 255),
                    content = c.String(nullable: false),
                    incidentID = c.String(),
                    tagID = c.String(),
                    dateModified = c.DateTime(nullable: false),
                    dateCreated = c.DateTime(nullable: false),
                    datePublished = c.DateTime(nullable: false),                      
                    status = c.Int(nullable: false),
                    contactGroupID = c.Int(nullable: false),
                    modifiedByAccountID = c.Int(nullable: false),
                    createdByAccountID = c.Int(nullable: false),
                    publishedByAccountID = c.Int(nullable: false),
                    CreatedByAccount_ID = c.Int(),
                    Account_ID = c.Int(),
                })
            .PrimaryKey(t => t.ID)
            .ForeignKey("dbo.Account", t => t.CreatedByAccount_ID)
            .ForeignKey("dbo.Account", t => t.modifiedByAccountID, cascadeDelete: true)
            .ForeignKey("dbo.Account", t => t.publishedByAccountID, cascadeDelete: true)
            .ForeignKey("dbo.Account", t => t.Account_ID)
            .Index(t => t.modifiedByAccountID)
            .Index(t => t.publishedByAccountID)
            .Index(t => t.CreatedByAccount_ID)
            .Index(t => t.Account_ID);

In the script my issue is, it has created two columns "createdByAccountID = c.Int(nullable: false)" column and "CreatedByAccount_ID = c.Int()," column which I think serves the same purpose and has created another extra column "Account_ID = c.Int(),". Can anyone point out what am I doing wrong in here please ?

Thanks.

Solution

I have found the issue for the above behavior from EF. It is because I haven't configured the InverseProperty.

In the Post class, I have made the below changes;

I have removed the below properties

 [Column("createdByAccountID")]
 public int CreatedByAccountID { get; set; }

 [Column("modifiedByAccountID")]
 public int ModifiedByAccountID { get; set; }

 [Column("publishedByAccountID")]
 public int PublishedByAccountID { get; set; }

And modified the below properties

 [Column("createdByAccountID")]
 public Account CreatedByAccount { get; set; }

 [Column("modifiedByAccountID")]
 public Account ModifiedByAccount { get; set; }

 [Column("publishedByAccountID")]
 public Account PublishedByAccount { get; set; }

And in the Account class I have made the below changes;

Removed the following property

 public List<Post> ListPost { get; set; }

And added the following properties

 [InverseProperty("CreatedByAccount")]
 public List<Post> ListPostCreated { get; set; }

 [InverseProperty("ModifiedByAccount")]
 public List<Post> ListPostModified { get; set; }

 [InverseProperty("PublishedByAccount")]
 public List<Post> ListPostPublished { get; set; }

After making the above changes, when I executed the add-migration, correct db script was generated. Below is the script that was generated;

CreateTable(
        "dbo.Post",
        c => new
            {
                ID = c.Int(nullable: false, identity: true),
                title = c.String(nullable: false, maxLength: 255),
                content = c.String(nullable: false),
                incidentID = c.String(),
                tagID = c.String(),
                dateModified = c.DateTime(nullable: false),
                dateCreated = c.DateTime(nullable: false),
                datePublished = c.DateTime(nullable: false),                      
                status = c.Int(nullable: false),
                contactGroupID = c.Int(nullable: false),
                CreatedByAccount_ID = c.Int(),
                ModifiedByAccount_ID = c.Int(),
                PublishedByAccount_ID = c.Int(),
            })
        .PrimaryKey(t => t.ID)
        .ForeignKey("dbo.Account", t => t.CreatedByAccount_ID)
        .ForeignKey("dbo.Account", t => t.ModifiedByAccount_ID)
        .ForeignKey("dbo.Account", t => t.PublishedByAccount_ID)
        .Index(t => t.CreatedByAccount_ID)
        .Index(t => t.ModifiedByAccount_ID)
        .Index(t => t.PublishedByAccount_ID);

Hope this helps to someone having the same issue.