Entity Framework Core assign foreign key constraint in OnModelCreating?

Question

I'm trying to figure out how to programmatically generate my database using Entity Framework Core and I'm running into issues assigning a foreign key to a field in a table. I need the field Address to reference the Address object and load it when I go to retrieve a record from the database. If I create the database using EnsureCreated, it creates the tables correctly except that the field AddressId is not a foreign key to the Address table. I have tried doing my research on this, and ran into this article, which uses a method called HasForeignKey, however whatever is returning from Entity() doesn't know about HasForeignKey. I get this error if I try manually typing it in I get:

Error CS1061 'EntityTypeBuilder' does not contain a definition for 'HasForeignKey' and no extension method 'HasForeignKey' accepting a first argument of type 'EntityTypeBuilder' could be found (are you missing a using directive or an assembly reference?)

So obviously that approach isn't valid. I tried following this documentation, but I don't really understand what it is doing and how to apply it to my situation. In their case they have a couple tables, blog and posts, where blog has many posts and post has a blog. I tried following along the best I could, but I don't really understand all the jargon and what it is accomplishing.

How can I go about simply assigning the value found in AddressId as a foreign key to a record in Addresses? This is the code I am using.

public class Person
    {
        [Key]
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public byte Age { get; set; }
        public int AddressId { get; set; }
        public Address Address { get; set; }
    }

    public class Address
    {
        [Key]
        public int Id { get; set; }
        public string Street { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string ZipCode { get; set; }
    }

    public class MyContext: DbContext
    {
        public DbSet<Person> People { get; set; }
        public DbSet<Address> Addresses { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite($"Data Source=test.db");
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder.Entity<Address>().HasKey(v => v.Id);
            builder.Entity<Person>().HasKey(v => v.Id);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new MyContext())
            {
                //var person = db.People.Find(1);

                db.Database.EnsureCreated();

                var person = new Person()
                {
                    FirstName = "Jack",
                    LastName = "Jackson",
                    Age = 50,
                    Address = new Address()
                    {
                        Street = "123 Street St",
                        City = "Jacksonville",
                        State = "Mississippi",
                        ZipCode = "00000-0000"
                    }
                };

                db.People.Add(person);

                db.SaveChanges();
            }
        }
    }

A little extra information:

• I am using .Net Core 2.0
• My database is SQLite
• I am using the Microsoft.EntityFrameworkCore.SQLite & SQLite.Core libraries for my provider.

Answer 1

Even though this question is getting old, since the documentation it pointed out helped me figure out a different problem I was going through myself, I'll post a solution for it down here.

There are two ways of solving this, one with a ForeignKeyAttribute annotation, another using the FluentAPI like you were using. You can set the Person to have one or multiple addresses, and the corresponding method changes according to that.

I also recommend making a Person property in the Address class, so you can use them the other way around as well.

The first is set in the Model class itself:

public class Person
{
    [Key]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    // The byte type is interpreted by EF as a boolean or BIT on SQL Server/Sqlite,
    // so I took the liberty of changing it to integer
    public int Age { get; set; }
    public int AddressId { get; set; }

    [ForeignKey("AddressId")]
    public virtual Address Address { get; set; }
    // Set it to be a Collection of any type for a Person to be able to have multiple Addresses
    // public ICollection<Address> Addresses { get; set; }
}

public class Address
{
    [Key]
    public int Id { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }
    public int PersonId { get; set; }    

    [ForeignKey("PersonId")]
    public virtual Person Person { get; set; }
}

The other, in case you want to tweak the constraint names, delete behaviors or other extra settings, you drop the ForeignKey attributes and do it with the Fluent API instead:

public class MyContext: DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<Address> Addresses { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite($"Data Source=test.db");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>(person =>
        {
            string tableName = "Persons";
            person.ToTable(tableName);
            person.HasKey(p => p.Id).HasConstraintName($"PK_{tableName}_Id");

            // In case you want to set your Id column to have an Identity/Autoincrement
            person.Property(p => p.Id).UseIdentityColumn();
            person.Property(p => p.FirstName).HasMaxLength(100);
            person.Property(p => p.LastName).HasMaxLength(250);

            // Replace HasOne by HasMany in case you want to make a Person
            // to be able to have multiple Addresses
            person.HasOne(p => p.Address).WithOne(a => a.Person).HasForeignKey("IdAddress").HasConstraintName($"FK_{tableName}_IdAddress").OnDelete(DeleteBehavior.Cascade);
        });

        modelBuilder.Entity<Address>(address => {
            string tableName = "Addresses";
            address.ToTable(tableName);
            address.HasKey(a => a.Id).HasConstraintName($"PK_{tableName}_Id");

            // In case you want to set your Id column to have an Identity/Autoincrement
            address.Property(a => a.Id).UseIdentityColumn();
            address.Property(a => a.Street).HasMaxLength(50);
            address.Property(a => a.City).HasMaxLength(30);
            address.Property(a => a.State).HasMaxLength(30);
            address.Property(a => a.ZipCode).HasMaxLength(10);

            // Replace WithOne by WithMany in case you want to make a Person
            // to be able to have multiple addresses
            address.HasOne(a => a.Person).WithOne(p => p.Address).OnDelete(DeleteBehavior.NoAction);
        });
    }
}

Answer 2

In your OnModelCreating method try updating the following line to look like this (I wasn't able to test this code so my formatting may be off):

builder.Entity<Person>().HasOne(v => v.Address).WithMany().HasForeignKey(v => v.AddressId);

I believe that since you have the [Key] Data annotations in both entities, you don't need to set the key up via the Fluent API. Warning - I'm a bit new to EF Core too so I could be wrong. :)

Answer 3

I know it is late but I hope someone can benefit from this answer. I experienced the same problem and it turned out I forgot to add the required nuget package. Install Microsoft.EntityFrameworkCore.Relational and you'll be good to go.