using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using Microsoft.Data.SqlClient;
public static void Main()
using (var context = new EntityContext())
context.Database.EnsureCreated();
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())
var existingPickDatas = context.PickDatas.WhereBulkContains(importPickDatas, x => new
).AsNoTracking().ToList();
FiddleHelper.WriteTable("2 - PickData found with WhereBulkContains:", existingPickDatas);
var existingPickDataDict = existingPickDatas.ToDictionary(x => GetCustomPickDataKey(x));
var toInsertPickDatas = importPickDatas.Where(x => !existingPickDataDict.ContainsKey(GetCustomPickDataKey(x))).ToList();
var toUpdatePickDatas = importPickDatas.Where(x => existingPickDataDict.ContainsKey(GetCustomPickDataKey(x))).ToList();
context.BulkInsert(toInsertPickDatas);
toUpdatePickDatas.ForEach(x => x.PickDataID = existingPickDataDict[GetCustomPickDataKey(x)].PickDataID);
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
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 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; }