Dapper select inner join mapping to a model

user_15921653 0
Viewed 1913

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?

dapper asp.net-core-mvc asp.net-core c#
4 Answers

You can use the Query method like below:

using (IDbConnection connection = new SqlConnection(ConnectionString))
{
    string sql = "SELECT Products.Id, BrandName FROM Products INNER JOIN Brands ON Products.BrandId = Brands.Id";
    var result = connection.Query<ProductBrand>(sql);
}
user_119652970

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");
user_2168850

If you want to use it by creating a procedure

CREATE PROCEDURE dbo.GetProducts

AS
BEGIN
    SELECT 
        Products.Id,
        Brands.BrandName
    FROM Products
        INNER JOIN
        Brands ON Products.BrandId  = Brands.Id

END
GO

using (IDbConnection db = new SqlConnection("ConnectionString"))
{
    
   var productBrands = db.Query<ProductBrand>("GetProducts").ToList();
    
}
user_154643760

Relationships management in Dapper goes the other way- Each Brand should have a list of Products.

so the classes should look like this:

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

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

and then you can query like this:

var products = connection.QueryAsync<Product, Brand, Product>(sql, 
(product, brand) => {
    product.Brand = brand;
    return product;
},
splitOn: "BrandId");
user_123729920
Related