C#•2y ago

✅ Inserting record with one-to-many relationship EF Core

Hi, I'm trying to insert a record that has one-to-many relationship with other entities which leads to Foregin Key Constraint failed. My current types look like these

using System.ComponentModel.DataAnnotations;

namespace Shared.Models;

public static class TipeCustomer
{
    public const string Personal = "Personal";
    public const string Perusahaan = "Perusahaan";
}

public class Customer
{
    public int Id { get; set; }
    [Display(Name = "Tipe")]
    public string TipeCustomer { get; set; } = Models.TipeCustomer.Perusahaan;
    [Display(Name = "Nama")]
    public string Nama { get; set; } = null!;
    [Display(Name = "Email")]
    public string? Email { get; set; }
    [Display(Name = "Nomor Telepon")]
    public string? NomorTelepon { get; set; }
    [Display(Name = "Kota")]
    public string? Kota { get; set; }
    [Display(Name = "Keterangan")]
    public string? Keterangan { get; set; }
}

using System.ComponentModel.DataAnnotations;

namespace Shared.Models;

public static class TipeCustomer
{
    public const string Personal = "Personal";
    public const string Perusahaan = "Perusahaan";
}

public class Customer
{
    public int Id { get; set; }
    [Display(Name = "Tipe")]
    public string TipeCustomer { get; set; } = Models.TipeCustomer.Perusahaan;
    [Display(Name = "Nama")]
    public string Nama { get; set; } = null!;
    [Display(Name = "Email")]
    public string? Email { get; set; }
    [Display(Name = "Nomor Telepon")]
    public string? NomorTelepon { get; set; }
    [Display(Name = "Kota")]
    public string? Kota { get; set; }
    [Display(Name = "Keterangan")]
    public string? Keterangan { get; set; }
}

16 Replies

kunio_kunOP•2y ago

using System.ComponentModel.DataAnnotations;

namespace Shared.Models;

public class Invoice
{
    public int Id { get; set; }
    [Display(Name = "Nomor Invoice")]
    public string NomorInvoice { get; set; }
    public DateTime TanggalInvoice { get; set; }
    public string UsernamePembuka { get; set; }
    public Akun AkunPembuka { get; set; }
    public List<Pekerjaan> DaftarPekerjaan { get; set; }
    [Display(Name = "Status Invoice")]
    public StatusInvoice StatusInvoice { get; set; }
}

using System.ComponentModel.DataAnnotations;

namespace Shared.Models;

public class Invoice
{
    public int Id { get; set; }
    [Display(Name = "Nomor Invoice")]
    public string NomorInvoice { get; set; }
    public DateTime TanggalInvoice { get; set; }
    public string UsernamePembuka { get; set; }
    public Akun AkunPembuka { get; set; }
    public List<Pekerjaan> DaftarPekerjaan { get; set; }
    [Display(Name = "Status Invoice")]
    public StatusInvoice StatusInvoice { get; set; }
}

And my DbContext looks like this

using Microsoft.EntityFrameworkCore;
using Shared.Models;

namespace InvoiceManagement.Database;

public class InvoiceDbContext : DbContext
{
    private readonly string _dbPath = Path.Join(AppDomain.CurrentDomain.BaseDirectory, "invoice-data.sqlite3");

    public DbSet<Akun> Akun { get; set; }
    public DbSet<Invoice> Invoice { get; set; }
    public DbSet<Customer> Customer { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite($"Data Source={_dbPath}");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Akun>().OwnsOne<Pengguna>(akun => akun.Pengguna);
        modelBuilder.Entity<Invoice>().HasMany(invoice => invoice.DaftarPekerjaan);
        modelBuilder.Entity<Invoice>().HasIndex(invoice => invoice.NomorInvoice).IsUnique();
        modelBuilder.Entity<Akun>().HasIndex(akun => akun.Username).IsUnique();
        modelBuilder.Entity<Pekerjaan>().HasKey(p => p.Id);
        base.OnModelCreating(modelBuilder);
    }
}

using Microsoft.EntityFrameworkCore;
using Shared.Models;

namespace InvoiceManagement.Database;

public class InvoiceDbContext : DbContext
{
    private readonly string _dbPath = Path.Join(AppDomain.CurrentDomain.BaseDirectory, "invoice-data.sqlite3");

    public DbSet<Akun> Akun { get; set; }
    public DbSet<Invoice> Invoice { get; set; }
    public DbSet<Customer> Customer { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite($"Data Source={_dbPath}");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Akun>().OwnsOne<Pengguna>(akun => akun.Pengguna);
        modelBuilder.Entity<Invoice>().HasMany(invoice => invoice.DaftarPekerjaan);
        modelBuilder.Entity<Invoice>().HasIndex(invoice => invoice.NomorInvoice).IsUnique();
        modelBuilder.Entity<Akun>().HasIndex(akun => akun.Username).IsUnique();
        modelBuilder.Entity<Pekerjaan>().HasKey(p => p.Id);
        base.OnModelCreating(modelBuilder);
    }
}

I'm trying to add a record to the database with the following statements

List<Pekerjaan> daftarPekerjaan = viewModel.DaftarPekerjaan.Select(p => new Pekerjaan()
            {
                Keterangan = p.Keterangan,
                Nominal = p.Nominal
            })
            .ToList();

        Invoice invoiceBaru = new Invoice()
        {
            NomorInvoice = nomorInvoice,
            DaftarPekerjaan = daftarPekerjaan,
            UsernamePembuka = username,
            StatusInvoice = StatusInvoice.BelumDikerjakan,
            TanggalInvoice = DateTime.Now
        };

        await Db.Invoice.AddAsync(invoiceBaru);
        await Db.SaveChangesAsync();

List<Pekerjaan> daftarPekerjaan = viewModel.DaftarPekerjaan.Select(p => new Pekerjaan()
            {
                Keterangan = p.Keterangan,
                Nominal = p.Nominal
            })
            .ToList();

        Invoice invoiceBaru = new Invoice()
        {
            NomorInvoice = nomorInvoice,
            DaftarPekerjaan = daftarPekerjaan,
            UsernamePembuka = username,
            StatusInvoice = StatusInvoice.BelumDikerjakan,
            TanggalInvoice = DateTime.Now
        };

        await Db.Invoice.AddAsync(invoiceBaru);
        await Db.SaveChangesAsync();

Any clue why the exception happens? Thanks in advance

Anton•2y ago

well it's because you're taking them from the db, so they probably already have a foreign key pointing at something else also, don't use AddAsync (read its doc comment)

kunio_kunOP•2y ago

well it's because you're taking them from the db

Which part? I am creating new documents there

Anton•2y ago

viewModel.DaftarPekerjaan.AsNoTracking().Select(p => new Pekerjaan()

viewModel.DaftarPekerjaan.AsNoTracking().Select(p => new Pekerjaan()

kunio_kunOP•2y ago

I'm sorry i dont understand.. viewModel is an object retrieved from form

[HttpPost]
    public async Task<IActionResult> InvoiceBaru([FromForm] InvoiceBaruViewModel viewModel)

[HttpPost]
    public async Task<IActionResult> InvoiceBaru([FromForm] InvoiceBaruViewModel viewModel)

For the full controller action

    [HttpPost]
    public async Task<IActionResult> InvoiceBaru([FromForm] InvoiceBaruViewModel viewModel)
    {
        ValidationResult validationResult = await InvoiceBaruViewModelValidator.ValidateAsync(viewModel);
        if (!validationResult.IsValid)
        {
            validationResult.AddToModelState(ModelState);
            return View(viewModel);
        }

        string? username = User.Claims.FirstOrDefault(c => c.Type == ClaimTypes.NameIdentifier)?.Value;
        if (username is null)
        {
            await HttpContext.SignOutAsync();
            return RedirectToAction("Login", "Akun");
        }

        long nomorInvoiceBaru = await Db.Invoice.LongCountAsync() + 1;
        DateTime today = DateTime.Today;
        string nomorInvoice = $"{nomorInvoiceBaru:00000}/{today.Month}/{today.Year}";

        List<Pekerjaan> daftarPekerjaan = viewModel.DaftarPekerjaan.Select(p => new Pekerjaan()
            {
                Keterangan = p.Keterangan,
                Nominal = p.Nominal
            })
            .ToList();

        Invoice invoiceBaru = new Invoice()
        {
            NomorInvoice = nomorInvoice,
            DaftarPekerjaan = daftarPekerjaan,
            UsernamePembuka = username,
            StatusInvoice = StatusInvoice.BelumDikerjakan,
            TanggalInvoice = DateTime.Now
        };

        Db.Invoice.Add(invoiceBaru);
        await Db.SaveChangesAsync();

        TempData["Success"] = true;
        TempData["CreatedInvoice"] = nomorInvoiceBaru;
        return RedirectToAction("InvoiceBaru");
    }

    [HttpPost]
    public async Task<IActionResult> InvoiceBaru([FromForm] InvoiceBaruViewModel viewModel)
    {
        ValidationResult validationResult = await InvoiceBaruViewModelValidator.ValidateAsync(viewModel);
        if (!validationResult.IsValid)
        {
            validationResult.AddToModelState(ModelState);
            return View(viewModel);
        }

        string? username = User.Claims.FirstOrDefault(c => c.Type == ClaimTypes.NameIdentifier)?.Value;
        if (username is null)
        {
            await HttpContext.SignOutAsync();
            return RedirectToAction("Login", "Akun");
        }

        long nomorInvoiceBaru = await Db.Invoice.LongCountAsync() + 1;
        DateTime today = DateTime.Today;
        string nomorInvoice = $"{nomorInvoiceBaru:00000}/{today.Month}/{today.Year}";

        List<Pekerjaan> daftarPekerjaan = viewModel.DaftarPekerjaan.Select(p => new Pekerjaan()
            {
                Keterangan = p.Keterangan,
                Nominal = p.Nominal
            })
            .ToList();

        Invoice invoiceBaru = new Invoice()
        {
            NomorInvoice = nomorInvoice,
            DaftarPekerjaan = daftarPekerjaan,
            UsernamePembuka = username,
            StatusInvoice = StatusInvoice.BelumDikerjakan,
            TanggalInvoice = DateTime.Now
        };

        Db.Invoice.Add(invoiceBaru);
        await Db.SaveChangesAsync();

        TempData["Success"] = true;
        TempData["CreatedInvoice"] = nomorInvoiceBaru;
        return RedirectToAction("InvoiceBaru");
    }

Anton•2y ago

List<Pekerjaan> DaftarPekerjaan Are these entities tracked? In the view model by EF core

kunio_kunOP•2y ago

it is not oh wait with the DbContext above, isnt it tracked? viewModel.DaftarPekerjaan is not tracked

namespace Shared.ViewModels;

public class InvoiceBaruViewModel
{
    public int IdCustomer { get; set; }
    public List<PekerjaanViewModel>? DaftarPekerjaan { get; set; }
}

namespace Shared.ViewModels;

public class InvoiceBaruViewModel
{
    public int IdCustomer { get; set; }
    public List<PekerjaanViewModel>? DaftarPekerjaan { get; set; }
}

namespace Shared.ViewModels;

public class PekerjaanViewModel
{
    public decimal Nominal { get; set; }
    public string Keterangan { get; set; }
}

namespace Shared.ViewModels;

public class PekerjaanViewModel
{
    public decimal Nominal { get; set; }
    public string Keterangan { get; set; }
}

I only have this 3 DbSet in the db context

    public DbSet<Akun> Akun { get; set; }
    public DbSet<Invoice> Invoice { get; set; }
    public DbSet<Customer> Customer { get; set; }

    public DbSet<Akun> Akun { get; set; }
    public DbSet<Invoice> Invoice { get; set; }
    public DbSet<Customer> Customer { get; set; }

It isnt necessary if I dont want to manage the table manually right?

Anton•2y ago

I'm not sure, but you might need to add the daftarPekerjaan objects to DbContext first Db.Set<Perkerjar>().AddRange(daftarPekerjaan)

kunio_kunOP•2y ago

I saw samples in the internet and it could automatically add entities

Anton•2y ago

Show me Pekerjaan

kunio_kunOP•2y ago

namespace Shared.Models;

public class Pekerjaan
{
    public int Id { get; set; }
    public decimal Nominal { get; set; }
    public string Keterangan { get; set; }
    public int InvoiceId { get; set; }
    public Invoice Invoice { get; set; }
}

namespace Shared.Models;

public class Pekerjaan
{
    public int Id { get; set; }
    public decimal Nominal { get; set; }
    public string Keterangan { get; set; }
    public int InvoiceId { get; set; }
    public Invoice Invoice { get; set; }
}

Anton•2y ago

And OnModelCreating

kunio_kunOP•2y ago

using Microsoft.EntityFrameworkCore;
using Shared.Models;

namespace InvoiceManagement.Database;

public class InvoiceDbContext : DbContext
{
    private readonly string _dbPath = Path.Join(AppDomain.CurrentDomain.BaseDirectory, "invoice-data.sqlite3");

    public DbSet<Akun> Akun { get; set; }
    public DbSet<Invoice> Invoice { get; set; }
    public DbSet<Customer> Customer { get; set; }
    public DbSet<Pekerjaan> Pekerjaan { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite($"Data Source={_dbPath}");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Akun>().OwnsOne<Pengguna>(akun => akun.Pengguna);
        modelBuilder.Entity<Invoice>()
            .HasMany(invoice => invoice.DaftarPekerjaan)
            .WithOne(pekerjaan => pekerjaan.Invoice)
            .HasForeignKey(pekerjaan => pekerjaan.InvoiceId);
        modelBuilder.Entity<Invoice>().HasIndex(invoice => invoice.NomorInvoice).IsUnique();
        modelBuilder.Entity<Invoice>().HasKey(i => i.Id);
        modelBuilder.Entity<Akun>().HasIndex(akun => akun.Username).IsUnique();
        modelBuilder.Entity<Pekerjaan>().HasKey(p => p.Id);
        base.OnModelCreating(modelBuilder);
    }
}

using Microsoft.EntityFrameworkCore;
using Shared.Models;

namespace InvoiceManagement.Database;

public class InvoiceDbContext : DbContext
{
    private readonly string _dbPath = Path.Join(AppDomain.CurrentDomain.BaseDirectory, "invoice-data.sqlite3");

    public DbSet<Akun> Akun { get; set; }
    public DbSet<Invoice> Invoice { get; set; }
    public DbSet<Customer> Customer { get; set; }
    public DbSet<Pekerjaan> Pekerjaan { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite($"Data Source={_dbPath}");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Akun>().OwnsOne<Pengguna>(akun => akun.Pengguna);
        modelBuilder.Entity<Invoice>()
            .HasMany(invoice => invoice.DaftarPekerjaan)
            .WithOne(pekerjaan => pekerjaan.Invoice)
            .HasForeignKey(pekerjaan => pekerjaan.InvoiceId);
        modelBuilder.Entity<Invoice>().HasIndex(invoice => invoice.NomorInvoice).IsUnique();
        modelBuilder.Entity<Invoice>().HasKey(i => i.Id);
        modelBuilder.Entity<Akun>().HasIndex(akun => akun.Username).IsUnique();
        modelBuilder.Entity<Pekerjaan>().HasKey(p => p.Id);
        base.OnModelCreating(modelBuilder);
    }
}

And here's Invoice

using System.ComponentModel.DataAnnotations;

namespace Shared.Models;

public class Invoice
{
    public int Id { get; set; }
    [Display(Name = "Nomor Invoice")]
    public string NomorInvoice { get; set; }
    public DateTime TanggalInvoice { get; set; }
    public string UsernamePembuka { get; set; }
    public Akun AkunPembuka { get; set; }
    public ICollection<Pekerjaan> DaftarPekerjaan { get; set; }
    [Display(Name = "Status Invoice")]
    public StatusInvoice StatusInvoice { get; set; }
}

using System.ComponentModel.DataAnnotations;

namespace Shared.Models;

public class Invoice
{
    public int Id { get; set; }
    [Display(Name = "Nomor Invoice")]
    public string NomorInvoice { get; set; }
    public DateTime TanggalInvoice { get; set; }
    public string UsernamePembuka { get; set; }
    public Akun AkunPembuka { get; set; }
    public ICollection<Pekerjaan> DaftarPekerjaan { get; set; }
    [Display(Name = "Status Invoice")]
    public StatusInvoice StatusInvoice { get; set; }
}

Now trying to insert it like

List<Pekerjaan> daftarPekerjaan = viewModel.DaftarPekerjaan.Select(p => new Pekerjaan()
            {
                Keterangan = p.Keterangan,
                Nominal = p.Nominal
            })
            .ToList();
        
        Db.Pekerjaan.AddRange(daftarPekerjaan);
        
        Invoice invoiceBaru = new Invoice()
        {
            NomorInvoice = nomorInvoice,
            DaftarPekerjaan = daftarPekerjaan,
            UsernamePembuka = username,
            StatusInvoice = StatusInvoice.BelumDikerjakan,
            TanggalInvoice = DateTime.Now
        };

        Db.Invoice.Add(invoiceBaru);
        
        await Db.SaveChangesAsync();

List<Pekerjaan> daftarPekerjaan = viewModel.DaftarPekerjaan.Select(p => new Pekerjaan()
            {
                Keterangan = p.Keterangan,
                Nominal = p.Nominal
            })
            .ToList();
        
        Db.Pekerjaan.AddRange(daftarPekerjaan);
        
        Invoice invoiceBaru = new Invoice()
        {
            NomorInvoice = nomorInvoice,
            DaftarPekerjaan = daftarPekerjaan,
            UsernamePembuka = username,
            StatusInvoice = StatusInvoice.BelumDikerjakan,
            TanggalInvoice = DateTime.Now
        };

        Db.Invoice.Add(invoiceBaru);
        
        await Db.SaveChangesAsync();

and it throws

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)

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)

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

kunio_kunOP•2y ago

Not yet

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

✅ Inserting record with one-to-many relationship EF Core