My Shop and Product entities have a one to many relationship, please see my models
public class Product
{
public int ID { get; set; }
public string Name { get; set; }
public string Tag { get; set; }
public string Brand { get; set; }
public string Place { get; set; }
public int ShopID { get; set; }
public Shop Shop { get; set; }
}
public class Shop
{
public int ID { get; set; }
public string Name { get; set; }
public string Comment { get; set; }
public string Number { get; set; }
public ICollection<Product> Products { get; set; }
}
public class ShopContext : DbContext
{
public ShopContext(DbContextOptions<ShopContext> options) : base(options)
{
}
public DbSet<Shop> Shops { get; set; }
public DbSet<Product> Products { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Shop>().ToTable("shop");
modelBuilder.Entity<Product>().ToTable("product");
}
}
public class CreateModel : PageModel
{
private readonly ShopContext _context;
public CreateModel(ShopContext context)
{
_context = context;
}
public IActionResult OnGet()
{
ViewData["Shop"] = new SelectList(_context.Shops, "ID", "Name");
return Page();
}
[BindProperty]
public Product Product { get; set; }
public async Task<IActionResult> OnPostAsync()
{
if (!ModelState.IsValid)
{
return Page();
}
_context.Products.Add(Product);
await _context.SaveChangesAsync();
return RedirectToPage("./Index");
}
}
The shop in create page is a dropdown list, when I post the new product to the handler, I can see the product.ShopID is an ID which exist in Shop table, and the product.Shop.ID as same as the product.ShopID. But when I call saveChangesAsync(), it throws an exception:
MySqlException: Cannot add or update a child row: a foreign key constraint fails (shopdb
.product
, CONSTRAINT FK_product_shop_ShopID
FOREIGN KEY (ShopID
) REFERENCES shop
(ID
) ON DELETE CASCADE)
I tried to set product.Shop to null before call saveChangesAsync method, but still doesn't work, anyone can help?
ASP.NET Core 2.0 Razor Pages Application, Nuget: Pomelo.EntityFrameworkCore.MySql v2.0.1
Declare ShopID
as nullable in the Product
table, you are doing product.Shop = null;
, so it tries to insert ShopID
value null
in this column. Since you column ShopID
in Product
table is not nullable, that is why its giving error. Because there is no Id
in Shop
table which is nullable. So its giving foreign key constraint fails exception. So all you have to do is to make use the nullable foreign key like public int? ShopID
.
You shouldn't make ShopID
nullable if it's required by design.
The problem you are experiencing is because Add
method also recursively marks all entity instances reachable through navigation properties and not currently tracked by the context as Added
(i.e. new).
It can be solved in many ways:
Setting entity entry to Added
instead of Add
method:
_context.Entry(Product).State = EntityState.Added;
await _context.SaveChangesAsync();
Setting the navigation property to null
before calling Add
:
Product.Shop = null;
_context.Products.Add(Product);
await _context.SaveChangesAsync();
Attaching the navigation property object before calling Add
:
if (Product.Shop != null) _context.Attach(Product.Shop);
_context.Products.Add(Product);
await _context.SaveChangesAsync();
Using Update
instead of Add
:
_context.Products.Update(Product);
await _context.SaveChangesAsync();
The last technique is explained in Saving Data - Disconnected Entities - Mix of new and existing entities:
With auto-generated keys, Update can again be used for both inserts and updates, even if the graph contains a mix of entities that require inserting and those that require updating
Since it works only when all entities use auto-generated PKs, and also produces unnecessary updates of the related entities, I don't recommend it.