Mapping Parent-Child Relationships with Dapper

1.Widget.cs

public class Widget
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

2.GetAllWidgetsWithSqlDataReader.cs

public IEnumerable<Widget> GetAllWidgets(SqlConnection connection)
{
    List<Widget> widgets = new List<Widget>();

    using (SqlCommand command = new SqlCommand("SELECT [Id], [Name], [Description] FROM [Widgets]", connection))
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.read())
        {
            widgets.Add
            (
                new Widget
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Description = reader.GetString(2)
                }
            );
        }
    }
    
    return widgets;
}

3.GetAllWidgetsWithDapper.cs

public IEnumerable<Widget> GetAllWidgets(SqlConnection connection)
{
    return connection.Query<Widget>
    (
        "SELECT [Id], [Name], [Description] FROM [Widgets]"
    );
}

4.AddWidget.cs

public void AddWidget(SqlConnection connection, Widget widget)
{
    connection.Execute
    (
        "INSERT INTO [Widgets] ([Id], [Name], [Description]) VALUES (@Id, @Name, @Description)",
        widget
    );
}

5.DomainModel.cs

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Widget> Widgets { get; set; }
    
    public Category()
    {
        Widgets = new List<Widget>();
    }
}

public class Widget
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    public Category Category { get; set; }
}

6.Query.cs

Query<TFirst, TSecond, ..., TReturn>
(
    string query,
    Func<TFIrst, TSecond, ... TReturn> map,
    ...
)

7.GetHierarchyFirstPass.cs

public IEnumerable<Category> GetHierarchy(SqlConnection connection)
{
    return connection.Query<Category, Widget, Category>
    (
        @"SELECT
            [Categories].[Id],
            [Categories].[Name],
            [Widgets].[Id],
            [Widgets].[Name]
        FROM
            [Categories]
        JOIN
            [Widgets]
        ON
            [Widgets].[CategoryId] = [Categories].[Id]",
        (category, widget) =>
        {
            category.Widgets.Add(widget);
            widget.Category = category;
            
            return category;
        }
    );
}

8.GetHierarchy.cs

public IEnumerable<Category> GetHierarchy(SqlConnection connection)
{
    // Use a lookup to store unique categories.
    Dictionary<int, Category> lookup = new Dictionary<int, Category>();

    return connection.Query<Category, Widget, Category>
    (
        @"SELECT
            [Categories].[Id],
            [Categories].[Name],
            [Widgets].[Id],
            [Widgets].[Name]
        FROM
            [Categories]
        JOIN
            [Widgets]
        ON
            [Widgets].[CategoryId] = [Categories].[Id]",
        (possibleDupeCategory, widget) =>
        {
            Category category;
            
            // Look for the current category, storing it in `category` if it
            // exists.
            if (!lookup.TryGetValue(possibleDupeCategory.Id, out category))
            {
                // If the lookup doesn't contain the current category, add
                // it and store it in `category` as well.
                lookup.Add(possibleDupeCategory.Id, possibleDupeCategory);
                
                category = possibleDupeCategory;
            }
            
            // Regardless of the state of the lookup before this mapping,
            // `category` now refers to a distinct category.
            
            category.Widgets.Add(widget);
            widget.Category = category;
            
            return category;
        }
    ).Distinct();
}

Great sample!!!

public class Product
{
public int ProductId { get; set; }
public string ProductName { get; set; }
public int CategoryId { get; set; }
public Category Category { get; set; }
public int ProductOriginId { get; set; }
public ProductOrigin ProductOrigin { get; set; }
}

public class Category
{
public int CategoryId { get; set; }
public string CategoryName { get; set; }
public ICollection Products { get; set; }
}

public class ProductOrigin
{
public int ProductOriginId { get; set; }
public string ProductOriginName { get; set; }
}

public static async Task<List> GetCategoryWithProducts()
{
using (var connection = new SqlConnection(connString))
{
var sql = @" SELECT c.CategoryId,c.CategoryName,p.ProductId,p.ProductName,po.ProductOriginName
FROM Categories c
LEFT JOIN Products p ON p.CategoryId=c.CategoryId
LEFT JOIN ProductOrigins po on po.ProductOriginId=p.ProductOriginId";

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




            var list = connection.QueryAsync<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,ProductId").Result
                .Distinct()
                .ToList();


           // Console.WriteLine(list.Count);


            return list.ToList();


        }


    }

with the code category> product, I can buy the category and the products under the category together.
I want to get a list like category> product> productorigin.