无法添加或更新子行:外键约束失败mysql:ef core

xn1cxnb4  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(516)

尝试在mysql数据库中插入数据时获取dbupdateexception。使用pomelo.entityframeworkcore.mysql包从数据库中构建模型,该项目是在.netcore2.1中创建的
堆栈跟踪:
异常消息:无法添加或更新子行:外键约束失败( HMEBooking . Invoice ,约束 Invoice_ibfk_7 外键( BookingId )参考文献 Booking ( Id )on delete no action on update no action)堆栈跟踪:位于c:\projects\mysqlconnector\src\mysqlconnector\mysql.data.mysqlclient\mysqldatareader.cs中mysql.data.mysqlclient.mysqldatareader.activateresultset(resultset resultset)的第93行mysql.data.mysqlclient.mysqldatareader.readfirstresultsetasync(iobehavior iobehavior)c:\projects\mysqlconnector\src\mysqlconnector\mysql.data.mysqlclient\mysqldatareader.cs:mysql.data.mysqlclient.mysqldatareader.createasync(mysqlcommand命令,commandbehavior,iobehavior iobehavior)在c:\projects\mysqlconnector\src\mysqlconnector\mysql.data.mysqlclient\mysqldatareader.cs中:mysqlconnector.core.textcommandeexecutor.executereaderasync(string commandtext,mysqlparametercollection parametercollection,commandbehavior,iobehavior,c:\projects\mysqlconnector\src\mysqlconnector\core\textcommandexecutor.cs中的cancellationtoken cancellationtoken c:\projects\mysqlconnector\src\mysqlconnector\mysql.data.mysqlclient\mysqlcommand.cs中的第73行mysql.data.mysqlcommand.executedbdatareader(commandbehavior behavior)microsoft.entityframeworkcore.storage.internal.relationalcommand.execute(irelationalconnection连接、dbcommandmethod执行方法、iRelationalDictionary 2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary microsoft.entityframeworkcore.update.readermodificationcommandbatch.execute(irelationalconnection)中的2个参数值)
有订票,订票明细和发票型号。记账明细与记账有关,发票与记账和记账明细有关。
向数据库添加条目时,出现错误:无法添加或更新子行:外键约束失败。但当我在非.net核心应用程序中使用相同的模型时,它就可以工作了
如果我有什么地方出错,请告诉我。谢谢你的帮助。
下面是使用的模型

public partial class Booking
{
    public Booking()
    {
        BookingDetail = new HashSet<BookingDetail>();
        Invoice = new HashSet<Invoice>();
    }

    [Column(TypeName = "bigint(20)")]
    public long Id { get; set; }
    [Required]
    [Column(TypeName = "varchar(50)")]
    public string TempId { get; set; }

    [InverseProperty("Booking")]
    public ICollection<BookingDetail> BookingDetail { get; set; }
    [InverseProperty("Booking")]
    public ICollection<Invoice> Invoice { get; set; }
}
public partial class BookingDetail
{
    public BookingDetail()
    {
        Invoice = new HashSet<Invoice>();
    }

    [Column(TypeName = "bigint(20)")]
    public long Id { get; set; }
     [Required]
    [Column(TypeName = "varchar(50)")]
    public string CreatedBy { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime CreatedOn { get; set; }
    [Required]
    [Column(TypeName = "varchar(50)")]
    public string ModifiedBy { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime ModifiedOn { get; set; }
    [Column(TypeName = "bigint(20)")]
    public long BookingId { get; set; }       
    [ForeignKey("BookingId")]
    [InverseProperty("BookingDetail")]
    public Booking Booking { get; set; }
    [InverseProperty("BookingDetail")]
    public ICollection<Invoice> Invoice { get; set; }
}
 public partial class Invoice
{

    [Column(TypeName = "bigint(20)")]
    public long Id { get; set; }
    [Column(TypeName = "bigint(20)")]
    public long BookingId { get; set; }
    [Column(TypeName = "bigint(20)")]
    public long BookingDetailId { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime CreatedOn { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime CreatedBy { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime ModifiedOn { get; set; }
    [Required]
    [Column(TypeName = "varchar(50)")]
    public string ModifiedBy { get; set; }
    [ForeignKey("BookingId")]
    [InverseProperty("Invoice")]
    public Booking Booking { get; set; }
    [ForeignKey("BookingDetailId")]
    [InverseProperty("Invoice")]
    public BookingDetail BookingDetail { get; set; }
}
  public class Class1
{
    HMEBookingContext context = new HMEBookingContext();
    #region declarations
    public long BookingId { get; set; }
    public string CustomerEmailId { get; set; } = "admin@holidayme.com";      
    #endregion
    Random rnd = new Random();
    public void insert()
    {
        try
        {
            Booking booking = new Booking();
            booking.TempBookingId = BookingRefNumber.ToString();

            var bookingDetail = new List<BookingDetail>();
            BookingDetail detail = new BookingDetail();
            detail.CreatedBy = CustomerEmailId;
            detail.CreatedOn = DateTime.Now;
            detail.ModifiedBy = "1";
            detail.ModifiedOn = DateTime.UtcNow;
            detail.BookingStatusId = 1;

            var invoices = new List<Invoice>();
            Invoice invoice = new Invoice();
            invoices.Add(invoice);
            detail.Invoice = invoices;

            bookingDetail.Add(detail);

            booking.BookingDetail = bookingDetail;

            context.Add(booking);
            context.SaveChanges(true);
            BookingId=booking.Id
        }
        catch(DbUpdateException upd)
        {

        }

        catch(Exception ex)
        {

        }
    }

}

预订-预订明细、预订发票、预订明细-发票之间存在一对多关系
ef core版本:2.1数据库提供程序:pomelo.entityframeworkcore.mysql ide:visual studio 2017

pw9qyyiw

pw9qyyiw1#

错误在于 Invoice 记录的值无效 Invoice.BookingId .
查看您的测试代码,您可以创建一个 Invoice 对象, invoice ; 你从不分配 invoice.Booking 属性也不添加 invoicebooking.Invoices 集合,所以没有建立b/t二者的关系,因此为默认值 Invoice.BookingId 正在使用,并且该pk值在表中不存在 Booking .
分配 bookinginvoice.Booking 或添加 invoicebooking.Invoices 这个错误应该会消失。
然而,这个解决方案并不能纠正一个设计缺陷——一个 Invoice 引用两者 Booking s和 BookingDetail s和 BookingDetail 然后可以引用不同的 Booking 导致数据不一致的记录。应为存在单个函数依赖关系 Invoice ,或者 Invoice -> BookingDetail 或者 Invoice -> Booking ,而不是两者。

相关问题