// Entity Framework Extensions

// Doc: https://entityframework-extensions.net/coalesce

​

// @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/coalesce

        // CREATE & SEED

        using (var context = new EntityContext())

        {

            context.Database.EnsureCreated();

            var customers = new List<Customer>();

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

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

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

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

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

        }

​

        using (var context = new EntityContext())

        {

            var customers = new List<Customer>();

            // DATA coming from an importation

            // "Jonathan" email is null. The value "Email_Jonathan" will be kept in the database.

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

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

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

            context.BulkMerge(customers, options => 

            {

                // ON UPDATE, modify a column value only if the source value is not null 

                options.OnMergeUpdateUseCoalesce = true;

            });

            FiddleHelper.WriteTable("2 - After BulkMerge", 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; }

    }

}