INSERT statement conflicted with the FOREGIGN KEY constraint

Question

I am a green asp.net developer and I am using the latest entity framework with my project. I am having an issue seeding my database with autogenerated values(I think). Here is the exact error.

Here's the code:

public class Address
{
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int AddressId { get; set; }
// some more properties
}

public class ApplicationUser : IdentityUser
{
        [ForeignKey("Address")]        
    public int AddressId { get; set; }
    public Address Address { get; set; }

// some more properties
}

public class Shelter
{
        [Required]
        [ForeignKey("Address")]
        public int AddressId { get; set; }
        public Address Address { get; set; }
// some more properties
}
//seed
private void CreateShelters()
{
    EntityEntry<Address> MspcaAddress = DbContext.Addresses.Add(new Address()
    {
                Street = "350 S Huntington Ave",
                City = "Jamaica Plain",
                State = "MA",
                AreaCode = "02130",
                Latitude = 42.3228928,
                Longititude = -71.11120540000002
    });
     EntityEntry<Address> BostonAnimalCareAndControlAddress = DbContext.Addresses.Add(new Address()
     {
                Street = "26 Mahler Rd",
                City = "Roslindale",
                State = "MA",
                AreaCode = "02131",
                Latitude = 42.2943377,
                Longititude = -71.12153390000003
            });
            EntityEntry<Address> AnimalRescueLeagueOfBostonAddress = DbContext.Addresses.Add(new Address()
            {
                Street = "10 Chandler St",
                City = "Boston",
                State = "MA",
                AreaCode = "0S2116",
                Latitude = 42.3470486,
                Longititude = -71.06976929999996
            });

            EntityEntry<Shelter> Mspca = DbContext.Shelters.Add(new Shelter()
            {
                Name = "MCSPA",
                AddressId = MspcaAddress.Entity.AddressId
            });
            EntityEntry<Shelter> BostonAnimalCareAndControl = DbContext.Shelters.Add(new Shelter()
            {
                Name = "Boston Animal Care And Control",
                AddressId = BostonAnimalCareAndControlAddress.Entity.AddressId
            });
            EntityEntry<Shelter> AnimalRescueLeagueOfBoston = DbContext.Shelters.Add(new Shelter()
            {
                Name = "Animal Rescue League Of Boston Address",
                AddressId = AnimalRescueLeagueOfBostonAddress.Entity.AddressId
            });
            DbContext.SaveChanges();
   }

I tried recreating the database to see if was an issue with inconsistent data/tables but I am still getting the error. Any ideas?

Answer 1

Your "Shelter" entities, which you are trying to seed, require that you submit an existing AddressID Your code above tries to Get The AddressId of an address that has not been created yet.

You must first Commit your addresses to the database (using DbContext.SaveChanges();)

Then, you can create the Shelter entities with the retrieved AddressId. The code below shows a sample of how you can do this, but you may have to re-write the queries that fetch the addressId. Or, you may have to separate them from the insert process altogether.

    private void CreateShelters()
{
    EntityEntry<Address> MspcaAddress = DbContext.Addresses.Add(new Address()
    {
                Street = "350 S Huntington Ave",
                City = "Jamaica Plain",
                State = "MA",
                AreaCode = "02130",
                Latitude = 42.3228928,
                Longititude = -71.11120540000002
    });
     EntityEntry<Address> BostonAnimalCareAndControlAddress = DbContext.Addresses.Add(new Address()
     {
                Street = "26 Mahler Rd",
                City = "Roslindale",
                State = "MA",
                AreaCode = "02131",
                Latitude = 42.2943377,
                Longititude = -71.12153390000003
            });
            EntityEntry<Address> AnimalRescueLeagueOfBostonAddress = DbContext.Addresses.Add(new Address()
            {
                Street = "10 Chandler St",
                City = "Boston",
                State = "MA",
                AreaCode = "0S2116",
                Latitude = 42.3470486,
                Longititude = -71.06976929999996
            });

    DbContext.SaveChanges();


            EntityEntry<Shelter> Mspca = DbContext.Shelters.Add(new Shelter()
            {
                Name = "MCSPA",
                AddressId = MspcaAddress.Entity.AddressId
            });
            EntityEntry<Shelter> BostonAnimalCareAndControl = DbContext.Shelters.Add(new Shelter()
            {
                Name = "Boston Animal Care And Control",
                AddressId = DbContext.Addresses.Where(x => x.Street == "26 Mahler Rd").FirstOrDefault().AddressId
       });
            EntityEntry<Shelter> AnimalRescueLeagueOfBoston = DbContext.Shelters.Add(new Shelter()
            {
                Name = "Animal Rescue League Of Boston Address",
                AddressId = DbContext.Addresses.Where(x => x.Street == "10 Chandler St").FirstOrDefault().AddressId

            });
            DbContext.SaveChanges();
   }

Hope this is enough for you to go by. If the queries for the addressId don't work let me know and I will be happy to help you out with those.

Answer 2

The key :) to this issue is to use reference properties instead of Id values. This means: set Address instead of AddressId. When EF saves the changes, it will figure out the dependencies between the entities and save them all in the right order, while setting the generated AddressId key values just in time as foreign key values.

The essence of you code (i.e. leaving out the property assignments, for brevity) should look like this:

var mspcaAddress = new Address();
var bostonAnimalCareAndControlAddress = new Address();
var animalRescueLeagueOfBostonAddress = new Address();
context.Addresses.AddRange(new[]
{
    mspcaAddress,
    bostonAnimalCareAndControlAddress,
    animalRescueLeagueOfBostonAddress
});

var mspca = new Shelter()
{
    Address = mspcaAddress
};
var bostonAnimalCareAndControl = new Shelter()
{
    Address = bostonAnimalCareAndControlAddress
};
var animalRescueLeagueOfBoston = new Shelter()
{
    Address = animalRescueLeagueOfBostonAddress
};
context.Shelters.AddRange(new[]
{
    mspca,
    bostonAnimalCareAndControl,
    animalRescueLeagueOfBoston
});
context.SaveChanges();

Although the error you show applies to an AddressId in ApplicationUser, I think a similar approach in creating ApplicationUsers will solve the issue.

By the way, you can do without the first context.Addresses.AddRange statement. The addresses will be added by adding the shelters.

Note that I also removed the EntityEntrys from the code. I think they needlessly complicate the code. You only need the simple objects you're creating.