EFCore - Seed data only if it doesn't exist in another table

Question

I want to seed some data using EntityFramework in .net core 3.1 and I'm facing an issue:

I've got two SQL tables (so two DbSet<>):

public virtual DbSet<TableA> TableA { get; set; }
public virtual DbSet<TableB> TableB { get; set; }

Table A has this structure:

[Key] 
public int Id { get; set; } // PK
public string EnglishText { get; set; } // some value

Table B has this structure:

[Key]
public int Id { get; set; } // PK
public int TableAId { get; set; } // FK to Table A
public string TranslatedText { get; set; } // some value

For seeding the data for table A, I use the OnModelCreating(ModelBuilder modelBuilder) method in the DBContext:

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

        // Seed TableA
        modelBuilder.Entity<TableA>().HasData(
            new TableA { Id = 1, EnglishText = "first data"}
        );
    }

I then want to seed table B only if it doesn't contain a record that reference TableA (via the FK), I'm not sure how to do that in the OnModelCreating method.

I guess I'm after something like:

modelBuilder.Entity<TableB>().HasData(var X = new TableB{...}).Where([X.TableAId is not in TableA])

If someone has an idea or can point me in a direction, that would be very much appreciated.

Rzassar Rzassar · Accepted Answer · 2020-08-04T09:21:00

I had the exact same problem. So i decided to extend DataBuilder as follow:

using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;

namespace Infrastructure
{
    static class DataBuilderDynamicData
    {
        private static object asyncObject = new object();
        private static Dictionary<Type, List<EntityBase>> dynamicData = new Dictionary<Type, List<EntityBase>>();
        public static DataBuilder<TEntity> HasDynamicData<TEntity>(this EntityTypeBuilder<TEntity> builder, Func<Dictionary<Type, List<EntityBase>>, TEntity> Resolver) where TEntity : EntityBase
        {
            var targetType = typeof(TEntity);
            TEntity model;

            //Note: Not necessary, but just in case several threads reached the same code scope.
            //Note: Lock has some performance hits, but it is negligible due to the fact that Seeding is an In-Design-time process (not run-time).
            lock (asyncObject)
            {
                model = Resolver(dynamicData);
                if (dynamicData.ContainsKey(targetType))
                    dynamicData[targetType].Add(model);
                else
                    dynamicData.Add(targetType, new List<EntityBase> { model });
            }

            return builder.HasData(model);
        }
    }
}

Then use it instead of HasData() like following code:

        foreach (var model in models)
        {
            builder.HasDynamicData<ProvinceMap>((dic) =>
            {
                //Please note that "Province" should be already seeded with "HasDynamicData" method. Otherwise, you get an error here.
                var provinces = dic[typeof(Province)];
                return new ProvinceMap
                {
                   Id = 1,
                   ProvinceId = provinces.Cast<Province>().First(item => item.Name=="SOME-PROVINCE"),
                   //Some other seed data.
                };
            });
        }

Please note that EntityBase is my custom base class. You can use object instead.

public abstract class EntityBase
{
    public int Id { get; set; }
}

Every entities e.g. Province and ProvinceMap are inherited from EntityBase.
You can make sure that dictionary already has the base data you want (in my example: Province class) with placing the relevent config code prior to consumer class ()in my example ProvinceMap class:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration<Province>(new ProvinceConfiguration()); //First, declare the base class.
        modelBuilder.ApplyConfiguration<ProvinceMap>(new ProvinceMapConfiguration()); //Next, declare the consumer class.
    }

You can implement other overloads of the HasData() method to fit your needs.

EFCore - Seed data only if it doesn't exist in another table

1 Answers