// Entity Framework Extensions

// Doc: https://entityframework-extensions.net/delete-matched-and-formula

​

// @nuget: Microsoft.EntityFrameworkCore.SqlServer

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

​

using System;

using System.Linq;

using Microsoft.EntityFrameworkCore;

using System.Collections.Generic;

using Microsoft.Data.SqlClient;

using Z.BulkOperations;

​

public class Program

{

    public static void Main()

    {

        // SCENARIO: https://entityframework-extensions.net/delete-matched-and-formula

        // CREATE & SEED

        using (var context = new EntityContext())

        {

            context.Database.EnsureCreated();

            var customers = new List<Customer>();

            // "Jonathan" version = 4, other customers version = 2

            customers.Add(new Customer() { CustomerID = 1, Name = "Jonathan", Email = "Email_Jonathan", Note = "Note_Jonathan", Version = 4 });

            customers.Add(new Customer() { CustomerID = 2, Name = "Mikael", Email = "Email_Mikael", Note = "Note_Mikael", Version = 2 });

            customers.Add(new Customer() { CustomerID = 3, Name = "Sara", Email = "Email_Sara", Note = "Note_Sara", Version = 2 });

            context.BulkInsert(customers, options => { options.InsertKeepIdentity = true; });

​

            FiddleHelper.WriteTable("1 - Before BulkDelete", context.Customers.ToList());

        }

​

        using (var context = new EntityContext())

        {

            var customers = new List<Customer>();

            // DATA coming from an importation

            customers.Add(new Customer() { CustomerID = 1, Version = 3 });

            customers.Add(new Customer() { CustomerID = 2, Version = 3 });

            customers.Add(new Customer() { CustomerID = 3, Version = 3 });

            context.BulkDelete(customers, options => 

            {

                // REQUIRED because by default, only the "CustomerID" is part of the "StagingTable"

                options.ColumnStagingTableFormulaExpression = x => x.Version;

                // ON DELETE, remove customer where the version is equal or lower than the one coming from the importation

                // StagingTable = source

                // DestinationTable = database/destination

                options.DeleteMatchedAndFormula = "StagingTable.Version > DestinationTable.Version";

            });

            FiddleHelper.WriteTable("2 - After BulkDelete", context.Customers.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<Customer> Customers { get; set; }

    }

    public class Customer

    {

        public int CustomerID { get; set; }

        public string Name { get; set; }

        public string Email { get; set; }

        public string Note { get; set; }

        public int Version { get; set; }

    }

}