Seeding data in Entity Framework Core from Visual Studio

Insert sample data from Visual Studio IDE with EF core

Unlike Entity Framework 6, EF Core and whole .NET Core relies on dependency injection. For example, configuration is expected to be injected from the calling assembly instead of being available at any time.

This is one of the reasons Migrations in Code First approach do not work out of the box for EF Core. If you try to generate migrations files from Visual Studio NuGet console you will get an error, so you need to write some code in your project to enable this.

If you search for Migrations and Data Seeding in EF Core, you will find most of the articles explaining how to invoke these two from the executing assembly. It makes sense to have this in some cases, but you definitely do not want this execute on your production environment. For that reason I prefer to execute both migrations and data seeding from Visual Studio on my development environment.

To demonstrate how to enable migrations and data seeding from Visual Studio IDE, I have a small project which can be downloaded from this article.

First thing we need to do in a sample project are entities. I used simple model of Products and Orders which have relation many to many. Since EF Core does not directly support many to many relations out of the box I had to introduce third entity called OrderProduct.

Product entity

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Samples.EfCore.Entities
{
    public class Product
    {
        [Key, Column(Order = 0)]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid Id { get; set; }

        [Required]
        public String Name { get; set; }

        public String Descrition { get; set; }

        public ICollection<OrderProduct> OrderProducts { get; private set; }
    }


    internal class ProductsConfiguration : IEntityTypeConfiguration<Product>
    {
        public void Configure(EntityTypeBuilder<Product> builder)
        {
            builder.HasKey(t => t.Id);
        }
    }
}

    

Order entity

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Samples.EfCore.Entities
{
    public class Order
    {
        [Key, Column(Order = 0)]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid Id { get; set; }

        [Required]
        public DateTime DateCreated { get; set; }

        public String Note { get; set; }

        public Boolean Delivered { get; set; }

        public ICollection<OrderProduct> OrderProducts { get; private set; }

    }


    internal class OrdersConfiguration : IEntityTypeConfiguration<Order>
    {
        public void Configure(EntityTypeBuilder<Order> builder)
        {
            builder.HasKey(t => t.Id);
            builder.Property(t => t.DateCreated).HasDefaultValueSql("GETDATE()");
        }
    }

}

    

ProductOrder entity

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

namespace Samples.EfCore.Entities
{
    public class OrderProduct
    {
        public Guid OrderId { get; set; }
        public Order Order { get; set; }

        public Guid ProductId { get; set; }
        public Product Product { get; set; }

        public int Quantity { get; set; }
    }

    internal class OrderProductsConfiguration : IEntityTypeConfiguration<OrderProduct>
    {
        public void Configure(EntityTypeBuilder<OrderProduct> builder)
        {
            builder.HasKey(t => new { t.ProductId, t.OrderId });

            builder.HasOne(t => t.Product).WithMany(t => t.OrderProducts).HasForeignKey(t => t.ProductId);
            builder.HasOne(t => t.Order).WithMany(t => t.OrderProducts).HasForeignKey(t => t.OrderId);

            builder.Property(t => t.Quantity).HasDefaultValue(1);
        }

    }
}

    

Although it might not be the best practice, I tend to keep entity configuration classes as part of entity class files since they are directly related to the specific entity. These configurations are used to define relations, keys and indexes and are referenced from the data context. These configuration classes are only referenced by data context, so they can be kept internal as they are not used anywhere outside of the data library project.

Next thing is data context class which will reference all entities and entity configurations

using Microsoft.EntityFrameworkCore;
using Samples.EfCore.Entities;

namespace Samples.EfCore
{
    public class StoreDbContext : DbContext
    {
        public StoreDbContext(DbContextOptions options) : base(options)
        {

        }

        protected StoreDbContext():base()
        {

        }

        protected override void OnModelCreating(ModelBuilder builder)
        {

            base.OnModelCreating(builder);

            builder.ApplyConfiguration(new OrdersConfiguration());
            builder.ApplyConfiguration(new ProductsConfiguration());
            builder.ApplyConfiguration(new OrderProductsConfiguration());

        }

        public DbSet<Product> Products { get; set; }
        public DbSet<Order> Orders { get; set; }
        public DbSet<OrderProduct> OrderProducts { get; set; }

    }
}

    

Migrations

Before you reach migrations you need to install NuGet package Microsoft.EntityFrameworkCore.Tools. It will enable you to perform migrations from Visual Studio IDE. 

Install-Package Microsoft.EntityFrameworkCore.Tools -ProjectName Samples.EfCore
    

Once you have EF Core Tool NuGet package installed, you can try to invoke migrations from NuGet package console. Unfortunately it will not work because migrations do not know how to generate data context because these is not injection from calling assembly involved. 

That is why you need to implement IDesignTimeDbContextFactory interface.

using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System.IO;
using Samples.EfCore.Migrations;

namespace Samples.EfCore
{
    public class StoreDbContextFactory : IDesignTimeDbContextFactory<StoreDbContext>
    {
        public StoreDbContext CreateDbContext(string[] args)
        {
            var dbContext = new StoreDbContext(new DbContextOptionsBuilder<StoreDbContext>().UseSqlServer(
               new ConfigurationBuilder()
                   .AddJsonFile(Path.Combine(Directory.GetCurrentDirectory(), $"appsettings.json"))
                   .Build()
                   .GetConnectionString("StoreDbConnection")
               ).Options);

            return dbContext;
        }


    }
}

    

Once you add your IDesignTimeDbContextFactory interface implementation, you van switch to NuGet package manager console and execute migrations command

Add-Migration InitialCreate -Project Samples.EfCore  -Context StoreDbContext
    

This will create Migrations folder in the project and generate migration classes. You changes will still not be applied to the database untill you execute Update-Database command from package manager console

Update-Database -Project Samples.EfCore -Context StoreDbContext
    

After command is executed you can connect to database with SQL Server Management Studio and check for database structure. It should have the follwoing structure. 

Efcoremigration 

Data seeding

Now we can add some test data to the existing database. I created a StoreDbDataSeeder class which takes StoreDbContext instance as a constructor parameter.

using Samples.EfCore.Entities;
using System;
using System.Collections.Generic;

namespace Samples.EfCore.Migrations
{
    public class StoreDbDataSeeder
    {
        private StoreDbContext context;
        public StoreDbDataSeeder(StoreDbContext context)
        {
            this.context = context;
        }

        public void Seed()
        {
            var banana = new Product() { Id = Guid.NewGuid(), Name = "Banana", Descrition = "Banana fruit" };
            var kiwi = new Product() { Id = Guid.NewGuid(), Name = "Kiwi", Descrition = "Kiwi fruit" };
            var pinaple = new Product() { Id = Guid.NewGuid(), Name = "Pinaple", Descrition = "Pinaple fruit" };
            var orange = new Product() { Id = Guid.NewGuid(), Name = "Orange", Descrition = "Orange fruit" };

            context.Products.AddRange(banana, kiwi, pinaple, orange);

            var order1 = new Order() { Id = Guid.NewGuid(), DateCreated = DateTime.Now.AddDays(-6), Delivered = true, Note = "Deliver before 15:00" };
            var order2 = new Order() { Id = Guid.NewGuid(), DateCreated = DateTime.Now, Delivered = false };

            context.Orders.AddRange(order1, order2);

            var order1products = new List<OrderProduct>(){
                new OrderProduct() { OrderId = order1.Id, ProductId = banana.Id, Quantity = 4 },
                new OrderProduct() { OrderId = order1.Id, ProductId = kiwi.Id, Quantity = 12 },
                new OrderProduct() { OrderId = order1.Id, ProductId = orange.Id, Quantity = 9 },
            };

            var order2products = new List<OrderProduct>(){
                new OrderProduct() { OrderId = order2.Id, ProductId = pinaple.Id, Quantity = 2 },
                new OrderProduct() { OrderId = order2.Id, ProductId = orange.Id, Quantity = 10 },
                new OrderProduct() { OrderId = order2.Id, ProductId = kiwi.Id, Quantity = 13 },
                new OrderProduct() { OrderId = order2.Id, ProductId = banana.Id, Quantity = 8 },
            };

            order1products.AddRange(order2products);

            context.OrderProducts.AddRange(order1products.ToArray());

            context.SaveChanges();
        }
    }
}

    

I deleted the previous generated database so I can have the full cycle executed from clean environment and have my development environment ready with one go.

To apply the data seeding we need to add StoreDbDataSeeder.Seed() method from our DbContext factory

using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System.IO;
using Samples.EfCore.Migrations;

namespace Samples.EfCore
{
    public class StoreDbContextFactory : IDesignTimeDbContextFactory<StoreDbContext>
    {
        public StoreDbContext CreateDbContext(string[] args)
        {
            var dbContext = new StoreDbContext(new DbContextOptionsBuilder<StoreDbContext>().UseSqlServer(
               new ConfigurationBuilder()
                   .AddJsonFile(Path.Combine(Directory.GetCurrentDirectory(), $"appsettings.json"))
                   .Build()
                   .GetConnectionString("StoreDbConnection")
               ).Options);

            new StoreDbDataSeeder(dbContext).Seed();

            return dbContext;
        }
    }
}

    

Now we need to call Database-Update from Package Manager Console

Update-Database -Project Samples.EfCore -Context StoreDbContext
    

At first, you will get an error after trying to execute PowerShell command from above. This is because seeding will be executed before the database is actually generated on the server. We need to ensure database is already migrated before the seeding is invoked

    public class StoreDbContextFactory : IDesignTimeDbContextFactory<StoreDbContext>
    {
        public StoreDbContext CreateDbContext(string[] args)
        {
            var dbContext = new StoreDbContext(new DbContextOptionsBuilder<StoreDbContext>().UseSqlServer(
               new ConfigurationBuilder()
                   .AddJsonFile(Path.Combine(Directory.GetCurrentDirectory(), $"appsettings.json"))
                   .Build()
                   .GetConnectionString("StoreDbConnection")
               ).Options);

            dbContext.Database.Migrate();
            new StoreDbDataSeeder(dbContext).Seed();

            return dbContext;
        }
    }
    

Now after executing the Database-Update from Package Manager Console, we will have both Database and sample data on our local SQL Server instance.

I wrote a simple query to check the data and structure. 

select 
	o.Id,
	o.DateCreated,
	o.Delivered,
	p.Name,
	op.Quantity
from Orders(nolock) o
join OrderProducts(nolock) op on o.Id=op.OrderId
join Products(nolock) p on op.ProductId=p.Id
order by o.Id
    

And the output is the following 

Id                                   DateCreated                 Delivered Name                                 Quantity
------------------------------------ --------------------------- --------- ------------------------------------ -----------
4267EE04-3A90-4DE6-BFB4-064B530C1147 2018-05-12 22:51:39.7134164 1         Banana                               4
4267EE04-3A90-4DE6-BFB4-064B530C1147 2018-05-12 22:51:39.7134164 1         Kiwi                                 12
4267EE04-3A90-4DE6-BFB4-064B530C1147 2018-05-12 22:51:39.7134164 1         Orange                               9
C4D4F288-5E87-4410-B0A2-ED8BF841D3FC 2018-05-18 22:51:39.7149148 0         Banana                               8
C4D4F288-5E87-4410-B0A2-ED8BF841D3FC 2018-05-18 22:51:39.7149148 0         Pinaple                              2
C4D4F288-5E87-4410-B0A2-ED8BF841D3FC 2018-05-18 22:51:39.7149148 0         Kiwi                                 13
C4D4F288-5E87-4410-B0A2-ED8BF841D3FC 2018-05-18 22:51:39.7149148 0         Orange                               10

(7 rows affected)

This way we enabled both Migrations and data seeding for our EF Core project directly from Visual Studio IDE.

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article