using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QueryingDemo.Model;

namespace QueryingDemo.Samples
{
    public class Sample5 : ISample
    {
        public int Number => 5;
        public string Title => "SELECT records with a collection";

        public void Run(NorthwindContext dc)
        {
            var result = dc.Products
                .Select(p => new
                {
                    Id = p.ProductId,
                    Name = p.ProductName,
                    OrderDetails = p.OrderDetails.Select(d => new
                    {
                        Quantity = d.Quantity,
                        CustomerName = d.Order.Customer.CompanyName
                    })
                    //.ToList()     // SELECT N+1
                })
                .ToList();

            var result2 = dc.OrderDetails
                .Select(d => new
                {
                    Product = d.Product,
                    Quantity = d.Quantity,
                    CustomerName = d.Order.Customer.CompanyName
                })
                .GroupBy(d => d.Product)        // client eval!
                .Select(d => new
                {
                    Id = d.Key.ProductId,
                    Name = d.Key.ProductName,
                    OrderDetails = d.Select(i => new
                    {
                        Quantity = i.Quantity,
                        CustomerName = i.CustomerName
                    })
                })
                .ToList();
        }
    }
}
