Dapper select inner join mapping to a model

Question

Assume I have the following tables

  Table Products
  --------
  Id (int)
  Name (varchar)
  BrandId (int)

  Table Brands
  -----------
  Id (int)
  BrandName (varchar)

and the following classes in CSharp

public class Product{
   public int Id { get; set;}
   public string Name { get;}
   public ProductBrand Brand { get;}
}
public class ProductBrand{
   public int Id { get; set;}
   public string BrandName { get;}
}

How can i Select inner join the two tables and map to the ProductBrand model with all its properties in dapper?

Answer 1

Working code, please make changes like below,

    public class Product{
   public int Id { get; set;}
   public string ProductName { get;}
}
public class Brand{
   public int BrandId { get; set;}
   public string BrandName { get;}
   public IList<Product>Products { get;}
}

var sql ="SELECT Id, ProductName, BrandId, BrandName FROM Brand B INNER JOIN Product  P ON B.BrandId=P.BrandId"
var products = connection.QueryAsync<Brand, Product, Brand>(sql, 
(brand, product) => {
if(brand.Products==null)
{
    brand.Products = new List<Product>;
}
    brand.Products.Add(product);
    return brand;
},
splitOn: "BrandId");