我正在用 VB.NET 编写一个发票系统,并使用实体框架来存储我的对象 ASP.NET。我想汇总任何特定月份的所有计费行项目,并将它们整理到发票中。以下 SQL 查询完成此操作,但我无法让它在实体框架代码优先中工作:
SQL查询:
SELECT DISTINCT
[database].[dbo].[WorkOrderDetails].PriceCodeID,
[database].[dbo].[WorkOrderDetails].[Description],
SUM([database].[dbo].[WorkOrderDetails].[Quantity]) AS [Quantity],
SUM([Subtotal]) AS Subtotal,
SUM([Total]) AS Total
FROM
[database].[dbo].[WorkOrderDetails]
INNER JOIN
[database].[dbo].[WorkOrders] ON [database].[dbo].[WorkOrderDetails].[WorkOrderID] = [database].[dbo].[WorkOrders].[WorkOrderID]
WHERE
[ClientID] = 182
AND [WorkOrders].[Date] >= '10/1/2016'
AND [WorkOrders].[Date] < '10/31/2016'
GROUP BY
[PriceCodeID], [Description]
此查询的输出类似于以下内容:
PriceCodeID | Description | Quantity | Subtotal | Total
------------+-------------+----------+----------+--------
26 BOX REFILE 19 47.50 47.50
28 BOX RETRIEVAL 15 37.50 37.50
98 Del/Pu Out 376 545.20 545.20
95 Shredding 16893 760.19 760.19
我的对象是:
WorkOrder
: 工作订单 ID, 日期, 客户端 ID (外键), 列表 (WorkOrderDetail
)WorkOrderDetail
:工作订单详细ID,价格代码ID(外键),描述,单价,数量,小计,税金,总计,工作订单ID(外键)PriceCode
: 价格代码ID, 简码, 描述, 单价, 税金
我正在尝试使用以下语句,但我不确定如何使用GroupBy
语句。
newInvoice.Details = _db.WorkOrderDetails
.Include("WorkOrder")
.Where(Function(wod) wod.WorkOrder.Date >= newInvoice.StartDate And
wod.WorkOrder.Date < newInvoice.EndDate And
wod.WorkOrder.ClientID = newInvoice.ClientID)
.GroupBy(...)
.ToList()
您可以通过以下方式获得聚合结果
Dim aggregate = From wod in _db.WorkOrderDetails
Where wod.WorkOrder.Date >= newInvoice.StartDate And
wod.WorkOrder.Date < newInvoice.EndDate And
wod.WorkOrder.ClientID = newInvoice.ClientID)
Group wod By Key = New With { Key wod.PriceCodeID, Key Description }
Into Group
Select agg = Key.PriceCodeID,
Key.Description,
Quantity = Group.Sum(Function(wod) wod.Quantity),
Subtotal = Group.Sum(Function(wod) wod.Subtotal),
Total = Group.Sum(Function(wod) wod.Total)
不能在此查询中直接创建新WorkOrderDetail
,因为 EF 不允许在 LINQ 查询中创建新的实体类型。但您可以使用aggregate.AsEnumerable()
继续:
newInvoice.Details = aggregate.AsEnumerable()
.Select(Function (wod) New WorkOrderDetail ...)