// Entity Framework Extensions

// Doc: https://entityframework-extensions.net/bulk-merge

​

// @nuget: Microsoft.EntityFrameworkCore.SqlServer

// @nuget: Z.EntityFramework.Extensions.EFCore

​

using System.Collections.Generic;

using System.Linq;

using Microsoft.Data.SqlClient;

using Microsoft.EntityFrameworkCore;

​

public class Program

{

    public static void Main()

    {

        using (var context = new EntityContext())

        {

            context.Database.EnsureCreated();

        }

        // SEED entity 2

        var entity2 = new Entity2() { ExternalID = 13, ColumnInt = 13 };

        using (var context = new EntityContext())

        {

            context.SingleInsert(entity2);

        }

        // 

        using (var context = new EntityContext())

        {

            var entity1 = new Entity1() { ColumnInt = 99 };

            // we currently assume that we got the "ExternalID = 13" from entity 2 instead of the real FK (which should be = 1)

            entity1.Entity2ID = 13; // look like a mistake but that's by purpose... it will become 1 with the "FormulaInsert"

            context.BulkInsert(new List<Entity1>() { entity1 }, options => {

                options.PostConfiguration = config => {

                    var column = config.ColumnMappings.Single(x => x.SourceName == nameof(Entity1.Entity2ID));

                    column.FormulaInsert = "(SELECT [Entity2ID] FROM [Entity2s] WHERE [ExternalID] = StagingTable.[Entity2ID])";

                    column.FormulaUpdate = column.FormulaInsert;

                };

            });

            FiddleHelper.WriteTable(context.Entity1s.AsNoTracking());

        }

    }

​

    public class EntityContext : DbContext

    {

        public EntityContext() 

        {

        }

​

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

        {

            optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));

​

            base.OnConfiguring(optionsBuilder);

        }

        public DbSet<Entity1> Entity1s { get; set; }

        public DbSet<Entity2> Entity2s { get; set; }

    }

    public class Entity1

    {

        public int Entity1ID { get; set; }

        public int Entity2ID { get; set; }

        public int ColumnInt { get; set; }      

    }

    public class Entity2

    {

        public int Entity2ID { get; set; }

        public int ExternalID { get; set; }

        public int ColumnInt { get; set; }

    }

}