// Dapper Plus

// Doc: https://dapper-tutorial.net/query

​

// @nuget: Dapper -Version 1.60.6

​

using Dapper;

using System;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Collections.Generic;

public class Program

{

    public class OrderDetail

    {

        public int OrderDetailID { get; set; }

        public int OrderID { get; set; }

        public int ProductID { get; set; }

        public int Quantity { get; set; }

    }

    public class Order

    {

        public int OrderID { get; set; }

        public int CustomerID { get; set; }

        public int EmployeeID { get; set; }

        public DateTime OrderDate  { get; set; }

        public int ShipperID  { get; set; }

        public List<int> OrderDetails { get; set; }

    }

    public static void Main()

    {

        string sql = "SELECT TOP 10 * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";

​

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

        {           

            var orderDictionary = new Dictionary<int, Order>();

            var list = connection.Query<Order, OrderDetail, Order>(

                sql,

                (order, orderDetail) =>

                {

                    Order orderEntry;

                    if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))

                    {

                        orderEntry = order;

                        orderEntry.OrderDetails = orderEntry.OrderDetails ?? new List<int>();

                        orderDictionary.Add(orderEntry.OrderID, orderEntry);

                    }

​

                    orderEntry.OrderDetails.Add(orderDetail.OrderDetailID);

                    return orderEntry;

                },

                splitOn: "OrderDetailID")

            .Distinct()

            .ToList();

​

            Console.WriteLine("Orders Count:" + list.Count);

            foreach(Order item in list)

            {

                Console.WriteLine("orderID" + item.OrderID);

                Console.WriteLine(string.Join(",",item.OrderDetails));

            }

            FiddleHelper.WriteTable(list);

​

        }

    }

}