// 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;";

​

        var orders = GetAll(sql);

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

        FiddleHelper.WriteTable(orders);

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

​

    }

    private static List<Order> GetAll(string sql) 

    {

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

            return list;

        }

    }

}