Seed data migrations don't compensate for on delete cascade

[smerliere]

Hi,

I have a configuration file that automatically seed data in my database.

After I create a first migration, all tables are filled with data (including relation tables) when I update the database with the following command :
dotnet ef database update

Here is an example of data from migration file that I want to insert in my database :

migrationBuilder.InsertData(
    table: "REL_FUNCTION_MEASURE",
    columns: new[] { "MEASURE_UID", "FUNCTION_UID" },
    values: new object[,]
    {
         { 4, 3 },
         { 4, 4 },
         { 4, 5 },
         { 5, 5 },
         { 5, 2 },
         { 5, 3 },
         { 5, 4 },
         { 4, 2 },
         { 5, 1 },
         { 4, 1 },
         { 3, 2 },
         { 3, 4 },
         { 3, 3 },
         { 3, 1 },
         { 2, 5 },
         { 2, 4 },
         { 2, 3 },
         { 2, 2 },
         { 2, 1 },
         { 1, 4 },
         { 1, 3 },
         { 1, 2 },
         { 1, 1 },
         { 3, 5 },
         { 1, 5 }
    });

And here is the data in the seed file :

modelBuilder.Entity<MeasureEntity>().HasData(

	new MeasureEntity { Id = 1, Name = "Quantity", Description = "Quantity measure", Type = MeasureTypeEnum.QUANTITY },
	new MeasureEntity { Id = 2, Name = "Unit price", Description = "Unit price measure", Type = MeasureTypeEnum.UNIT_PRICE },
	new MeasureEntity { Id = 3, Name = "Unit margin", Description = "Unit margin measure", Type = MeasureTypeEnum.UNIT_MARGIN },
	new MeasureEntity { Id = 4, Name = "Unit weight", Description = "Unit weight measure", Type = MeasureTypeEnum.UNIT_WEIGHT },
	new MeasureEntity { Id = 5, Name = "Amount", Description = "Amount measure", Type = MeasureTypeEnum.AMOUNT }
);

modelBuilder.Entity<FunctionEntity>().HasData(

	new FunctionEntity { Id = 1, Name = "Sum", Description = "Sum function", Type = FunctionTypeEnum.SUM },
	new FunctionEntity { Id = 2, Name = "Min", Description = "Min function", Type = FunctionTypeEnum.MIN },
	new FunctionEntity { Id = 3, Name = "Max", Description = "Max function", Type = FunctionTypeEnum.MAX },
	new FunctionEntity { Id = 4, Name = "Average", Description = "Average function", Type = FunctionTypeEnum.MEAN },
	new FunctionEntity { Id = 5, Name = "Median", Description = "Median function", Type = FunctionTypeEnum.MEDIAN }
);

modelBuilder.Entity<FunctionMeasureRelationEntity>().HasData(

	new FunctionMeasureRelationEntity { FunctionId = 1, MeasureId = 1 },
	new FunctionMeasureRelationEntity { FunctionId = 2, MeasureId = 1 },
	new FunctionMeasureRelationEntity { FunctionId = 3, MeasureId = 1 },
	new FunctionMeasureRelationEntity { FunctionId = 4, MeasureId = 1 },
	new FunctionMeasureRelationEntity { FunctionId = 5, MeasureId = 1 },

	// Unit price functions
	new FunctionMeasureRelationEntity { FunctionId = 1, MeasureId = 2 },
	new FunctionMeasureRelationEntity { FunctionId = 2, MeasureId = 2 },
	new FunctionMeasureRelationEntity { FunctionId = 3, MeasureId = 2 },
	new FunctionMeasureRelationEntity { FunctionId = 4, MeasureId = 2 },
	new FunctionMeasureRelationEntity { FunctionId = 5, MeasureId = 2 },

	// Unit margin functions
	new FunctionMeasureRelationEntity { FunctionId = 1, MeasureId = 3 },
	new FunctionMeasureRelationEntity { FunctionId = 2, MeasureId = 3 },
	new FunctionMeasureRelationEntity { FunctionId = 3, MeasureId = 3 },
	new FunctionMeasureRelationEntity { FunctionId = 4, MeasureId = 3 },
	new FunctionMeasureRelationEntity { FunctionId = 5, MeasureId = 3 },

	// Unit weight functions
	new FunctionMeasureRelationEntity { FunctionId = 1, MeasureId = 4 },
	new FunctionMeasureRelationEntity { FunctionId = 2, MeasureId = 4 },
	new FunctionMeasureRelationEntity { FunctionId = 3, MeasureId = 4 },
	new FunctionMeasureRelationEntity { FunctionId = 4, MeasureId = 4 },
	new FunctionMeasureRelationEntity { FunctionId = 5, MeasureId = 4 },

	// Amount functions
	new FunctionMeasureRelationEntity { FunctionId = 1, MeasureId = 5 },
	new FunctionMeasureRelationEntity { FunctionId = 2, MeasureId = 5 },
	new FunctionMeasureRelationEntity { FunctionId = 3, MeasureId = 5 },
	new FunctionMeasureRelationEntity { FunctionId = 4, MeasureId = 5 },
	new FunctionMeasureRelationEntity { FunctionId = 5, MeasureId = 5 }
);

However when I create a new migration, I found out that relations table are not filled with data from my seed file (FunctionMeasureRelationEntity as exemple) in my database.

I checked the new migration files and I noticed that I didn't find data insertions in my relation table (that I had in my previous migration).

Is that classic comportement from EF Core and I forgot to set some configuration in my code to keep insertion process for my relation tables ?

Further technical details

EF Core version: 2.2.3
Database Provider: Pomelo MySQL
IDE: Visual Studio Code version: 1.33.0

[ajcvickers]

@smerliere I'm likely not 100% following what you are saying, but if the data did not change between the first and second migration, then there should not be anything to do in the second migration, and hence it should not include the seed data. In other words, the first migration will insert the data, and the second migration does not need to because the first migration already did it.

I would only expect to see data inserts/updates in subsequent migrations if the data changes.

If this doesn't help, then can you file a small, runnable project/solution that demonstrates the behavior you are seeing so that we can investigate further.

[smerliere]

Thank you for your answer.

I send you a runnable project.
TestSeeding.zip

Don't forget to configure a MariaDB's database (version:10.3) and modify the configuration in appsettings.json

In this project, we have three entities that represent our database tables:

-MeasureEntity :

/// <summary>
    /// Measure entity.
    /// </summary>
    [Table("DIM_MEASURE")]
    public class MeasureEntity : EntityDateBase, IEntityBase<int>
    {
        /// <summary>
        /// Id.
        /// </summary>
        [Key]
        [Column("MEASURE_UID")]
        public int Id { get; set; }

        /// <summary>
        /// Name.
        /// </summary>
        [Column("MEASURE_NAME")]
        [Required]
        public string Name { get; set; }

        /// <summary>
        /// Description.
        /// </summary>
        [Column("MEASURE_DESCRIPTION")]
        [Required]
        public string Description { get; set; }

        /// <summary>
        /// Type.
        /// </summary>
        [Column("MEASURE_TYPE")]
        [Required]
        public MeasureTypeEnum Type { get; set; }

        /// <summary>
        /// Functions relation list.
        /// </summary>
        public IEnumerable<FunctionMeasureRelationEntity> Functions { get; set; }
    }

-FunctionEntity

    /// <summary>
    /// Function entity.
    /// </summary>
    [Table("DIM_FUNCTION")]
    public class FunctionEntity : EntityDateBase, IEntityBase<int>
    {
        /// <summary>
        /// Id.
        /// </summary>
        [Key]
        [Column("FUNCTION_UID")]
        public int Id { get; set; }

        /// <summary>
        /// Name.
        /// </summary>
        [Column("FUNCTION_NAME")]
        [Required]
        public string Name { get; set; }

        /// <summary>
        /// Description.
        /// </summary>
        [Column("FUNCTION_DESCRIPTION")]
        [Required]
        public string Description { get; set; }

        /// <summary>
        /// Type.
        /// </summary>
        [Column("FUNCTION_TYPE")]
        [Required]
        public FunctionTypeEnum Type { get; set; }

        /// <summary>
        /// Measures relation list.
        /// </summary>
        public IEnumerable<FunctionMeasureRelationEntity> Measures { get; set; }
    }

-MeasureFunctionEntity (relation many to many between MeasureEntity and FunctionEntity)

    /// <summary>
    ///  FunctionMeasure relation entity 
    /// </summary>
    [Table("REL_FUNCTION_MEASURE")]
    public class FunctionMeasureRelationEntity
    {
        /// <summary>
        /// Measure Id.
        /// </summary>
        [Column("MEASURE_UID")]
        [Required]
        public int MeasureId { get; set; }

        /// <summary>
        /// Function Id.
        /// </summary>
        [Column("FUNCTION_UID")]
        [Required]
        public int FunctionId { get; set; }

        /// <summary>
        /// Measure relation.
        /// </summary>
        public MeasureEntity Measure { get; set; }

        /// <summary>
        /// Function relation.
        /// </summary>
        public FunctionEntity Function { get; set; }
    }

There is already an initial migration that you can run with :
dotnet ef database update

to create tables and seed associated datas included in ApplicationDbContextSeed.cs

To reproduce our problem, you can add a property into MeasureEntity like the following example :

/// <summary>
    /// Measure entity.
    /// </summary>
    [Table("DIM_MEASURE")]
    public class MeasureEntity : EntityDateBase, IEntityBase<int>
    {
        /// <summary>
        /// Id.
        /// </summary>
        [Key]
        [Column("MEASURE_UID")]
        public int Id { get; set; }

        /// <summary>
        /// Name.
        /// </summary>
        [Column("MEASURE_NAME")]
        [Required]
        public string Name { get; set; }

        /// <summary>
        /// Description.
        /// </summary>
        [Column("MEASURE_DESCRIPTION")]
        [Required]
        public string Description { get; set; }

        **/// <summary>
        /// Column testing.
        /// </summary>
        [Column("MEASURE_COLUMN_TESTING")]
        [Required]
        public string TestingColumn { get; set; }**

        /// <summary>
        /// Type.
        /// </summary>
        [Column("MEASURE_TYPE")]
        [Required]
        public MeasureTypeEnum Type { get; set; }

        /// <summary>
        /// Functions relation list.
        /// </summary>
        public IEnumerable<FunctionMeasureRelationEntity> Functions { get; set; }
    }

Then, you can create and apply a new migration. You will can see that datas in Measure and Function tables are here. However the table REL_FUNCTION_MEASURE is empty.

[aborderon]

@ajcvickers I am on the same issue and I just tested with SQLite to isolate the problem of MariaDB and Pomelo and I confirm that I have the same problem.

At the second migration, the data in the relationship table is empty.

[AndriySvyryd]

The issue here is that the FKs on REL_FUNCTION_MEASURE have OnDelete set to Cascade, but we don't compensate for this when dropping rows.
The workaround would be to manually copy the InsertData for the relationship tables to the migrations that drop principal rows.

[aborderon]

@AndriySvyryd Ok I understand why we are losing relationship data.

On the other hand I find that your compensation solution is not very practical in the case where we need to change the database several times because it would be necessary to copy / paste the insert data for each relation tables in each new migration.

It's just not feasible for a production environment ...

It would not be possible to act on this problem with the DeleteBehavior enumerator type?

[AndriySvyryd]

@aborderon Sure, you can also change the FKs to DeleteBehavior.Restrict, add the migration, then change them back and add another migration.

[aborderon]

I tried to add OnDelete.(DeleteBehavior.Restrict) on one relation of the intermediate table like :

        /// <Summary>
        /// Function Measure relation configuration.
        /// </Summary>
        /// <param name="builder"></param>
        void ConfigureFunctionMeasureRelation(EntityTypeBuilder<FunctionMeasureRelationEntity> builder)
        {
            builder.HasKey(fm => new { fm.MeasureId, fm.FunctionId });

            builder.HasOne(fm => fm.Measure)
                .WithMany(m => m.Functions)
                .HasForeignKey(fm => fm.MeasureId);

            builder.HasOne(fm => fm.Function)
                .WithMany(f => f.Measures).IsRequired()
                .HasForeignKey(fm => fm.FunctionId)
                .OnDelete(DeleteBehavior.Restrict);
        }

But suddenly I get a constraint error during the second update (SQLite Error 19: 'FOREIGN KEY constraint failed'.). On the EF core doc it is specified:

Note that in this case your code must synchronize the child entities and their foreign key values ​​manually. Otherwise, constraint exceptions will be lifted.

[AndriySvyryd]

@aborderon For SQLite many schema operations are not supported, so you would have to use the first workaround.

[aborderon]

@AndriySvyryd Yes, I realized it right after, I connected the initial Pomelo.MySQL provider to my MariaDB database but I get the same problem during the second update:

MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot delete or update a parent row: a foreign key constraint fails (ibis_api.REL_FUNCTION_MEASURE, CONSTRAINT FK_REL_FUNCTION_MEASURE_DIM_FUNCTION_FUNCTION_UID FOREIGN KEY (FUNCTION_UID) REFERENCES DIM_FUNCTION (FUNCTION_UID)) ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot delete or update a parent row: a foreign key constraint fails (ibis_api.REL_FUNCTION_MEASURE, CONSTRAINT FK_REL_FUNCTION_MEASURE_DIM_FUNCTION_FUNCTION_UID FOREIGN KEY (FUNCTION_UID) REFERENCES DIM_FUNCTION (FUNCTION_UID))

I also tried to define the relationship directly on each tables (MeasureEntity and FunctionEntity) but without any possitive results.

[AndriySvyryd]

@aborderon Make sure that you are changing the OnDelete value in the same migration as the seed data.

Also you can avoid needless reseeding by not setting CreatedDate and UpdatedDate to DateTime.Now.

Post your migrations if you are still having issues.

[aborderon]

Hello @AndriySvyryd ,

I just did different tests and I understood the problem.

Effectively, by removing the Datetime.Now assignment on the CreatedDate and UpdateDate properties there is no problem even with a cascading configuration it works fine.

On the other hand, I use MariaDB in production database and SQLite in database for unit tests and functional tests.

That is to say at the launch of unit tests, migrations and seeds are also launched to be in line with the production environment.

The problem is that SQLite obviously does not support the ValueGeneratedOnAdd propertybuilder defined in the DbContext;

builder.Entity<FunctionEntity>().Property(c => c.CreatedDate).ValueGeneratedOnAdd();
builder.Entity<MeasureEntity>().Property(c => c.CreatedDate).ValueGeneratedOnAdd();
builder.Entity<FunctionEntity>().Property(c => c.UpdatedDate).ValueGeneratedOnAddOrUpdate();
builder.Entity<MeasureEntity>().Property(c => c.UpdatedDate).ValueGeneratedOnAddOrUpdate();

I also tried to force it with the annotation [DatabaseGenerated (DatabaseGeneratedOption.Identity)] for the property CreatedDate and [DatabaseGenerated (DatabaseGeneratedOption.Computed)] for UpdatedDate but nothing to do, I always get the error:

Microsoft.Data.Sqlite.SqliteException: SQLite Error 19: 'NOT NULL constraint failed: DATA_CUSTOMER.CREATED_DATE'.

I remember why I had forced DateTime.Now on the properties ...

Do you have a solution to handle the case of SQLite for testing ?

[AndriySvyryd]

@aborderon You can add a default value just for SQLite:

if (Database.IsSqlite())
{
    builder.Entity<FunctionEntity>().Property(c => c.CreatedDate).HasDefaultValueSql("CURRENT_TIMESTAMP");
    builder.Entity<MeasureEntity>().Property(c => c.CreatedDate).HasDefaultValueSql("CURRENT_TIMESTAMP");
    builder.Entity<FunctionEntity>().Property(c => c.UpdatedDate).HasDefaultValueSql("CURRENT_TIMESTAMP");
    builder.Entity<MeasureEntity>().Property(c => c.UpdatedDate).HasDefaultValueSql("CURRENT_TIMESTAMP");
}

[aborderon]

@AndriySvyryd I had the same idea that you yesterday, it confirms me on my idea ;)

Thank you for your help, you can close the ticket.