using Dapper;

using System;

using System.Data;

using System.Data.SqlServerCe;

using System.Linq;

using System.Collections.Generic;

public class Program

{

    public class Product

    {

        public int ProductID { get; set; }

        public string ProductName { get; set; }

        public int SupplierID { get; set; }

        public int CategoryID { get; set; }

        public string Unit { get; set; }

        public double Price { get; set; }

        public Category Category { get; set; }

    }

    public class Category

    {

        public int CategoryID { get; set; }

        public string CategoryName { get; set; }

        public string Description { get; set; }

        public List<Product> Products { get; set;}

    }

    public static void Main()

    {

        string sql = "SELECT * FROM Categories AS A INNER JOIN Products AS B ON A.CategoryID = B.CategoryID;";

​

        using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))

        {

            connection.Open();

            var categoryDictionary = new Dictionary<int, Category>();

            var list = connection.Query<Category, Product, Category>(

                sql,

                (category, product) =>

                {

                    Category categoryEntry;

                    if (!categoryDictionary.TryGetValue(category.CategoryID, out categoryEntry))

                    {

                        categoryEntry = category;

                        categoryEntry.Products = new List<Product>();

                        categoryDictionary.Add(categoryEntry.CategoryID, categoryEntry);

                    }

​

                    categoryEntry.Products.Add(product);

                    return categoryEntry;

                },

                splitOn: "CategoryID")

            .Distinct()

            .ToList();

​

            Console.WriteLine(list.Count);

        }

    }

}