仪表板选择嵌套对象 [英] Dapper selecting nested objects
本文介绍了仪表板选择嵌套对象的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的SQL看起来像
Select TOP 3 o.OrderId,
o.Total,
od.Quantity,
od.ItemDescription,
os.OrderStatusDescription,
os.OrderStatusID
From dbo.Orders o
INNER JOIN dbo.OrderStatuses os ON os.OrderStatusID = o.OrderStatusID
INNER JOIN dbo.OrderDetails od ON od.OrderID = o.OrderID
Where o.CustomerId = 100 order by OrderDate desc;
我的类在c#端,如下所示,我在数据库端也有三个表。 我正在尝试是否可以使用Dapper将3个表映射到一个对象顺序中,而不必执行多个SELECT查询。 我需要从订单表和它的子表加载前3行。%1 orderID的OrderDetail可以有%n行。
如何使用Dapper一次查找选择嵌套对象?
class Order{
int OrderId {get;set;}
decimal Total {get;set;}
List<OrderDetail> Details {get;set;}
OrderStatus Status {get;set;}
}
class OrderDetail
{
int OrderLineId {get;set;}
int OrderId {get;set;}
string ItemDescription {get;set;}
int Quantity {get;set;}
}
class OrderStatus
{
int OrderStatusID {get;set;}
string OrderStatusDescription {get;set;}
}
这是我到目前为止尝试过的方法,但不成功
sqlConnection.Open();
IEnumerable orders = sqlConnection
.Query<Order>(
@"select Order.*, OrderDetail.*
from Order join OrderDetail
inner join on Order.OrderId = OrderDetail.OrderId
inner join on Order.OrderStatusID = OrderStatus.OrderId
and Order.CustomerId = 100",
(o, od, os) =>
{
o.Details = od; //this is a List<OrderDetail>
o.Status = os;
return o;
}); //
推荐答案
有一些方法可以实现您的案例,但对于我来说,您可以通过以下代码来实现
var sql = @"Select TOP 3 o.OrderId,
o.Total,
os.OrderStatusID,
os.OrderStatusDescription,
od.OrderLineId,
od.Quantity,
od.ItemDescription
From dbo.Orders o
INNER JOIN dbo.OrderStatuses os ON os.OrderStatusID = o.OrderStatusID
INNER JOIN dbo.OrderDetails od ON od.OrderID = o.OrderID
Where o.CustomerId = 100 order by OrderDate desc;";
sqlConnection.Open();
var lookupOrders = new Dictionary<int,Order>();
var oders = sqlConnection.Query<Order, OrderStatus, OrderDetails, Order>(sql,
(o, os, od) =>
{
Order order;
if(!lookupOrders.ContainsKey(o.OrderId)){
lookupOrders.Add(o.OrderId, o);
order = o;
order.Details = new List<OrderDetail>();
}
else {
order = lookupOrders[o.OrderId];
}
order.Details.Add(od);
order.Status = os;
return order;
}, splitOn: "OrderId, OrderStatusID, OrderLineId"
).Distinct();
这篇关于仪表板选择嵌套对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文