时间戳

首页 > 解决方案 > Insert data into multiple tables with transaction and rollback on failure

asp.net-mvc - Insert data into multiple tables with transaction and rollback on failure

问题描述

What is the best way to store the data to database, while inserting multiple classes together to database. I have around 10 classes where I would like to save all the data together to database.

Posting some classes with relations:

Public class ClassMain
{
     public int ClassMainId { get; set; }
}

Public class ClassA
{
      public int ClassAId { get; set; }
      public int ClassMainId { get; set; }
      public string ClassAName { get; set; }
}

Public class ClassB
{
      public int ClassBId { get; set; }
      public int ClassMainId { get; set; }
      public string ClassBName { get; set; }
}

Like above I have some other tables with the relation

I would like to insert this data in one go, if there's any failure, I would like to rollback the data, in Entity Framework or stored procedure approach do we have the approach to do this.

I am going through this approach to achieve

https://dotnettutorials.net/lesson/unit-of-work-csharp-mvc/

Is there any other approach?

标签： asp.net-mvcentity-framework

解决方案

如果它们是使用相同的 DbContext 和单个SaveChanges()调用插入的，它们将在同一个事务中提交。

如果实体通过 FK 相互关联，那么您希望 DB 管理 PK 并自动解析 FK 的最佳解决方案是设置导航属性并将从属实体添加到顶级实体，然后将顶级的添加到各自的 DBSet 并调用 SaveChanges。

在你的情况下，你会想要这样的东西：

public class ClassMain
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ClassMainId { get; set; }

    [InverseProperty("ClassMain")]
    public virtual ICollection<ClassA> ClassAs { get; set;} = new List<ClassA>();
    [InverseProperty("ClassMain")]
    public virtual ICollection<ClassB> ClassBs { get; set;} = new List<ClassB>();
}

public class ClassA
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ClassAId { get; set; }

    public int ClassMainId { get; set; }
    [ForeignKey("ClassMainId")]
    public virtual ClassMain ClassMain{ get; set; }
    public string ClassAName { get; set; }
}

Public class ClassB
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ClassBId { get; set; }

    public int ClassMainId { get; set; }
    [ForeignKey("ClassMainId")]
    public virtual ClassMain ClassMain{ get; set; }

    public string ClassBName { get; set; }
}

对于 EF Core 5，ClassA 和 B 中的 FK ClassMainId 是可选的。EF Core 可以简单地使用 Shadow 属性进行映射，而不是将它们作为属性公开。（推荐的）

现在，当您创建一个 ClassMain 以及关联的 A 和 B 时，您只需实例化新实例，填充各种字段，并将顶级实体添加到 DbSet。

using(var context = new AppDbContext())
{
    var newMain = new ClassMain
    {
        // ... set main properties, but don't worry about PK

        ClassA = new ClassA { ClassAName = someAName },
        ClassB = new ClassB { ClassBName = someBName }
    };
    context.ClassMains.Add(newMain);
    context.SaveChanges();
}

您也可以在没有身份 PK 的情况下执行此操作，您可以在代码中设置 PK/FK，但您有责任确保它们是唯一的。通过设置导航属性，EF 将负责在保存所有内容时关联 FK。他们将一起保存或根本不保存。

编辑：如果您不希望 ClassMain 包含 ClassA 和 ClassB 实例的集合，您可以删除这些集合和 InverseProperty 属性。填充实例看起来更像：

using(var context = new AppDbContext())
{
    var newMain = new ClassMain();
    var classA = new ClassA { ClassAName = someAName, ClassMain = newMain };
    var classB = new ClassB { ClassBName = someBName, ClassMain = newMain };

    context.ClassAs.Add(classA);
    context.ClassBs.Add(classB);
    context.SaveChanges();
}

在这种情况下，ClassMain 实例将与 ClassA 和 B 一起保存。

推荐阅读