使用Dapper构建具有多对多关系的对象

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Other properties
    public List<Category> Categories { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Other properties
    public List<Product> Products { get; set; }
}

using (var connection = new SqlConnection(connectionString))
{
    var sql = @"
        SELECT p.Id, p.Name, c.Id AS CategoryId, c.Name AS CategoryName
        FROM Product p
        INNER JOIN ProductCategory pc ON p.Id = pc.ProductId
        INNER JOIN Category c ON pc.CategoryId = c.Id";

    var products = new Dictionary<int, Product>();

    connection.Query<Product, Category, Product>(sql,
        (product, category) =>
        {
            if (!products.TryGetValue(product.Id, out var p))
            {
                p = product;
                p.Categories = new List<Category>();
                products.Add(p.Id, p);
            }
            p.Categories.Add(category);
            return p;
        },
        splitOn: "CategoryId");

    return products.Values.ToList();
}