How to work with Foreign Keys in C# ASP . NET Core?

Question

Good night, you guys.

I am a beginner in C# ASP . NET Core and I am facing problems with Foreign Key. I need to popular a combobox with data from a database table.

Next is my Seller model

using System;
using System.Collections.Generic;
using System.Linq;

namespace SalesWebMvc.Models {
    public class Seller {
        public int id { get; set; }
        public string name { get; set; }
        public string email { get; set; }
        public double basesalary { get; set; }
        public DateTime birthdate { get; set; }
        public Department department { get; set; }
        public int DepartmentId { get; set; }
        public ICollection<SalesRecord> sales { get; set; } = new List<SalesRecord>();

        public Seller() {

        }

        public Seller(int id, string name, string email, double basesalary, DateTime birthdate, Department department) {
            this.id = id;
            this.name = name;
            this.email = email;
            this.basesalary = basesalary;
            this.birthdate = birthdate;
            this.department = department;
        }

        public void AddSales(SalesRecord sr) {
            sales.Add(sr);   
        }

        public void RemoveSales(SalesRecord sr) {
            sales.Remove(sr);
        }

        public double TotalSales(DateTime initial, DateTime final) {
            return sales.Where(sr => sr.date >= initial && sr.date <= final).Sum(sr => sr.amount);
        }
    }
}

Then follow my Department model:

using System.Collections.Generic;
using System;
using System.Linq;

namespace SalesWebMvc.Models {
    public class Department {
        public int id { get; set; }
        public string nome { get; set; }
        public ICollection<Seller> sellers { get; set; } = new List<Seller>();

        public Department() {

        }

        public Department(int id, string name) {
            this.id = id;
            this.nome = name;
        }

        public void AddSeller(Seller s) {
            sellers.Add(s);
        }

        public double TotalSales(DateTime initial, DateTime final) {
            return sellers.Sum(seller => seller.TotalSales(initial, final));
        }
    }
}

In view I put like this:

<div class="form-group">
<label asp-for="Seller.DepartmentId" class="control-label"></label>
<select asp-for="Seller.DepartmentId" asp-items="@(new SelectList(Model.Departments,"Id",
"nome"))" class="form-control"></select>
</div>

Gives referential integrity error!

Mysqlexception: Cannot add or update a Child Row: a Foreign key Constraint fails

I would like to know the simplest way to put all departments within a combobox in the View.

Thank you.

Answer 1

Hi, Lucas, if you are using Entityframework, I created this base class for the context for you to inherit yours from it, or make the change by overwriting the Onmodelcreating method, including conventions to avoid CASCADE DELETE, which apparently is generating your error.

public class BaseContexto : DbContext
{
    public BaseContexto()
        : base("DefaultConnection")
    {

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
    }
}

I hope it helps.