// Dapper Plus

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

​

// @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 static void Main()

    {

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

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

        {

            // Just for more simple...

            connection.Query("Update OrderDetails set ProductID = 11");

            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.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);

        }

        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.BulkDelete(suppliers.SelectMany(x => x.Products)).BulkDelete(suppliers);

        }

        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.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();

            Console.WriteLine("Row count after delete: " + suppliers.Count());

        }

    }

}