how to bug 'FOREIGN KEY constraint failed'.

Question

Hi,

Iam getting exception error while saving enitities and am working with one to many relation ship

using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Extensions.Configuration;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
using static System.Net.Mime.MediaTypeNames;
using System.Diagnostics.Metrics;
using System.Diagnostics;
using System.Drawing;
using System.Reflection.Emit;
using System.Reflection;
using System.Runtime.Intrinsics.X86;
using System.Threading;
using System;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.VisualBasic;
using System.Xml.Linq;
using EMPLOYEE_ROSTER.Models;
using EMPLOYEE_ROSTER.ViewModels;
using EMPLOYEE_ROSTER.Data;

namespace EmployeeRoster.Pages.Employees
{
    public class CreateModel : PageModel
    {
        private readonly EMPLOYEE_ROSTER.Data.RosterContext _context;

        public CreateModel(EMPLOYEE_ROSTER.Data.RosterContext context)
        {
            _context = context;
        }

        [BindProperty]
        public EmployeeVM Employees { get; set; }

        [BindProperty]
        public List<SelectListItem> Departments { get; set; } = new List<SelectListItem>();

        public async Task<IActionResult> OnGetAsync()
        {
            Departments = await _context.Departments
                .Select(d => new SelectListItem { Value = d.DepartmentId.ToString(), Text = d.DepartmentName })
                .ToListAsync();

            return Page();
        }
        public async Task<IActionResult> OnPostAsync()
        {
            if (!ModelState.IsValid)
            {
                return Page();
            }
            var employee = new Employee
            {
                EmployeeCode = Employees.EmployeeCode,
                Name = Employees.Name,
                Gender = Employees.Gender,
                Email = Employees.Email,
                Country = Employees.Country,
                DepartmentId = Employees.DepartmentId

            };

            _context.Employees.Add(employee);
            await _context.SaveChangesAsync();

            return RedirectToPage("/Employees/Index");

        }
    }
}



    //public IActionResult OnGet()
    //{
    //    Departments = new SelectList(_context.Departments, "DepartmentId", "DepartmentName").ToList();
    //    //var Departments = new SelectList(_context.Departments, "DepartmentId", "DepartmentName").ToList();
    //    return Page();
    //}

An unhandled exception occurred while processing the request.

SqliteException: SQLite Error 19: 'FOREIGN KEY constraint failed'.

Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int rc, sqlite3 db)

DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

Answer 1

Hi @Nandini S

    public class Department
    {      
        public int Id { get; set; }
        public int DepartmentId { get; set;}
        public string DepartmentName { get; set; }
        public List<Employee> Employees { get; set; }
    }

The issue relates the Department class, we can see it contains the Id and DepartmentId property. The Id property is the primary key, so in the Employee table, the Employee's DepartmentId should be the Id property in the Department table.

In the Create page OnGetAsync method, you will populate the Department DropdownList using the DepartmentId property, so in the Post method, when you insert the new employee, the new employee's DepartmentId is not the Id property in the Department table. Then, it will show the "FOREIGN KEY constraint failed" error.

To solve this issue, you can try to use the following methods:

Method 1: In the OnGetAsync method, select the Id property, instead of the DepartmentId property, like this:

        [BindProperty]
        public List<SelectListItem> Departments { get; set; } = new List<SelectListItem>();
        public async Task<IActionResult> OnGetAsync()
        {
            Departments = await _context.Departments
                .Select(d => new SelectListItem { Value = d.Id.ToString(), Text = d.DepartmentName })
                .ToListAsync();

            return Page();
        }

Method 2: Before creating the Employee instance, find the department based on the DepartmentId, then, when create the employ instance, set the DepartmentId value using the department.Id

        public async Task<IActionResult> OnPostAsync()
        {
            if (!ModelState.IsValid)
            {
                return Page();
            }

            var department = _context.Departments.Where(c => c.DepartmentId == Employees.DepartmentId).FirstOrDefault();
            var employee = new Employee
            {
                EmployeeCode = Employees.EmployeeCode,
                Name = Employees.Name,
                Gender = Employees.Gender,
                Email = Employees.Email,
                Country = Employees.Country,
                DepartmentId = department.Id
                //Department = department    //or using this line to set the department.
            };

            _context.Employees.Add(employee);
            await _context.SaveChangesAsync();

            return RedirectToPage("/Employees/Index");

        } 

The result as below:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion

Answer 2

Hi @Nandini S

how to delete employees associated with department , means I want to add one dropdown to selct deprtment name and i need to display employee list for which deprtment is selected and i need to delete that employees

You can use a partial view to display the employee list, after the drondownlist selected value changes, you can use JQuery ajax to call the handler method to filter the employee based on the selected department id property, then return a partial view.

In the partial view, you can add a column with a hyper link (delete button), and add the employee id as the route parameter, then after clicking the Delete button, it will go the delete handler the remove the specified employee from the database.

Code as below:

Create a _employeePV.cshtml partial view in the Pages/Shared folder:

@model List<RazorWebApp.Models.Employee>
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model[0].EmployeeCode)
            </th>
            <th>
                @Html.DisplayNameFor(model => model[0].Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model[0].Gender)
            </th>
            <th>
                @Html.DisplayNameFor(model => model[0].Email)
            </th>
            <th>
                @Html.DisplayNameFor(model => model[0].Country)
            </th>
            <th>
                @Html.DisplayNameFor(model => model[0].Department.DepartmentName)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.EmployeeCode)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Name)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Gender)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Email)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Country)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Department.DepartmentName)
                </td>
                <td>
                    <a asp-page="/Employees/Index" asp-page-handler="delete" asp-route-id="@item.EmployeeCode">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Then in the Index.cshtml page: use partial tag to display the partial view, then in the dropdownlist change event, use jquery ajax to call the handler method to find employees by the department id.

@page
@model RazorWebApp.Pages.Employees.IndexModel
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

<select id="ddlDepartment" asp-items="Model.DepartmentOptions">
    <option value="-1">Select Department</option>
</select>
<div id="divcontainer">
    <partial name="../Pages/Shared/_employeePV" model="Model.Employees" />
</div>

@section Scripts{
    <script>
        $(function(){
            $("#ddlDepartment").change(function(){
                var selectedDepID = $(this).val(); 
                $.ajax({
                    type: "Get",
                    url: "/Employees/Index?handler=FindEmployeeByDep",
                    beforeSend: function (xhr) {
                        xhr.setRequestHeader("RequestVerificationToken",
                            $('input:hidden[name="__RequestVerificationToken"]').val());
                    },
                    data: { "id": selectedDepID },
                    success: function (response) {
                        //clear the container and then append the partial page in the container.
                        $("#divcontainer").html("");
                        $("#divcontainer").html(response);
                             
                    }, 
                    error: function (response) {
                        console.log(response.responseText);
                    }
                }); 
            });
        });
    </script>
}

Index.cshtml.cs page:

    public class IndexModel : PageModel
    {
        private readonly ApplicationDbContext _context;

        public IndexModel(ApplicationDbContext applicationDbContext)
        {
            _context=applicationDbContext;
        }
        public List<Employee> Employees { get; set; }

        public List<SelectListItem> DepartmentOptions { get; set; }
        public void OnGet()
        {
            DepartmentOptions = _context.Departments.Select(a =>
                             new SelectListItem
                             {
                                 Value = a.Id.ToString(),
                                 Text =  a.DepartmentName
                             }).ToList();
            Employees = _context.Employees.Include(c => c.Department).ToList();
        }
        public IActionResult OnGetFindEmployeeByDep(int id)
        {
            var emplist = new List<Employee>();
            if(id == -1)
            {   //return all employee
                emplist = _context.Employees.Include(c => c.Department).ToList(); 
            }
            else
            {
                emplist = _context.Employees.Include(c => c.Department).Where(c => c.DepartmentId == id).ToList();
            }

            return Partial("../Pages/Shared/_employeePV", emplist);
        }

        public IActionResult OnGetDelete(string id)
        {
            var item = _context.Employees.Where(c=>c.EmployeeCode == id).FirstOrDefault();
            _context.Employees.Remove(item);
            _context.SaveChanges();

            //query the latest data
            Employees = _context.Employees.Include(c => c.Department).ToList();
            DepartmentOptions = _context.Departments.Select(a =>
                       new SelectListItem
                       {
                           Value = a.Id.ToString(),
                           Text =  a.DepartmentName
                       }).ToList();
            return Page();
        }
    }

The result as below:

If you want to delete multiple employees at one time, in the partial view, you can add a column with checkbox, and modify the code as below:

_employeePV.cshtml:

                <td>
                    <input class="chk_isDelete" type="checkbox" data-employeecode="@item.EmployeeCode" />
                    @*<a asp-page="/Employees/Index" asp-page-handler="delete" asp-route-id="@item.EmployeeCode">Delete</a>*@
                </td>

Index.cshtml:

@page
@model RazorWebApp.Pages.Employees.IndexModel
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

<select id="ddlDepartment" asp-items="Model.DepartmentOptions">
    <option value="-1">Select Department</option>
</select>
<input type="button" class="btn btn-primary" id="btndelete" value="Delete" />
<div id="divcontainer">
    <partial name="../Pages/Shared/_employeePV" model="Model.Employees" />
</div>



@section Scripts{
    <script>
        $(function(){
            $("#ddlDepartment").change(function(){
                var selectedDepID = $(this).val(); 
                $.ajax({
                    type: "Get",
                    url: "/Employees/Index?handler=FindEmployeeByDep",
                    beforeSend: function (xhr) {
                        xhr.setRequestHeader("RequestVerificationToken",
                            $('input:hidden[name="__RequestVerificationToken"]').val());
                    },
                    data: { "id": selectedDepID },
                    success: function (response) {
                        //clear the container and then append the partial page in the container.
                        $("#divcontainer").html("");
                        $("#divcontainer").html(response);
                             
                    }, 
                    error: function (response) {
                        console.log(response.responseText);
                    }
                }); 
            });

            $("#btndelete").click(function(){
                var id = [];
                $("#divcontainer input[type='checkbox']:checked").each(function (index, item) {
                    id.push($(item).attr("data-employeecode")); 
                });
                $.ajax({
                    type: "Get",
                    url: "/Employees/Index?handler=delete",
                    beforeSend: function (xhr) {
                        xhr.setRequestHeader("RequestVerificationToken",
                            $('input:hidden[name="__RequestVerificationToken"]').val());
                    },
                    data: { "id": JSON.stringify(id) },
                    success: function (response) {
                        //clear the container and then append the partial page in the container.
                        //$("#divcontainer").html("");
                        //$("#divcontainer").html(response);
                        window.location.href="/Employees/Index"; //refresh the page                                             
                    },
                    error: function (response) {
                        console.log(response.responseText);
                    }
                });
            });              
        });
    </script>
}

Index.cshtml.cs

        public IActionResult OnGetDelete(string id)
        {
            var ids = System.Text.Json.JsonSerializer.Deserialize<List<string>>(id);
            var item = _context.Employees.Where(c=> ids.Contains(c.EmployeeCode)).ToList();
            _context.Employees.RemoveRange(item);
            _context.SaveChanges();

            //query the latest data
            Employees = _context.Employees.Include(c => c.Department).ToList();
            DepartmentOptions = _context.Departments.Select(a =>
                       new SelectListItem
                       {
                           Value = a.Id.ToString(),
                           Text =  a.DepartmentName
                       }).ToList();
            return Page();
        }
    }

The result as below:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion

Answer 3

employee and deprtment entites
    public class Department
    {      
        public int Id { get; set; }
        public int DepartmentId { get; set;}
        public string DepartmentName { get; set; }
        public List<Employee> Employees { get; set; }
    }

public string EmployeeCode{ get; set; }         
public string Name { get; set; }         
public string Gender { get; set; }                   
public string Email { get; set; }         
public string Country { get; set; }         
public int DepartmentId { get; set; }          
public Department Department { get; set; }   

context class

using System;
using System.Collections.Generic;
using EMPLOYEE_ROSTER.Models;
using Microsoft.EntityFrameworkCore;

namespace EMPLOYEE_ROSTER.Data;

public partial class RosterContext : DbContext
{
    public RosterContext()
    {
    }

    public RosterContext(DbContextOptions<RosterContext> options)
        : base(options)
    {
    }
    public DbSet<Employee> Employees { get; set; }
    public DbSet<Department> Departments { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
        => optionsBuilder.UseSqlite("Data Source=.\\Data\\roster.db");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Department>()
            .HasKey(d => d.Id); // Define Department's primary key

        modelBuilder.Entity<Employee>()
            .HasKey(e => e.EmployeeCode); // Define Employee's primary key

        modelBuilder.Entity<Employee>()
            .HasOne(e => e.Department)
            .WithMany(d => d.Employees)
            .HasForeignKey(e => e.DepartmentId)
            .OnDelete(DeleteBehavior.Cascade);

        OnModelCreatingPartial(modelBuilder); // Call the partial method for additional configurations
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

here i have taken id and deprtmentId in deprtment entity ,one is databasegenereated another deprtmentid is not . am not gettinnng where the mistake was done?

Answer 4

after this how to delete employees associated with department , means I want to add one dropdown to selct deprtment name and i need to display employee list for which deprtment is selected and i need to delete that employees

Answer 5

public string EmployeeCode{ get; set; }

public string Name { get; set; }

public string Gender { get; set; }

public string Email { get; set; }

public string Country { get; set; }

public int DepartmentId { get; set; }

public Department Department { get; set; } what about this departmentid how to access public deprtment department{get;set;} using this department id .

If i select one deprtment in delete page it should display employees for that deprtment and if i deleete deprtment all employees list associaated with that deprtment should be deleted