EntityFramework Seed AddOrUpdate with Foreign Key

Question

Trying to seed data in an ASP.NET web app utilizing Entity Framework and code first. I'm placing this code in the Seed() method of the Configuration.cs file. Right now I'm working on the address aspects of the solution and I'm trying to get all of the states and counties loaded into the system.

Here is my code for states, working just fine...

context.StateProvinces.AddOrUpdate(
    p => p.Abbreviation,
    new StateProvince { Abbreviation = "AL", Name = "Alabama" },
    new StateProvince { Abbreviation = "AK", Name = "Alaska" },
    new StateProvince { Abbreviation = "AZ", Name = "Arizona" },
    ....
);

How can I go about using the AddOrUpdate() function for context.Counties? This is what my County object looks like.

public class County
{
    public int CountyId { get; set; }
    public int StateProvinceId { get; set; }
    public String Name { get; set; }

    [ForeignKey("StateProvinceId")]
    public virtual StateProvince StateProvince { get; set; }
}

While I can simply duplicate the format for what I did with States, we know that counties names are reused throughout the country. So I can't simply use c => c.Name as the existing check. I could use the name and StateProvinceId field though?

Answer 1

Why not add the counties to the states, instead of doing it in reverse?

If you can't do that, then you can modify yours to the following:

var al = new StateProvince { Abbreviation = "AL", Name = "Alabama" },
var ak = new StateProvince { Abbreviation = "AK", Name = "Alaska" },
var ar = new StateProvince { Abbreviation = "AZ", Name = "Arizona" },

context.StateProvinces.AddOrUpdate(
    p => p.Abbreviation,
    al,
    ak,
    ar
);

context.SaveChanges();

context.Counties.Add(new County() { Name = "Something", StateProvince = al });

// DONT CALL SAVE, Initializer needs something to do or it complains

Answer 2

You'll have to tap into the OnModelCreating() method and declare a composite key that contains the name of the county and the state abbreviation. You'll also need a property on your County entity for the abbreviation.

This is a code fragment with an example:

public class DirectMailDbContext : DbContext, IDirectMailDbContext
{
    public DbSet<BulkQualificationCriteria> QualificationRanges { get; set; }
    public DbSet<CarrierRouteDefinition> RouteDefinitions { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer(new DirectMailDbContextDropInitializer());

        modelBuilder.Entity<BulkQualificationCriteria>().HasKey(q => new {q.QualificationLevel, q.MatchType});  // <- Define composite primary key here
        modelBuilder.Entity<CarrierRouteDefinition>()
                    .HasKey(c => new {c.Zip, c.RouteNumber});

        base.OnModelCreating(modelBuilder);
   }

FROM OP

public class County
{
    public int CountyId { get; set; }
    public int StateProvinceId { get; set; }
    public String Name { get; set; }
    [ScaffoldColumn(false)]
    public String StatePostalAbbreviation { get; set; }

    [ForeignKey("StateProvinceId")]
    public virtual StateProvince StateProvince { get; set; }
}

SEED

context.Counties.AddOrUpdate(
    c => new {c.StatePostalAbbreviation, c.Name},
    new County { Name = "Aleutians East", StatePostalAbbreviation = "AK" },
    new County { Name = "Aleutians West", StatePostalAbbreviation = "AK" },
    ....
);

OnModelCreating

modelBuilder.Entity<County>().HasKey(q => new { q.Name, q.StatePostalAbbreviation });