ASP.NET Core SqlException: the insert statement conflicted with the foreign key constraint

Question

I created two tables (Customer, Sale) with one to many relationship in my application (ASP.NET Core application) where one customer have many sales invoices, but when create a sale, I get error

An unhandled exception occurred while processing the request.
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sale_Customer_customerId". The conflict occurred in database "LibraryBookManagmentDataBase", table "dbo.Customer", column 'Customerid'. The statement has been terminated.

This is the Customer model class:

public class Customer
{
    [Key]
    public int Customerid { get; set; }
    [Required, MinLength(2), MaxLength(50)]
    public string Name { get; set; }
    public double NationalID { get; set; }
    public string Email { get; set; }
    public string Gender { get; set; }
    public ICollection<Sale> Sales { get; set; } 
}

This is the Sale model class:

public class Sale
{
    [Key]
    public int id { get; set; }
    [Required]
    [DisplayName("Sales Date")]
    public DateTime SalesDate { get; set; }
    [DisplayName("Sales Code")]
    public string SaleCode { get; set; }
    [ForeignKey("Customerid")]
    public int customerId { get; set; }
    public Customer Customer { get; set; }
    public ICollection<Book> Books { get; set; } 
}

This is razor page of create new sale

<form asp-controller="Sale" asp-action="Create" method="post" class="form-horizontal" role="form">
    <div class="col-md-12">
        <div class="form-inline">
            <div class="col-md-4">
                <div class="form-group">
                    <label for="Name">Customer &nbsp  </label>
                    <select class="form-control border-input" asp-items="ViewBag.Customerid" placeholder = "Customer"></select>
                </div>
            </div>
        </div>
    </div>
</form>

This is my controller

public ActionResult Create()
{
    ViewData["Customerid"] = new SelectList(customerRepository.PreffedCustomer, "Customerid", "Name");
    ViewData["Bookid"] = new SelectList(BookRepository.Preffedbooks, "Bookid", "Title");
    return View();
}

[HttpPost]
public ActionResult Create(Sale model)
{
    if (ModelState.IsValid)
    {
        saleRepository.Create(model);
        return RedirectToAction();
    }

    return Json(new { error = true, message = "failed to save Sales" });
}

The Sale is stored with this method :

public void Create(sale entity) 
{ 
    _context.Add(entity); 
    _context.SaveChanges(); 
} 

Can anyone help me please?

Answer 1

According to your code, I have created a sample, it seems that when insert new Sale entity, if the customerId is null, it will show this "The INSERT statement conflicted with the FOREIGN KEY constraint" error. You can set a break point in the Create HttpPost method to verify it.

<select class="form-control border-input" asp-items="ViewBag.Customerid" placeholder = "Customer"></select>

The issue is related that the select element, since the it doesn't contain the name attribute, when you submit the form, the customer is null ('0' is the default value, in my database, the customer is start from 1, doesn't contain '0').

To solve this issue, try to modify the select element code as below :

  <select asp-for="customerId" class ="form-control" asp-items="@ViewBag.Customerid" placeholder = "Customer"></select>

or

   <select name="customerId" class ="form-control" asp-items="@ViewBag.Customerid" placeholder = "Customer"></select>

The result (success):

Edit

Besides, about the ForeignKey attribute, we can use it as below:

public class Student
{
    public int StudentID { get; set; }
    public string StudentName { get; set; }
        
    [ForeignKey("Standard")]
    public int StandardRefId { get; set; }
    public Standard Standard { get; set; }
}

public class Standard
{
    public int StandardId { get; set; }
    public string StandardName { get; set; }
    
    public ICollection<Student> Students { get; set; }
}

In the above example, the [ForeignKey] attribute is applied on the StandardRefId and specified in the name of the navigation property Standard. This will create the foreign key column named StandardRefId in the Students table, preventing the generation of a StandardId column in the database.

public class Student
{
    public int StudentID { get; set; }
    public string StudentName { get; set; }
        
    public int StandardRefId { get; set; }
    
    [ForeignKey("StandardRefId")]
    public Standard Standard { get; set; }
}

public class Standard
{
    public int StandardId { get; set; }
    public string StandardName { get; set; }
    
    public ICollection<Student> Students { get; set; }
}

In the above example, the [ForeignKey] attribute is applied on the Standard navigation property and the name of the foreign key property StandardRefId is specified. This will create the foreign key column named StandardRefId in the Students table, preventing the generation of a StandardId column in the database.

More detail information, see: Data Annotations - ForeignKey Attribute in EF 6 & EF Core