Dapper选择映射到模型的内部联接

Question

假设我有以下表格

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

以及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;}
}

如何将两个表Select inner join映射到具有dapper中所有属性的ProductBrand模型？

Answer 1

工作代码，请进行如下更改，

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

Answer 2

您可以使用以下查询方法：

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);
}

Answer 3

如果您想通过创建过程来使用它

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();

}

Answer 4

Dapper中的关系管理则相反——每个品牌都应该有一个产品列表。

所以类应该是这样的：

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; }
}

然后你可以这样查询：

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