Dapper multi object query One-Many

Question

Not really sure why I'm not getting the child object populated.

My tables:

Product:
       [ProductId]
      ,[Brand]
      ,[Model]

StoreProduct:
       [StoreId]
      ,[ProductId]
      ,[StoreProductId]

Class

public class Product
{
        public Guid ProductId { get; set; }
        public string Brand { get; set; }
        public string Model { get; set; }
        public virtual List<StoreProduct> StoreProducts { get; set; }
}

public class StoreProduct
{
        public int StoreId { get; set; }       //Key 0
        public Guid ProductId { get; set; }    //Key 1
        public Store Store { get; set; }
        public Product Product { get; set; }
        public string StoreProductId { get; set; } //A new Id specific for each store
}

My Dapper Code

string sql = "SELECT * FROM StoreProduct AS A INNER JOIN Product AS B ON A.ProductId = B.ProductId WHERE A.StoreProductId = @StoreProductId and A.StoreId = @StoreId";
                    var connection = AppDbContext.Database.GetDbConnection();
                    return connection.Query<StoreProduct, Product, Product>(
                        sql, 
                        (StoreProduct, Product) => { StoreProduct.ProductId = Product.ProductId; return Product; }, 
                        new { StoreProductId = storeProductId, StoreId = StoreID }, splitOn: "ProductId")
                    .FirstOrDefault();

What the DB returns:

But... StoreProducts List is null.

Answer 1

Use Dapper the way it works.

var listProduct = new Dictionary<string, Product>();
var listStoreProduct = new Dictionary<string, StoreProduct>();

using var connection = _connectionProvider.GetConnection();
var query = "SELECT * FROM StoreProduct AS A INNER JOIN Product AS B ON A.ProductId = B.ProductId WHERE A.StoreProductId = @StoreProductId and A.StoreId = @StoreId";

var result = connection.Query<Product, StoreProduct, Product>(query, (product, storeProduct) =>
{
    if (!listProduct.TryGetValue(product.Id, out var entry))
    {
        entry = product;
        listProduct.Add(entry.Id, entry);
    }
    if (storeProduct != null && !listStoreProduct.TryGetValue(storeProduct.Id, out var procInstance))
    {
        listStoreProduct.Add(procInstance.Id, procInstance);
        entry.ProcessInstance.Add(procInstance);
    }
    return entry;
}, splitOn: "ProductId").Distinct().ToList();

I hope I could have helped you.