Greetings I am new in ASP.NET core and I have problem creating database with relationships Customers could be many addresses(one to many relationship). Addresses only one country of origin(one to one relationship)
I tried make one to many relationship without one to one relationship and it works same for opposite. I just do not get it where is the problem with FK key in my db context if I am trying make them both. Error -
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Address_Customer_CustomerId". The conflict occurred in database "AdressesContext-da27b4d1-a732-4121-bb76-e3bd5633718f", table "dbo.Customer", column 'Id'.
My entities
namespace Adresses.Models
{
public enum Gender
{
Female,Male
}
public class Customer
{
public int Id { get; set; }
public string FullName { get; set; }
public string Email { get; set; }
public DateTime Birthdate { get; set; }
public Gender Gender { get; set; }
public ICollection<Address> Addresses { get; set; }
}
}
Country model
namespace Adresses.Models
{
public class Country
{
public int Id { get; set; }
public string Name { get; set; }
}
}
namespace Adresses.Models
{
public enum Type
{
Billing,Delivery
}
public class Address
{
public int Id { get; set; }
public int CustomerId { get; set; }
public int CountryId { get; set; }
public string StreetAddress { get; set; }
public string City { get; set; }
public string Zip { get; set; }
public Type Type { get; set; }
public Customer Customer { get; set; }
public Country Country { get; set; }
}
}
Seeding database I am using this method
using Adresses.Models;
using System;
using System.Linq;
namespace Adresses.Data
{
public class DbInitializer
{
public static void Initialize(AdressesContext context)
{
context.Database.EnsureCreated();
// Look for any students.
if (context.Customer.Any())
{
return; // DB has been seeded
}
var customers = new Customer[]
{
new Customer{FullName="Carson Alexander",Birthdate=DateTime.Parse("2005-09-01"),Gender=Gender.Female,Email="[email protected]"},
new Customer{FullName="Meredith Alonso",Birthdate=DateTime.Parse("2002-09-01"),Gender=Gender.Male,Email="[email protected]"}
//new Customer{FullName="Arturo Anand",Birthdate=DateTime.Parse("2003-09-01"),Gender=Gender.Female,Email="[email protected]"},
//new Customer{FullName="Gytis Barzdukas",Birthdate=DateTime.Parse("2002-09-01"),Gender=Gender.Male,Email="[email protected]"},
//new Customer{FullName="Yan Li",Birthdate=DateTime.Parse("2002-09-01"),Gender=Gender.Female,Email="[email protected]"},
//new Customer{FullName="Peggy Justice",Birthdate=DateTime.Parse("2001-09-01"),Gender=Gender.Male,Email="[email protected]"},
//new Customer{FullName="Laura Norman",Birthdate=DateTime.Parse("2003-09-01"),Gender=Gender.Female,Email="[email protected]"},
//new Customer{FullName="Nino Olivetto",Birthdate=DateTime.Parse("2005-09-01"),Gender=Gender.Female,Email="[email protected]"}
};
foreach (Customer s in customers)
{
context.Customer.Add(s);
}
context.SaveChanges();
var countries = new Country[]
{
new Country{Id=1,Name="Heavens"},
new Country{Id=2,Name="Hell"}
};
foreach (Country e in countries)
{
context.Country.Add(e);
}
context.SaveChanges();
var addresses = new Address[]
{
new Address{CustomerId=1,StreetAddress="test",City="One City",Zip="2402",Type=Models.Type.Delivery,CountryId=1},
new Address{CustomerId=2,StreetAddress="another",City="City",Zip="2403",Type=Models.Type.Billing,CountryId=2},
new Address{CustomerId=2,StreetAddress="another",City="City",Zip="2403",Type=Models.Type.Delivery,CountryId=1}
};
foreach (Address e in addresses)
{
context.Address.Add(e);
}
context.SaveChanges();
}
}
}
CodePudding user response:
you have to fix the country relations
public class Country
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Address> Addresses { get; set; }
}
and since you don't know customerId it is better to start adding from country and address. This way db server will automatically assign primary and foreign keys.
var addresses = new Address[]
{
new Address{StreetAddress="test",City="One City",Zip="2402",Type=Models.Type.Delivery,
Country = new Country{ Name="Heavens"},
Customer = new Customer {FullName="Carson Alexander",Birthdate=DateTime.Parse("2005-09-01"),Gender=Gender.Female,Email="[email protected]"}
},
new Address{StreetAddress="another",City="City",Zip="2403",Type=Models.Type.Delivery,
Country= new Country { Name="Hell"},
Customer= new Customer { FullName="Meredith Alonso",Birthdate=DateTime.Parse("2002-09-01"),Gender=Gender.Male,Email="[email protected]"}
}
};
context.Address.AddRange(addresses);
context.SaveChanges();
CodePudding user response:
The customers you're inserting aren't being given the IDs 1
and 2
, so your addresses have the wrong CustomerId
values.
It's not entirely clear why that's happening - have you previously inserted and deleted records from the customers table?
It's generally easier to insert the addresses as part of the customer:
var countries = new Country[]
{
new Country{ Name="Heavens" },
new Country{ Name="Hell" }
};
foreach (Country e in countries)
{
context.Country.Add(e);
}
var customers = new Customer[]
{
new Customer
{
FullName = "Carson Alexander",
Birthdate = new DateTime(2005, 9, 1),
Gender = Gender.Female,
Email = "[email protected]",
Addresses = new[]
{
new Address
{
StreetAddress = "test",
City = "One City",
Zip = "2402",
Type = Models.Type.Delivery,
Country = countries[0]
}
}
},
new Customer
{
FullName = "Meredith Alonso",
Birthdate = new DateTime(2002, 9, 1),
Gender = Gender.Male,
Email = "[email protected]",
Addresses = new[]
{
new Address
{
StreetAddress = "another",
City = "City",
Zip = "2403",
Type = Models.Type.Billing,
Country = countries[1]
},
new Address
{
StreetAddress = "another",
City = "City",
Zip = "2403",
Type = Models.Type.Delivery,
Country = countries[0]
}
}
}
};
foreach (Customer s in customers)
{
context.Customer.Add(s);
}
context.SaveChanges();