// Dapper Plus

// Doc: https://dapper-tutorial.net/bulk-merge

​

// @nuget: Dapper -Version 1.60.6

// @nuget: Z.Dapper.Plus

// @nuget: System.Data.SqlClient

​

using Dapper;

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using Z.Dapper.Plus;

using System.Linq;

public class Program

{       

    public class Supplier 

    {

        public int SupplierID {get;set;}

        public string SupplierName {get;set;}

        public List<Product> Products {get;set;}

    }

    public class Product 

    {

        public int ProductID {get;set;}

        public string ProductName {get;set;}

        public string Unit {get;set;}

        public int SupplierID {get;set;}

    }

    public static void Main()

    {

        List<Supplier> suppliers = new List<Supplier>();

        using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))

        {           

            var supplierDictionary = new Dictionary<int, Supplier>();

            suppliers = connection.Query<Supplier, Product, Supplier>("Select A.SupplierID, A.SupplierName, B.ProductID, B.ProductName, B.Unit FROM Suppliers as A inner join Products as B on B.SupplierID =A.SupplierID where A.supplierID = 1",

            (supplier, product) => 

            {  

                Supplier supplierEntry;

                    if (!supplierDictionary.TryGetValue(supplier.SupplierID, out supplierEntry))

                    {

                        supplierEntry = supplier;

                        supplierEntry.Products = new List<Product>();

                        supplierDictionary.Add(supplier.SupplierID, supplierEntry);

                    }

​

                    supplierEntry.Products.Add(product);

                    return supplierEntry;

                }, splitOn: "ProductID").Distinct().ToList();

            FiddleHelper.WriteTable(suppliers);

            FiddleHelper.WriteTable(suppliers.FirstOrDefault().Products);

        }

        suppliers.ForEach(x => 

        {

            x.SupplierName = "ExampleSupplierBulkMerge";

            x.Products.ForEach(y => y.ProductName = "ExampleProductBulkMerge");

        });

        suppliers.Add(new Supplier() { SupplierName = "ExampleSupplierBulkMerge", Products = new List<Product>()

        { new Product() { ProductName = "ExampleProductBulkMerge", Unit = "1"},  new Product() { ProductName = "ExampleProductBulkMerge" , Unit = "2"} ,  new Product() { ProductName = "ExampleProductBulkMerge", Unit = "3" }       

        }});

​

        DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);

        DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);

        using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))

        {

            connection.BulkMerge(suppliers).ThenForEach(x => x.Products.ForEach(y => y.SupplierID =  x.SupplierID)).ThenBulkMerge(x => x.Products);

        }

        suppliers = null;

        using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))

        {

            var supplierDictionary = new Dictionary<int, Supplier>();

            suppliers = connection.Query<Supplier, Product, Supplier>("Select A.SupplierID, A.SupplierName, B.ProductID, B.ProductName FROM Suppliers as A inner join Products as B on B.SupplierID =A.SupplierID where A.SupplierName = 'ExampleSupplierBulkMerge'",

            (supplier, product) => 

            {  

                Supplier supplierEntry;

                    if (!supplierDictionary.TryGetValue(supplier.SupplierID, out supplierEntry))

                    {

                        supplierEntry = supplier;

                        supplierEntry.Products = new List<Product>();

                        supplierDictionary.Add(supplier.SupplierID, supplierEntry);

                    }

​

                    supplierEntry.Products.Add(product);

                    return supplierEntry;

                }, splitOn: "ProductID").Distinct().ToList();

            FiddleHelper.WriteTable(suppliers);

            FiddleHelper.WriteTable(connection.Query<Product>("select * from Products where ProductName = 'ExampleProductBulkMerge'"));

            FiddleHelper.WriteTable(connection.QueryFirstOrDefault("select * from Products where ProductName = 'ExampleProductBulkMerge' order by ProductID"));

        }

    }

}