// Dapper Plus

// Doc: https://dapper-plus.net/bulk-synchronize

// Doc: https://dapper-plus.net/options

​

// @nuget: Microsoft.Data.SqlClient

// @nuget: Dapper

// @nuget: Dapper.Contrib

// @nuget: Z.Dapper.Plus

​

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Linq;

using Microsoft.Data.SqlClient;

using Dapper;

using Z.Dapper.Plus;

//using Dapper.Contrib.Extensions;

​

public class Program

{   

    public static void Main()

    {

        // Map your entity

​

        DapperPlusManager.Entity<Product>()

            .Table("Product")

            .Identity(x => x.ProductID)

            .UseBulkOptions(options => {

                            //options.InsertKeepIdentity = true;

                            //options.ColumnSynchronizeDeleteKeySubsetExpression = x => new { x.StoreID, x.CountryID, x.LanguageID };

                            options.SynchronizeMatchedAndOneNotConditionExpression = x => new { x.Name };

                            //options.IgnoreOnSynchronizeInsertExpression = x => new { x.LastUpdatedDate };

                            options.IgnoreOnSynchronizeUpdateExpression = x => new { x.CreatedDate };

            });

        var products = new List<Product>();

        for (var country = 1; country < 3; country++)

        {

            for (var store = 1; store < 3; store++)

            {

                for (var lang = 1; lang < 3; lang++)

                {

                    products.Add(new Product() { ArbitraryOldID = Guid.NewGuid(), ProductKey = 1, Name = "Dapper Plus", StoreID = store, CountryID = country, LanguageID = lang, Description = $"This is a description in Language {lang}", LastUpdatedDate = DateTime.Now - System.TimeSpan.FromDays(1), CreatedDate = DateTime.Now - System.TimeSpan.FromDays(2) });

                    products.Add(new Product() { ArbitraryOldID = Guid.NewGuid(), ProductKey = 2, Name = "C# Eval Expression", StoreID = store, CountryID = country, LanguageID = lang, Description = $"This is a description in Language {lang}", LastUpdatedDate = DateTime.Now - System.TimeSpan.FromDays(1), CreatedDate = DateTime.Now - System.TimeSpan.FromDays(2) });

                    products.Add(new Product() { ArbitraryOldID = Guid.NewGuid(), ProductKey = 3, Name = "Entity Framework Extensions", StoreID = store, CountryID = country, LanguageID = lang, Description = $"This is a description in Language {lang}", LastUpdatedDate = DateTime.Now - System.TimeSpan.FromDays(1), CreatedDate = DateTime.Now - System.TimeSpan.FromDays(2) });

                }

            }

        }

        var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer());

        connection.CreateTable<Product>();

        // Seed

        connection.BulkInsert(products);

        FiddleHelper.WriteTable($"1 - Product (Before) - {connection.QuerySingle("SELECT COUNT(*) AS Products FROM Product").Products}", connection.Query<Product>("SELECT * FROM Product"));

        // (Keep only StoreID = 1) Update first product, Remove Second Product

        products = products.Where(x => x.StoreID == 1).ToList();

        products.ForEach(x => x.LastUpdatedDate = DateTime.Now);

        products.ElementAt(0).Name += "(Updated)";

        products.RemoveAt(9);

        products.RemoveAt(10);

        products.ElementAt(1).ArbitraryOldID = Guid.NewGuid();

        products.ElementAt(2).CreatedDate = DateTime.Now;

        connection.BulkSynchronize(products);

        // Query data with Dapper

        FiddleHelper.WriteTable("2 - Product (After)", connection.Query<Product>("SELECT * FROM Product"));

        products = products.Take(2).ToList();

        products[1].Name = "New Name here";

        products.Add(new Product() { ArbitraryOldID = Guid.NewGuid(), ProductKey = 1, Name = "Dapper Plus", StoreID = 99, CountryID = 99, LanguageID = 99, Description = $"This is a description in Language {99}", LastUpdatedDate = DateTime.Now - System.TimeSpan.FromDays(1), CreatedDate = DateTime.Now - System.TimeSpan.FromDays(2) });

        connection.BulkSynchronize(products);

        // Query data with Dapper

        FiddleHelper.WriteTable("3 - Product (Sync DEL)", connection.Query<Product>("SELECT * FROM Product"));

    }

    [Table("Product")]

    public class Product

    {

        public Guid ArbitraryOldID { get; set; }

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public int ProductID { get; set; }

        public int ProductKey { get; set; }

        public string Name { get; set; }

        public int StoreID { get; set; }

        public int CountryID { get; set; }

        public int LanguageID { get; set; }

        public string Description { get; set; }

        public DateTime LastUpdatedDate { get; set; }

        public DateTime CreatedDate { get; set; }

    }

}