如何在 dto 中对数据库中的数据进行分组?
例如,我希望我的输出是这样的:
{
id: "random-id-tst",
years: [
{
year: 1,
productIds: ['guid-id-1', 'guid-id-2'],
},
{
year: 2,
productIds: ['guid-id-3'],
}
]
}
但我正在挣扎,尝试了很多事情并走到了这一步:
{
id: "random-id-tst",
years: [
{
year: 1,
productIds: ['guid-id-1'],
},
{
year: 1,
productIds: ['guid-id-2'],
},
{
year: 2,
productIds: ['guid-id-3'],
},
]
}
public class Order
{
public Guid ID{ get; set; }
public ICollection<OrderLine> OrderLines { get; set; }
}
public class OrderLine
{
public Guid OrderID { get; set; }
public Order Order { get; set; }
public Guid ProductID { get; set; }
public Product Product { get; set; }
public int Year { get; set }
}
public class Product
{
public Guid ID { get; set; }
public ICollection<OrderLine> OrderLines { get; set; }
}
public class OrderDto
{
public Guid ID { get; set; }
public IEnumerable<YearsDto> Years { get; set; }
}
public class YearDto
{
public int Year { get; set; }
public ICollection<Guid> ProductIds { get; set; }
}
我尝试了很多东西,但每次都得到不同的错误。
这是我所做的:
var orderLines = (await _context.OrderLine
.Where(s => s.orderID == orderId)
.ToListAsync()).GroupBy(ol => ol.Year);
var order = (await _context.Order
.Where(order => order.ID == orderId)
.Select(x =>
new OrderDto()
{
ID = order.ID,
Years = orderLines.Select(ss => new YearsDto()
{
Year = 1,
}),
}
).FirstAsync());
return order;
如您所见,我对Year
进行了硬编码并且没有包含productIds
,因为由于某种原因我无法引用ss.Year
或ss.Product
,同样当我运行它时,它给了我一个错误:InvalidOperationException: variable '__Select_1' of type 'System.Collections.Generic.IEnumerable`1[YearsDto]' referenced from scope '', but it is not defined
有人可以指出我如何解决问题的正确方向吗?
这段代码应该会得到你需要的结果:
var order = _context.OrderLine
.Include(o => o.OrderLines)
.Where(s => s.Id == orderId)
.First();
var orderDto = new OrderDto()
{
ID = order.Id,
Years = order.OrderLines.ToLookup(ol => ol.Year).Select(g => new YearDto() {Year = g.Key, ProductIds = g.ToList().Select(x => x.ProductID).ToList()})
};
在你的第一组中,你可能想要类似的东西
var orderLines = (await _context.OrderLine
.Where(s => s.orderID == orderId)
.ToListAsync()).GroupBy(ol => ol.Year, (key, group) => new { Year = key, Items = group.ToList()});
这样,您的最终对象将是匿名项的枚举,其中包含属于该年份的属性Year
和属性Items
。
因此,查询的其余部分(只是为了使其编译和获取所需的数据(可以转换为:
var order = (await _context.Order
.Where(order => order.ID == orderId)
.Select(x =>
new OrderDto()
{
ID = order.ID,
Years = orderLines.Select(ss => new YearsDto()
{
Year = ss.Year,
ProductsIds = ss.Items.Select(i => i.ProductID)
}),
}
).FirstAsync());
return order;
由于除了 ID(您已经拥有的 ID(之外,您不需要 OrderDto 中的任何额外数据,因此您可以这样做:
var years = ( await _context.OrderLine
.Where(s => s.orderID == orderId)
.Select( ol => new {ol.Year, ol.ProductID})
.ToListAsync())
.GroupBy(ol => ol.Year)
.Select(g => new YearDto
{
Year = g.Key,
ProductIds = g.Select(i => i.ProductID).ToList() }
)
.ToList();
var orderDto = new OrderDto {ID = orderId; Years = years };
虽然您将失去 db 中是否存在订单的检查,但您可以单独查询它。