// Entity Framework Extensions

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

​

// @nuget: Microsoft.EntityFrameworkCore.SqlServer

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

​

using System;

using System.Collections.Generic;

using System.Linq;

using Microsoft.EntityFrameworkCore;

using System.Collections.Generic;

using Microsoft.Data.SqlClient;

public class Program

{

    public static void Main()

    {

        using (var context = new EntityContext())

        {

            context.Database.EnsureCreated();

        }

        SeedDatabase();

        var importPickDatas = new List<PickData>();

        importPickDatas.Add(new PickData() { FacilityID = 1, CustomerID = 1, PickupDate = DateTime.Today, OrderID = 1, ProductNumber = "ProductNumber_1", Location = "Location_1", ColumnToModify = "UpdatedColumn_1" });

        importPickDatas.Add(new PickData() { FacilityID = 2, CustomerID = 2, PickupDate = DateTime.Today, OrderID = 2, ProductNumber = "ProductNumber_2", Location = "Location_2", ColumnToModify = "UpdatedColumn_2"  });

        importPickDatas.Add(new PickData() { FacilityID = 3, CustomerID = 3, PickupDate = DateTime.Today, OrderID = 3, ProductNumber = "ProductNumber_3", Location = "Location_3", ColumnToModify = "AddedColumn_3"  });

        using (var context = new EntityContext())

        {

            // 1. Retrieve all existing data using the WhereBulkContains methods.

            var existingPickDatas = context.PickDatas.WhereBulkContains(importPickDatas, x => new

                {

                    x.FacilityID,

                    x.CustomerID,

                    x.PickupDate,

                    x.OrderID,

                    x.ProductNumber,

                    x.Location

                }  

            ).AsNoTracking().ToList();

            FiddleHelper.WriteTable("2 - PickData found with WhereBulkContains:", existingPickDatas);

            // 2. Create a dictionary using a combination of columns to create a custom key.

            var existingPickDataDict = existingPickDatas.ToDictionary(x => GetCustomPickDataKey(x));

            // 3. Find all entities to be inserted using the dictionary.

            var toInsertPickDatas = importPickDatas.Where(x => !existingPickDataDict.ContainsKey(GetCustomPickDataKey(x))).ToList();

            // 4. Find all entities to be updated using the dictionary (or excluding inserted entities from the original list).

            var toUpdatePickDatas = importPickDatas.Where(x => existingPickDataDict.ContainsKey(GetCustomPickDataKey(x))).ToList();

            // 5. Insert data with BulkInsert.

            {

                 context.BulkInsert(toInsertPickDatas);

            }

            // UPDATE existing PickData

            {

                // Copy the `id` retrieved previously for the entity to update.

                toUpdatePickDatas.ForEach(x => x.PickDataID = existingPickDataDict[GetCustomPickDataKey(x)].PickDataID);

                // Update data with BulkUpdate using the natural primary key instead of a custom key.

                context.BulkUpdate(toUpdatePickDatas);

            }

            FiddleHelper.WriteTable("3 - PickDatas After:", context.PickDatas.AsNoTracking().ToList());

        }   

    }

    public static void SeedDatabase() 

    {

        List<PickData> list = new List<PickData>();

        list.Add(new PickData() { FacilityID = 1, CustomerID = 1, PickupDate = DateTime.Today, OrderID = 1, ProductNumber = "ProductNumber_1", Location = "Location_1", ColumnToModify = "ColumnToUpdate_1" });

        list.Add(new PickData() { FacilityID = 2, CustomerID = 2, PickupDate = DateTime.Today, OrderID = 2, ProductNumber = "ProductNumber_2", Location = "Location_2", ColumnToModify = "ColumnToUpdate_2"  });

        using (var context = new EntityContext())

        {

            context.BulkInsert(list);

            FiddleHelper.WriteTable("1 - PickDatas Before:", context.PickDatas.AsNoTracking().ToList());

        }

    }

    public class EntityContext : DbContext

    {

        public EntityContext() 

        {

        }

​

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

        {

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

​

            base.OnConfiguring(optionsBuilder);

        }

        public DbSet<PickData> PickDatas { get; set; }

    }

    public static string GetCustomPickDataKey(PickData pick)

    {

        return $"{pick.FacilityID};{pick.CustomerID};{pick.PickupDate};{pick.OrderID};{pick.ProductNumber};{pick.Location}";

    }

    public class PickData 

    {

        public int PickDataID { get; set; }

        public int FacilityID { get; set; }

        public int CustomerID { get; set; }

        public DateTime PickupDate { get; set; }

        public int OrderID { get; set; }

        public string ProductNumber { get; set; }

        public string Location { get; set; }

        public string ColumnToModify { get; set; }

    }

}