How to solve EF-Core Code-First table foreign key column missing

entity-framework entity-framework-core

Question

I'm learning EF Core and the below is my three POCOs:

public class County
{
    [Key]
    public int cid { get; set; }
    public string cname { get; set; }
}

public class City
{
    [Key]
    public int cid { get; set; }
    public string cname { get; set; }
}

public class People
{
    [Key]
    public int pid { get; set; }
    public string pname { get; set; }

    public int cid { get; set; }
    public City WhichCity { get; set; }

}

I'm expecting two foreign keys but only got one from City table. How to make it(using annotation or fluent API or whatever) except explicitly define a County variable to People class.

1
0
3/20/2019 4:14:49 PM

Accepted Answer

Just want to clarify: you don't need to have navigation properties, i.e., public City City { get; set; } in order to setup relationships. The only things you need are the foreign key and proper configurations.

I think the following configuration would work for you (not tested though):

Entities

Here I also purposely modified your existing classes to follow C# Naming Conventions, if you care. Remember, if you're doing Code First, that means you can have your classes however you want first. You think about persistence later on. Actually I will show you how you can rename classes' properties when you persist them to your database via Configurations.

public class County
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class City
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class People
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int CityId { get; set; }
    // Optional
    //public City City { get; set; }

    public int CountyId { get; set; }
    // Optional
    //public County County { get; set; }
}

Configurations

Instead of using Data Annotation, you can use Fluent API with configurations to configure how you want to map your classes back to database.

public class CountyConfiguration : IEntityTypeConfiguration<County>
{
    public void Configure(EntityTypeBuilder<County> builder)
    {
        builder.HasKey(x => x.Id);        // Same as using [Key]

        builder.Property(x => x.Id)
            .HasColumnName("cid");        // If you want to rename to "cid"

        builder.Property(x => x.Name)
            .IsRequired()                 // If you want to mark that field required
            .HasColumnName("cname");      // If you want to rename to "cname"

        builder.ToTable("so_county");     // If you want to rename the table
    }
}

public class CityConfiguration : IEntityTypeConfiguration<City>
{
    public void Configure(EntityTypeBuilder<City> builder)
    {
        builder.HasKey(x => x.Id);        // Same as using [Key]

        builder.Property(x => x.Id)
            .HasColumnName("cid");        // If you want to rename to "cid"

        builder.Property(x => x.Name)
            .IsRequired()                 // If you want to mark that field required
            .HasColumnName("cname");      // If you want to rename to "cname"

        builder.ToTable("so_city");       // If you want to rename the table
    }
}

public class PeopleConfiguration : IEntityTypeConfiguration<People>
{
    public void Configure(EntityTypeBuilder<People> builder)
    {
        builder.HasKey(x => x.Id);        // Same as using [Key]

        builder.Property(x => x.Id)
            .HasColumnName("pid");        // If you want to rename to "pid"

        builder.Property(x => x.Name)
            .IsRequired()                 // If you want to mark that field required
            .HasColumnName("pname");      // If you want to rename to "pname"

        // Relationship
        builder.HasOne<County>()          // People has one County
            .WithMany()                   // County has many people
            .HasForeignKey<County>(x => x.CountyId);  // Foreign key is CountyId

        builder.HasOne<City>()            // People has one City
            .WithMany()                   // City has many people
            .HasForeignKey<City>(x => x.CityId);      // Foreign key is CityId

        builder.ToTable("so_people");     // If you want to rename the table
    }
}

And lastly, you need to apply those configurations OnModelCreating:

public class YourDbContext : DbContext
{
    public DbSet<County> Counties { get; set; }
    public DbSet<City> Cities { get; set; }
    public DbSet<People> People { get; set; }

    public YourDbContext(DbContextOptions<YourDbContext> options) : base(options) {}

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

        builder.ApplyConfiguration(new CountyConfiguration());
        builder.ApplyConfiguration(new CityConfiguration());
        builder.ApplyConfiguration(new PeopleConfiguration());
    }
}

DISCLAIM: wrote it by hand. Not tested.

2
3/20/2019 8:34:48 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow