// 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<OrderDetail> 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 = new List<OrderDetail>();

                    orderDictionary.Add(orderEntry.OrderID, orderEntry);

                }

​

                orderEntry.OrderDetails.Add(orderDetail);

                return orderEntry;

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

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

            FiddleHelper.WriteTable(list);

            FiddleHelper.WriteTable(list.First().OrderDetails);

        }

    }

}