Insert data into multiple tables with transaction and rollback on failure-CodePudding

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?

CodePudding user response：

Provided they are inserted using the same DbContext and a single SaveChanges() call, they will be committed in the same transaction.

If the entities are related to one another with FKs, then the best solution where you want the DB to manage the PKs and automatically resolve the FKs would be to set up the navigation properties and add the subordinate entities to the top-level ones, before adding the top-level ones to their respective DBSets and calling SaveChanges.

In your case you would want something like:

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; }
}

With EF Core 5 the FK ClassMainId in ClassA and B are optional. EF Core can simply use Shadow properties for the mapping rather than exposing them as properties. (Recommended)

Now when you go to create a ClassMain and associated A and B, you can just instantiate the new instances, populating the various fields, and add the top-level entity to the 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();
}

You can also do this without Identity PKs where you can set the PKs/FKs in code, though you are responsible for ensuring they are unique. By setting up the navigation properties EF will take care of associating FKs when everything is saved. They will all save together or none at all.

Edit: If you don't want ClassMain to contain collections for the ClassA and ClassB instances, you can remove those collections and InverseProperty attributes. Populating the instances would look more like:

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();
}

In this case the ClassMain instance would be saved along with the ClassA and B.

CodePudding user response：

If you have relationships between tables, the solution that @Steve Py said is the best. But if you don't have any relationship between entities, you can use explicit transactions.

    using var context = new AppDbContext();
    using var transaction = context.Database.BeginTransaction();

    var newMain = new ClassMain();
    context.ClassMains.Add(newMain);
    context.SaveChanges();

    var newClassA = new ClassA
    {
        ClassMainId = newMain.ClassMainId
    };

    context.ClassAs.Add(newClassA);

    var newClassB = new ClassB
    {
        ClassMainId = newMain.ClassMainId
    };

    context.ClassBs.Add(newClassB);

    context.SaveChanges();

    transaction.Commit();