EF Code First foreign key without navigation property

Question

Let's say I have the following entities:

public class Parent
{
    public int Id { get; set; }
}
public class Child
{
    public int Id { get; set; }
    public int ParentId { get; set; }
}

What is the code first fluent API syntax to enforce that ParentId is created in the database with a foreign key constraint to the Parents table, without the need to have a navigation property?

I know that if I add a navigation property Parent to Child, then I can do this:

modelBuilder.Entity<Child>()
    .HasRequired<Parent>(c => c.Parent)
    .WithMany()
    .HasForeignKey(c => c.ParentId);

But I don't want the navigation property in this particular case.

Answer 1

With EF Code First Fluent API it is impossible. You always need at least one navigation property to create a foreign key constraint in the database.

If you are using Code First Migrations you have the option to add a new code based migration on the package manager console (add-migration SomeNewSchemaName). If you changed something with your model or mapping a new migration will be added. If you didn't change anything force a new migration by using add-migration -IgnoreChanges SomeNewSchemaName. The migration will only contain empty Up and Down methods in this case.

Then you can modify the Up method by adding the follwing to it:

public override void Up()
{
    // other stuff...

    AddForeignKey("ChildTableName", "ParentId", "ParentTableName", "Id",
        cascadeDelete: true); // or false
    CreateIndex("ChildTableName", "ParentId"); // if you want an index
}

Running this migration (update-database on package manage console) will run a SQL statement similar to this (for SQL Server):

ALTER TABLE [ChildTableName] ADD CONSTRAINT [FK_SomeName]
FOREIGN KEY ([ParentId]) REFERENCES [ParentTableName] ([Id])

CREATE INDEX [IX_SomeName] ON [ChildTableName] ([ParentId])

Alternatively, without migrations, you could just run a pure SQL command using

context.Database.ExecuteSqlCommand(sql);

where context is an instance of your derived context class and sql is just the above SQL command as string.

Be aware that with all this EF has no clue that ParentId is a foreign key that describes a relationship. EF will consider it only as an ordinary scalar property. Somehow all the above is only a more complicated and slower way compared to just opening a SQL management tool and to add the constraint by hand.