EF Core and SQL multiple cascade-CodePudding

I am trying to model payments in ef-core using SQL Server. A payment has a from side and a to side, in this case say a user or actor for each. I have a transaction class that has relationships to a FromActor and to a ToActor. The generated migration fails to update in the database, saying there may be multiple cascade paths. I have read that I need to override the default cascades in OnModelCreating method in the db context but I am not sure how. Fairly new to EF and migrations, I am just following https://docs.microsoft.com/en-us/ef/core/get-started/overview/first-app?tabs=visual-studio.

public class Actor
{
    public int ActorId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public List<Transaction> Transactions = new List<Transaction>();
}

public class Transaction
{
    public int TransactionId { get; set; }
    public DateTime TimestampUtc { get; set; }
    public decimal Amount { get; set; }
  
    public int FromActorId { get; set; }
    public Actor FromActor { get; set; }
    public int ToActorId { get; set; }
    public Actor ToActor { get; set; }
}

Introducing FOREIGN KEY constraint 'FK_Transactions_Actors_ToActorId' on table 'Transactions' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Can you please let me know how to make this work in the db context? Or do I need to model my domain differently?

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        ??
    }

CodePudding user response：

You can modify the properties of your modelbuilder for each of your entities the way you want to with the modelBuilder yes :

if I were to give you an example with your actors, it could be :

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Transaction>(entity =>
            {
                entity.HasOne(t => t.FromActor)
                    .WithMany(a => a.Transactions)
                    .HasForeignKey(t => t.FromActorId)
                    .OnDelete(DeleteBehavior.NoAction)
                    .HasConstraintName("fk_transaction_id_from_actor");
                entity.HasOne(t => t.ToActor)
                    .WithMany(a => a.Transactions)
                    .HasForeignKey(t => t.ToActorId)
                    .OnDelete(DeleteBehavior.NoAction)
                    .HasConstraintName("fk_transaction_id_to_actor");
            });
    }

Or something similar. As you can see, the syntax is pretty self-explanatory.

The entity Transaction has one Actor FromActor, that FromActor has a list of Transactions. The foreign key should be FromActorId, and on delete, you don't do anything to the entity. The name of the contraint should be "fk_transaction_id_from_actor".

This allows you to change constraints, behaviours and naming at will. It can also permit you to add data in the model if necessary through migration :

modelBuilder.Entity<Actor>().HasData(
                new
                {
                    ActorId = 1,
                    FirstName = "John",
                    LastName = "Doe"
                },
                new
                {
                    ActorId = 2,
                    FirstName = "Jane",
                    LastName = "Doe"
                });

Every change in the context will be registered in the next migration when you do your "dotnet ef migrations add myMigration", so if you add data in the HasData, and you don't work alone on the project, be sure to keep track of the ids or the migration will fail ! I hope that was enough to help you.

CodePudding user response：

you have to add some navigation properties to your classes

public class Actor
{
    public int ActorId { get; set; }
     .....

    public virtual List<Transaction> FromActors {get; set;} 
   public virtual List<Transaction> ToActors { get; set; }

}

public class Transaction
{
    public int TransactionId { get; set; }
    ....

    public int? FromActorId { get; set; }
    public  virtual Actor FromActor { get; set; }

    public int? ToActorId { get; set; }
    public virtual Actor ToActor { get; set; }
}