ForeignKey to the same table with custom column name

Question

I have a model User :

public class User
{
    [Key]
    public int IDUser { get; set; }
    [Required]
    public string Forename { get; set; }
    [Required]
    public string Name { get; set; }                          

    public int? IDUser_CreatedBy { get; set; }
    public User User_CreatedBy { get; set; }
}

User can have its creator (User_CreatedBy) with its ID (IDUser_CreatedBy) which resides in the same table of course but I want to have an opportunity to leave it as null value (User with unknown creator). That's why IDUser_CreatedBy has int? nullable type.

I don't know how to set up my fluent API in order to bind that foreign key IDUser_CreatedBy to primary key IDUser in the same table.

I know that if I delete IDUser_CreatedBy foreign key from that model and add new migration then EF core will make for me shadow property foreign key implicitly but I want to have an opportunity to update my User later (in MVC controller) with custom created column IDUser_CreatedBy and be able to name that column for myself. Besides I don't want that shadow property since I don't have any control over naming that column.

How can I achieve that ?

EDIT

@IvanStoev - thank you for your answer but your example doesn't produce for me a valid migration code :

protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Users_Users_CreatedByIDUser",
            table: "Users");

        migrationBuilder.RenameColumn(
            name: "CreatedByIDUser",
            table: "Users",
            newName: "UserIDUser");

        migrationBuilder.RenameIndex(
            name: "IX_Users_CreatedByIDUser",
            table: "Users",
            newName: "IX_Users_UserIDUser");

        migrationBuilder.AddColumn<int>(
            name: "IDUser_CreatedBy",
            table: "Users",
            nullable: true);

        migrationBuilder.CreateIndex(
            name: "IX_Users_IDUser_CreatedBy",
            table: "Users",
            column: "IDUser_CreatedBy");

        migrationBuilder.AddForeignKey(
            name: "FK_Users_Users_IDUser_CreatedBy",
            table: "Users",
            column: "IDUser_CreatedBy",
            principalTable: "Users",
            principalColumn: "IDUser",
            onDelete: ReferentialAction.Restrict);

        migrationBuilder.AddForeignKey(
            name: "FK_Users_Users_UserIDUser",
            table: "Users",
            column: "UserIDUser",
            principalTable: "Users",
            principalColumn: "IDUser",
            onDelete: ReferentialAction.Restrict);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Users_Users_IDUser_CreatedBy",
            table: "Users");

        migrationBuilder.DropForeignKey(
            name: "FK_Users_Users_UserIDUser",
            table: "Users");

        migrationBuilder.DropIndex(
            name: "IX_Users_IDUser_CreatedBy",
            table: "Users");

        migrationBuilder.DropColumn(
            name: "IDUser_CreatedBy",
            table: "Users");

        migrationBuilder.RenameColumn(
            name: "UserIDUser",
            table: "Users",
            newName: "CreatedByIDUser");

        migrationBuilder.RenameIndex(
            name: "IX_Users_UserIDUser",
            table: "Users",
            newName: "IX_Users_CreatedByIDUser");

        migrationBuilder.AddForeignKey(
            name: "FK_Users_Users_CreatedByIDUser",
            table: "Users",
            column: "CreatedByIDUser",
            principalTable: "Users",
            principalColumn: "IDUser",
            onDelete: ReferentialAction.Restrict);
    }

It does produces a ForeignKey column that I wanted (IDUser_CreatedBy) and a valid index for that key column (IX_Users_IDUser_CreatedBy) but it also adds another ForeignKey (UserIDUser) witch is a shadow property I guess. So as a result I have 2 different ForeignKey columns that refer to one Primerykey column (IDUser) in the same table. In my case migration in fact renames old column (CreatedByIDUser) to new one (UserIDUser) which is a remnant of my previous shadow property ForeignKey column. What I'm looking for is that migration will get rid completely of that previous shadow property and introduse only one, new ForeignKey column (IDUser_CreatedBy). Any suggestions ?

EDIT 2

@viveknuna - thank you for your example but it also doesn't do the job for me.

Migration looks like this :

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Users_Users_CreatedByIDUser",
            table: "Users");

        migrationBuilder.RenameColumn(
            name: "CreatedByIDUser",
            table: "Users",
            newName: "User_CreatedByIDUser");

        migrationBuilder.RenameIndex(
            name: "IX_Users_CreatedByIDUser",
            table: "Users",
            newName: "IX_Users_User_CreatedByIDUser");

        migrationBuilder.AddColumn<int>(
            name: "IDUser_CreatedBy",
            table: "Users",
            nullable: true);

        migrationBuilder.AddForeignKey(
            name: "FK_Users_Users_User_CreatedByIDUser",
            table: "Users",
            column: "User_CreatedByIDUser",
            principalTable: "Users",
            principalColumn: "IDUser",
            onDelete: ReferentialAction.Restrict);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Users_Users_User_CreatedByIDUser",
            table: "Users");

        migrationBuilder.DropColumn(
            name: "IDUser_CreatedBy",
            table: "Users");

        migrationBuilder.RenameColumn(
            name: "User_CreatedByIDUser",
            table: "Users",
            newName: "CreatedByIDUser");

        migrationBuilder.RenameIndex(
            name: "IX_Users_User_CreatedByIDUser",
            table: "Users",
            newName: "IX_Users_CreatedByIDUser");

        migrationBuilder.AddForeignKey(
            name: "FK_Users_Users_CreatedByIDUser",
            table: "Users",
            column: "CreatedByIDUser",
            principalTable: "Users",
            principalColumn: "IDUser",
            onDelete: ReferentialAction.Restrict);
    }

It renames my remnant shadow property ForeignKey a new name but it keeps that shadow property. Migration also adds new column IDUser_CreatedBy which is not a ForeignKey (there's no AddForeignKey and CreateIndex method for that column).

EDIT 3

@Ivan Stoev - you're right. I haven't submitted whole model because I thought it will have no impact on the result but now it's clear it has ... :) First of all - I indeed created a brand new command line project and managed to scaffold the correct migration as you said. Then I realised that my model has another property which is public List<User> UsersAdded { get; set; } which depicts all users that were added by that particular User. And that is the culprit. It adds new column UserIDUser to newly created migration. So the final question is : how can I achieve all that what I need with that new property public List<User> UsersAdded { get; set; } ? because I want to have an opportunity to include User collecion that this User created while getting a User in my MVC controller. As a formality I submit my whole model (correctly this time) :

public class User
{
    [Key]
    public int IDUser { get; set; }
    [Required]
    public string Forename { get; set; }
    [Required]
    public string Name { get; set; }
    public string AvatarPath { get; set; }
    public string Email { get; set; }
    public string PhoneNumber { get; set; }
    public string Password { get; set; }
    public bool IsWebUser { get; set; }
    public DateTimeOffset CreatedAt { get; set; }

    public int? IDUser_CreatedBy { get; set; }
    public User User_CreatedBy { get; set; }

    public List<User> UsersAdded { get; set; }
}

Apparently there is something else in your code not shown here. There is no way EF migration to create column "UserIDUser" with this model and the suggested fluent configuration. Create a clean new project with just the code from your post and from my answer and you'll see that the migration will generate a correct `CreateTable`. — Ivan Stoev, Jun 17 '18 at 18:25

score 4 · Answer 1 · answered Jun 14 '18 at 15:06

EF Core fluent API gives you a full control over naming the table columns, regardless of whether you use explicit or shadow model property. The unconventional foreign key property is mapped with HasForeignKey fluent API of the relationship configuration.

Your entity model falls into Single Navigation Property with explicit FK property category, which can be mapped like this:

modelBuilder.Entity<User>()
    .HasOne(e => e.User_CreatedBy) // reference navigation property
    .WithMany() // no collection navigation property
    .HasForeignKey(e => e.IDUser_CreatedBy); // foreign key property

ForeignKey to the same table with custom column name

1 Answers1