EntityFrameworkCore Seeding Component

• Download pare.EntityFrameworkCore.Seeding.zip - 18.8 KB

Introduction

EntityFrameworkCore is a great improvement from the old days of construction model layers and describing them.  One thing I found lacking as most others do is the ability to seed data, either lookup tables or in some cases enums (flags - the various permutations of the flagged enum is still WIP).  The DbEnum use case I will address in a follow up article.  I have seen many articles where developers use the migration to seed data.  In my honest opinion, this is a workaround to a bigger problem.  In the following pieces of code, which I have written and re-used over multiple projects, we will be trying to eliminate the issue.

The minimum requirement to make any sense of this is to understand how we can mock the classes that the MS team use and then start injecting/using it to work for our needs in a generic way.

This is by no means polished and any input would be welcome.  (Even if you want me to dump it on GitHub.)

Please note that I do try to make minimilastic designs and not overload projects with packages.

This is a fairly long article, so bear with the code.

Background

The sample was done with VS 2017 utilizing netstandard1.6 for most projects and netcoreapp1.1 for the web api project.

Using the code

Check the database connection string, appsettings.development.cs

EntityFrameworkCore

The problem I found is that DbContext is that it is just a class, problem: Why not have an interface?  Have had this issue for many years.  Well, easy enough, lets grab the DbContext and decide what we need out of it and build an interface.  Now the question is: Why?  Well, I don't want to be loading EntityFrameworkCore package into every library that I have, why not just have it in a simplistic assembly and let the project dependancies sort themselves out.

The IDbContext:

public interface IDbContext
{
    DbSet<TDomainObject> Set<TDomainObject>() where TDomainObject : class;
    int SaveChanges();
    Task<int> SaveChangesAsync(CancellationToken cancellationToken);
    void Dispose();
    EntityEntry<TEntity> Add<TEntity>(TEntity entity) where TEntity : class;
    EntityEntry<TEntity> Update<TEntity>(TEntity entity) where TEntity : class;
    EntityEntry<TEntity> Attach<TEntity>(TEntity entity) where TEntity : class;
    ChangeTracker ChangeTracker { get; }
    EntityEntry<TEntity> Entry<TEntity>(TEntity entity) where TEntity : class;
}

The use of the interface will be on each ContextModel you create, since the DbContext have the same signature than the IDbContext interface, it will be compatible with EntityFrameworkCore.

Additional Interfaces

ISeedData & IDbEnum (the latter will be used to build classes to reflect the enum for DbContext and migration manupilation)

public interface ISeedData
{
    Task Seed(string environmentName);
    void SeedEnums(string environmentName); //although this is available, please check the next article
}

BaseSeeding.cs is a base class that exposes a fair amount of overloaded methods to seed the data from the implemented/derived class.

AddSeeding extension method for IServiceCollection

This will be used in the Startup.cs to hook-up the implementation of BaseSeeding as an ISeedData.

public static class SeedingCollectionExtensions
{
    public static IServiceCollection AddSeeding<TSeeding>(this IServiceCollection serviceCollection)
        where TSeeding : ISeedData
    {
        ServiceCollectionDescriptorExtensions.TryAdd(serviceCollection, new ServiceDescriptor(typeof(TSeeding), typeof(TSeeding), ServiceLifetime.Singleton));
        return serviceCollection;
    }
}

The Actual Base Class Implementation

The Action<TEntity> allows you to return the object to do "pre-work" before saving the data.  Typical use case would be reading a parent record and then setting the value or do some other data integrity check.  This article does not cover how to work with Action<T>.  For this example I will illustrate how to use it to set the Modified date property.

Very Important: The enum usage in here is to exclude 0 int ordinals.  Remove the line if you want a 0 in the Id field (might change this later to be overloaded with a bool or other indicator).  The update is by default true, since the code should be the only true source of data.

public abstract class BaseSeeding<TContext> : ISeedData where TContext : DbContext, IDbContext
{
    public IServiceProvider Provider { get; }

    public BaseSeeding(IServiceProvider provider)
    {
        Provider = provider;
    }

    public async Task AddOrUpdateAsync<TEntity>(IEnumerable<TEntity> entities, params Func<TEntity, 
        object>[] propertiesToMatch)
        where TEntity : class
    {
        await InternalAddOrUpdateAsync(entities);
    }

    public async Task AddOrUpdateAsync<TEntity>(IEnumerable<TEntity> entities, Action<TEntity> action, 
        params Func<TEntity, object>[] propertiesToMatch)
        where TEntity : class
    {
        await InternalAddOrUpdateAsync(entities, action, true, propertiesToMatch);
    }

    public async Task AddAsync<TEntity>(IEnumerable<TEntity> entities, params Func<TEntity, 
        object>[] propertiesToMatch)
        where TEntity : class
    {
        await InternalAddOrUpdateAsync(entities, false, propertiesToMatch);
    }

    public async Task AddAsync<TEntity>(IEnumerable<TEntity> entities, Action<TEntity> action, 
        params Func<TEntity, object>[] propertiesToMatch)
        where TEntity : class
    {
        await InternalAddOrUpdateAsync(entities, action, false, propertiesToMatch);
    }

    public void AddOrUpdate<TEntity>(IEnumerable<TEntity> entities, params Func<TEntity, 
        object>[] propertiesToMatch)
        where TEntity : class
    {
        InternalAddOrUpdate(entities, true, propertiesToMatch);
    }

    public void AddOrUpdate<TEntity>(IEnumerable<TEntity> entities, Action<TEntity> action, 
        params Func<TEntity, object>[] propertiesToMatch)
        where TEntity : class
    {
        InternalAddOrUpdate(entities, action, true, propertiesToMatch);
    }

    public void Add<TEntity>(IEnumerable<TEntity> entities, params Func<TEntity, 
        object>[] propertiesToMatch)
        where TEntity : class
    {
        InternalAddOrUpdate(entities, false, propertiesToMatch);
    }

    public void Add<TEntity>(IEnumerable<TEntity> entities, Action<TEntity> action, 
        params Func<TEntity, object>[] propertiesToMatch)
        where TEntity : class
    {
        InternalAddOrUpdate(entities, action, false, propertiesToMatch);
    }

    private void InternalAddOrUpdate<TEntity>(IEnumerable<TEntity> entities, bool update = true,
        params Func<TEntity, object>[] propertiesToMatch)
        where TEntity : class
    {
        InternalAddOrUpdate(entities, null, update, propertiesToMatch);
    }

    private void InternalAddOrUpdate<TEntity>(IEnumerable<TEntity> entities, Action<TEntity> action, 
        bool update = true, params Func<TEntity, object>[] propertiesToMatch)
        where TEntity : class
    {
        using (var serviceScope = Provider.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var context = serviceScope.ServiceProvider.GetService<TContext>();
            InternalEntityState(entities, action, update, propertiesToMatch, context);
            context.SaveChanges();
        }
    }

    private async Task InternalAddOrUpdateAsync<TEntity>(IEnumerable<TEntity> entities, 
        bool update = true, params Func<TEntity, object>[] propertiesToMatch)
        where TEntity : class
    {
        await InternalAddOrUpdateAsync(entities, null, update, propertiesToMatch);
    }

    private async Task InternalAddOrUpdateAsync<TEntity>(IEnumerable<TEntity> entities, 
        Action<TEntity> action, bool update = true, params Func<TEntity, object>[] propertiesToMatch)
        where TEntity : class
    {
        using (var serviceScope = Provider.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var context = serviceScope.ServiceProvider.GetService<TContext>();
            InternalEntityState(entities, action, update, propertiesToMatch, context);
            await context.SaveChangesAsync();
        }
    }

    private static void InternalEntityState<TEntity>(IEnumerable<TEntity> entities, 
        Action<TEntity> action, bool update, Func<TEntity, object>[] propertiesToMatch, TContext context)
        where TEntity : class
    {
        var existing = context.Set<TEntity>().AsNoTracking().ToList(); //ajb: do as no tracking to avoid errors
        foreach (var item in entities)
        {
            var match = FindMatch(existing, item, propertiesToMatch);
            var citem = context.Entry(item);
            if (match != null)
                citem.Property("Id").CurrentValue = context.Entry(match).Property("Id").CurrentValue;

            context.Entry(item).State = update
                ? (match != null ? EntityState.Modified : EntityState.Added)
                : (match != null ? EntityState.Unchanged : EntityState.Added);

            if (item is IModifyObject)
            {
                if (((IModifyObject)item).Modified == DateTime.MinValue)
                    ((IModifyObject)item).Modified = DateTime.Now;
            }

            action?.Invoke(citem.Entity);
        }
    }

    private static TEntity FindMatch<TEntity>(List<TEntity> existing, TEntity item, params Func<TEntity, 
        object>[] propertiesToMatch)
    {
        return existing.FirstOrDefault(g =>
        {
            var r = true;
            foreach (var ptm in propertiesToMatch)
            {
                var rptm = ptm(g);
                if (rptm != null)
                    r &= ptm(g).Equals(ptm(item));
            }
            return r;
        });
    }
        
    public Type[] GetDbEnums()
    {
        var types = Provider.GetService<TContext>().Model.GetEntityTypes().Select(t => t.ClrType);
        var result = types.Where(l => typeof(IDbEnum).GetTypeInfo().IsAssignableFrom(l));
        return result.ToArray();
    }

    public void SeedEnum(params Type[] types)
    {
        types.ToList().ForEach(t => InternalTypedSeedEnum(t));
    }

    public void SeedEnum<TClass>(bool update = true) where TClass : class, IDbEnum 
    {
        InternalSeedEnum<TClass>(update);
    }

    private void InternalSeedEnum<TClass>(bool update = true) where TClass : class, IDbEnum 
    {
        var type = typeof(TClass).GetTypeInfo();
        while (type.BaseType != null)
        {
            type = type.BaseType.GetTypeInfo();
            if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(DbEnum<>))
            {
                var arg = (type.GetGenericArguments()[0]).GetTypeInfo();
                var instance = typeof(TClass).GetTypeInfo().GetConstructor(new Type[] { arg.AsType() });
                var list = new List<TClass>();
                if (arg.IsEnum)
                {
                    var values = Enum.GetValues(arg.AsType());
                    foreach (var val in values)
                    {
                        if ((int)Convert.ChangeType(val, typeof(int)) == 0) //this is to exclude the 0 index
                            continue;
                        var obj = instance.Invoke(new[] { val });
                        list.Add((TClass)obj);
                    }
                    InternalAddOrUpdate<TClass>(list, update, i => i.Id);
                }
            }
        }
    }

    private void InternalTypedSeedEnum(Type type, bool update = true)
    {
        var mi = GetType().GetTypeInfo().BaseType.GetTypeInfo().GetMethod("InternalSeedEnum", 
                                                BindingFlags.NonPublic | BindingFlags.Instance);
        var gm = mi.MakeGenericMethod(type);
        gm.Invoke(this, new object[] { update });
    }

    public abstract Task Seed(string environmentName);

    public virtual void SeedEnums(string environmentName)
    {
        SeedEnum(GetDbEnums());
    }
}

That should be it from a "Framework Component", now the how to use:

1.  Lets build the classes we want to use:

public enum TitleEnum
{
    [Display(Description="Mister")]
    Mr = 1,
    Mrs,
    Miss,
    [Display(Description="Doctor")]
    Dr,
    Ds,
    [Display(Description="Professor")]
    Prof
}

[Table(nameof(Country))]
public class Country : BaseDomainObject<Int16>
{
    public string Description { get; set; }
    public string Alpha2Code { get; set; }
}

[Table(nameof(Customer))]
public class Customer : BaseDomainObject<int>
{
    public TitleEnum Title { get; set; }

    [MaxLength(100)]
    public string Firstname { get; set; }
    [MaxLength(100)]
    public string Surname { get; set; }

    [Required]
    public Int16 CountryId { get; set; }

    [ForeignKey(nameof(CountryId))]
    public Country Country { get; set; }

    public IList<Order> Orders { get; set; }
}

[Table(nameof(Order))]
public class Order : BaseDomainObject<int>
{
    [MaxLength(8)]
    public string Number { get; set; }
    public int CustomerId { get; set; }

    [ForeignKey(nameof(CustomerId))]
    public Customer Customer { get; set; }
}

[Table(nameof(OrderItem))]
public class OrderItem : BaseDomainObject<long>
{
    [Required]
    public int OrderId { get; set; }
    [Required]
    public int ProductId { get; set; }
    [Range(1, 100)]
    public int Qty { get; set; }

    [ForeignKey(nameof(OrderId))]
    public Order Order { get; set; }
    [ForeignKey(nameof(ProductId))]
    public Product Product { get; set; }
}

[Table(nameof(Product))]
public class Product : BaseDomainObject<int>
{
    [MaxLength(100)]
    public string Description { get; set; }
    [Range(1, 9999999)]
    public decimal Cost { get; set; }
}

2.  Implement Title class for DbEnum<TitleEnum>

Take Note:  There is no implicit operation between an Enum and a class and could not be done with a type specification and will need to be implemented per DbEnum<T> derived class

[Table(nameof(Title))]
public class Title : DbEnum<TitleEnum>
{
    public Title() : this(default(TitleEnum)) { }
    public Title(TitleEnum value) : base(value) { }

    public static implicit operator Title(TitleEnum value)
    {
        return new Title(value);
    }

    public static implicit operator TitleEnum(Title value)
    {
        return value.ToEnum();
    }
}

3.  The Database Context, eg SampleContext:

Note that in the first partial class is where I derive from DbContext and then include the IDbContext, since DbContext is partially extracted to IDbContext, we don't have to implement the interface.

If you do not see the second partial class, in the code (if downloaded), just expand the first one.

public partial class SampleContext : DbContext, IDbContext
{
    public SampleContext(DbContextOptions<SampleContext> options) : base(options) { }

    public virtual DbSet<Country> Countries { get; set; }
    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Order> Orders { get; set; }
    public virtual DbSet<OrderItem> OrderItem { get; set; }
    public virtual DbSet<Product> Products { get; set; }
}
public partial class SampleContext
{
    public virtual DbSet<Title> Titles { get; set; }
}

4.  Add-Migration (this is already done in the download)

5.  The ERD

Once you run the application, and build a sql erd, one will notice that since the classes and properties was properly adorned, all the relationships are in place.

The only one that is missing is the Title table, the reason for this is that we wanted the lookup data in the database, and strictly it is not a related object, it is just an enum in code.  The column in customer does not state TitleId, just Title with a numeric value!

6.  Adding Seeding Source Data

public static class Data
{
    private static Dictionary<string, Country> _countries;
    internal static Dictionary<string, Country> Countries
    {
        get
        {
            if (_countries != null)
                return _countries;
            var list = new List<Country>
            {
                new Country{Description = "British Indian Ocean Territory",Alpha2Code = "IO"},
                new Country{Description = "British Virgin Islands",Alpha2Code = "VG"},
                new Country{Description = "Burundi",Alpha2Code = "BI"},
                new Country{Description = "Cambodia",Alpha2Code = "KH"},
                new Country{Description = "Cameroon",Alpha2Code = "CM"},
                new Country{Description = "Canada",Alpha2Code = "CA"},
                new Country{Description = "Central African Republic",Alpha2Code = "CF"},
                new Country{Description = "Chad",Alpha2Code = "TD"},
                new Country{Description = "Chile",Alpha2Code = "CL"},
                new Country{Description = "China",Alpha2Code = "CN"},
                new Country{Description = "Christmas Island",Alpha2Code = "CX"}
            };
            return _countries = list.ToDictionary(l => l.Alpha2Code);
        }
    }

    private static Dictionary<string, Customer> _customers;
    internal static Dictionary<string, Customer> Customers
    {
        get
        {
            if (_customers != null)
                return _customers;
            var list = new List<Customer>
            {
                new Customer{Firstname="Joe", Surname="Blogs", Country = Countries["CA"]},
                new Customer{Firstname="Mary", Surname="Summer", Country = Countries["TD"]},
                new Customer{Firstname="Chris", Surname="Exhausted", Country = Countries["VG"]}
            };
            //If we did countries first, then the id will be populated and we can use it for assignment
            list.ForEach(l => l.CountryId = l.Country.Id);
            return _customers = list.ToDictionary(l => $"{l.Firstname} {l.Surname}".Trim());
        }
    }

    private static Dictionary<string, Product> _products;
    internal static Dictionary<string, Product> Products
    {
        get
        {
            if (_products != null)
                return _products;
            var list = new List<Product>
            {
                new Product{Description="Socks", Cost=5m},
                new Product{Description="Shirt", Cost=10m},
                new Product{Description="Pants", Cost=20m}
            };
            return _products = list.ToDictionary(l => l.Description);
        }
    }

    private static Dictionary<string, Order> _orders;
    internal static Dictionary<string, Order> Orders
    {
        get
        {
            if (_orders != null)
                return _orders;
            var counter = 0;
            counter++;
            var list = new List<Order>
            {
                new Order
                {
                    Customer = Customers["Joe Blogs"],
                    Number = $"{new string('0',8) + counter++}".GetLast(8),
                },
                new Order
                {
                    Customer = Customers["Chris Exhausted"],
                    Number = $"{new string('0',8) + counter++}".GetLast(8),
                },
                new Order
                {
                    Customer = Customers["Mary Summer"],
                    Number = $"{new string('0',8) + counter++}".GetLast(8),
                }
            };
            return _orders = list.ToDictionary(l => l.Number);
        }
    }

    internal static List<OrderItem> GetSomeOrderItems()
    {
        var list = new List<OrderItem>();
        var counter = 0;
        counter++;
        list.Add(new OrderItem { Product = Products["Socks"], Qty=1, Order = Orders[$"{new string('0', 8) + counter}".GetLast(8)] });
        list.Add(new OrderItem { Product = Products["Shirt"], Qty=2, Order = Orders[$"{new string('0', 8) + counter}".GetLast(8)] });
        counter++;
        list.Add(new OrderItem { Product = Products["Socks"], Qty = 6, Order = Orders[$"{new string('0', 8) + counter}".GetLast(8)] });
        list.Add(new OrderItem { Product = Products["Shirt"], Qty = 4, Order = Orders[$"{new string('0', 8) + counter}".GetLast(8)] });
        list.Add(new OrderItem { Product = Products["Pants"], Qty = 2, Order = Orders[$"{new string('0', 8) + counter}".GetLast(8)] });
        counter++;
        list.Add(new OrderItem { Product = Products["Shirt"], Qty = 9, Order = Orders[$"{new string('0', 8) + counter}".GetLast(8)] });
        list.ForEach(l =>
        {
            l.ProductId = l.Product.Id;
            l.OrderId = l.Order.Id;
        });
        return list;
    }
}

7.  Implementation of BaseSeeding

The tables are populated based on importance and if not in allowed environments, then the rest won't be executed.
Orders are done first and then the OrderItems, this is obviously still WIP, but it allows enough room to get the work done.

public class SeedSampleContext : BaseSeeding<SampleContext>
{
    private string[] _allowed = new[] { "Development", "Staging" };

    public SeedSampleContext(IServiceProvider provider) : base(provider) { }

    public override Task Seed(string environmentName)
    {
        //see that on the second parameter we are updating the modified date
        AddOrUpdate(Data.Countries.Select(c => c.Value),
            action: c => c.Modified = DateTime.Now, propertiesToMatch: c => c.Alpha2Code);
        //one property only
        Add(Data.Products.Select(p => p.Value), p => p.Description);

        if (!_allowed.Contains(environmentName))
            return null;
        //two properties will be checked for uniqueness
        Add(Data.Customers.Select(c => c.Value), c => c.Firstname, c => c.Surname);
        Add(Data.Orders.Select(o => o.Value), o => o.Number);
        Add(Data.GetSomeOrderItems(), oi => oi.ProductId, oi => oi.OrderId);
        return null;
    }
}

8.  StartUp.cs Changes

This is a method that I use a lot to wire up databases.  I have worked on a few applications where I had to connect to multiple database and do a modulare design.

private void ConfigureDatabase<TContext>(IServiceCollection services, 
                Action<DbContextOptionsBuilder> action) where TContext : DbContext, IDbContext
{
    services.AddDbContext<TContext>(action);
    services.AddScoped<IDbContext, TContext>(provider => provider.GetService<TContext>());
}

Now in ConfigureServices(IServiceCollection services) add the following lines:
The first will be for the database, the second is to actually add the Seeding implementation as per 7

ConfigureDatabase<SampleContext>(services, 
    o => o.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"),
    b => b.MigrationsAssembly("pare.Sample")));

services.AddSeeding<SeedSampleContext>()
    .AddSingleton<ISeedData, SeedSampleContext>(provider => provider.GetService<SeedSampleContext>());

In Configure(app,env,loggerfactory) we are going to add:

using (var serviceScope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope())
{
    var contexts = serviceScope.ServiceProvider.GetServices<IDbContext>();
    foreach (var context in contexts)
        ((DbContext)context).Database.Migrate();

    var seeding = serviceScope.ServiceProvider.GetServices<ISeedData>();
    foreach (var item in seeding)
    {
        item.SeedEnums(env.EnvironmentName);
        item.Seed(env.EnvironmentName);
    }
}

The top part will actually do a migration for all implementations of IDbContext and since we know we implemented DbContext as well, we can safely cast to it.
_{BTW: GetServices<DbContext> used to work in the pre-release for dotnet core, but then broke, the interface usage just solve that bug to.}

The second part is getting the ISeedData and then seeding both enums and execute the Seed method that was implemented in 7.  By passing the environment name, we can conditionally seed data for development vs production.

DbEnum can be found @ https://www.codeproject.com/Reference/1186336/DbEnum
EnumHelper can be found @ https://www.codeproject.com/Reference/1186338/EnumHelper-for-DbEnumc

Wrap-up

The migrations are done in the application space, whether it is WebApi, Console or WebApp.  The main reason is to keep the assemblies database independent and the application actually works with a specified database on the configuration.  In this sample it is in one WebAPI, but each folder could have been an assembly or even multiples, if you go multiples you can go for different schemas, databases etc and keep the seeding per "module" (if I get time I would definately write something about it)

You might ask why not do it with migration.  The answer is why should you, it is not wrong, but this way we ensure the data gets loaded and checked everytime.  The Add operation will leave data it matched based on the check you defined in-tact and only set the Id value in the object for the next related object to use, if it did not find a match it will seed the database with it.  The AddOrUpdate will do what the Add operation does apart from if it does find a match it will ignore any changes by any means external to the seeding, eg DBA, application, etc and set it back to the original seed state.

It is one source of data to Seed, not split accross multiple migrations, it will become messy to find and fix if you have a much larger team.  Leave migrations for structural changes, that is where EF code-first is great at.  Remember SOLID principals, let migrations do what is does best, do something else for seeding ***smiling***.

One would also say that this is going to run everytime on startup.  Well yes, you are 100% correct, but then is it a bad thing, it is only at startup of the first Application Pool, service, etc and you will be ensured that the database is always what you expect it to be and for long running apps, the cost of running it from time-to-time should be so small, that is should be a no-brainer.  It actually runs each time when we spool the application up on dev boxes  fairly frequantly, I would say I average 20-30 times a day (if not more) and it is not that bad.

Any comments and improvements are welcome, if you want there is a master on github.

https://github.com/Grompot77/pare.EntityFrameworkCore.Seeding