VB.NET中如何结合日期条件使用LINQ GroupBy与Where？

需求 #

结合日期条件，用LINQ的GroupBy与Where方法计算商品总采购量（PIQ）、总销售量（SIQ）及库存余额（BLC=PIQ-SIQ），需按商品分组汇总数据。

代码实现 #

Public Class Form3
    Private Purchase, Sales As New List(Of Invoice)
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadData()
    End Sub
    Private PurchaseDetails,SalesDetails As New List(Of Detail)
    Private Function CreateConnection() As String
        Return ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIAL2.accdb;Persist Security Info=False;")
    End Function
Private Sub LoadData()
        Using Connection = New OleDbConnection(CreateConnection())
 Purchase = CType(Connection.Query(Of Invoice)("SELECT * FROM PURCHASE"), List(Of Invoice))
            PurchaseDetails = CType(Connection.Query(Of Detail)("SELECT * FROM PURCHASEDETAILS"), List(Of Detail))
 Sales = CType(Connection.Query(Of Invoice)("SELECT * FROM SALES"), List(Of Invoice))
            SalesDetails = CType(Connection.Query(Of Detail)("SELECT * FROM SALESDETAILS"), List(Of Detail))
End Using
        Dim ps = From p In Purchase
                 Where p.DATEINVO >= CDate("01/01/2024")
                 From pd In PurchaseDetails
                 Select
             pd.ITEM,
             PIQ = pd.QTY,
             SIQ = 0,
             BLC = pd.QTY
                 Order By ITEM
        Dim ss = From s In Sales
                 Where s.DATEINVO >= CDate("01/01/2024")
                 From sd In SalesDetails
                 Select
            sd.ITEM,
            PIQ = 0,
            SIQ = sd.QTY,
            BLC = -sd.QTY
                 Order By ITEM
 Dim Card_temp = ps.Union(ss).OrderBy(Function(w) w.ITEM)
 Dim Card As New List(Of ItemCards3)
        Dim RunningBalance As Integer = 0
        For Each ct In Card_temp
            Dim sc As New ItemCards3
            With sc
                .ITEM = ct.ITEM
                .PIQ = ct.PIQ
                .SIQ = ct.SIQ
                .BLC = ct.PIQ - ct.SIQ
                '.BLC = RunningBalance + ct.BLC
                'RunningBalance = .BLC
            End With
            Card.Add(sc)
        Next
        DataGridView1.DataSource = Card
    End Sub

End Class
Public Class Invoice
    Property INVONO() As String
    Property DATEINVO() As Date
    Property CUSTCODE() As String
End Class

Public Class Detail
    Public Property INVONO() As String
    Public Property NOD() As Integer
    Public Property ITEM() As String
    Public Property QTY() As Integer
End Class
Public Class ItemCards3
    Public Property ITEM() As String
    Public Property PIQ As Integer
    Public Property SIQ As Integer
    Public Property BLC As Integer
End Class

数据库表结构 #

采购表（PURCHASE） #

采购明细表（PURCHASEDETAILS） #

销售表（SALES） #

销售明细表（SALESDETAILS） #

当前运行结果 #

期望结果 #

解决方案 #

问题分析 #

1. 原代码中采购单与采购明细、销售单与销售明细未通过INVONO关联，导致产生笛卡尔积，获取了不符合日期条件的明细数据。
2. 未对合并后的数据按ITEM分组汇总，因此输出逐条明细而非汇总结果。

修改后的代码 #

替换LoadData方法中的LINQ查询部分：

Private Sub LoadData()
    Using Connection = New OleDbConnection(CreateConnection())
        Purchase = CType(Connection.Query(Of Invoice)("SELECT * FROM PURCHASE"), List(Of Invoice))
        PurchaseDetails = CType(Connection.Query(Of Detail)("SELECT * FROM PURCHASEDETAILS"), List(Of Detail))
        Sales = CType(Connection.Query(Of Invoice)("SELECT * FROM SALES"), List(Of Invoice))
        SalesDetails = CType(Connection.Query(Of Detail)("SELECT * FROM SALESDETAILS"), List(Of Detail))
    End Using

    ' 获取符合日期条件的采购明细汇总
    Dim purchaseSummary = From p In Purchase
                          Where p.DATEINVO >= New Date(2024, 1, 1)
                          Join pd In PurchaseDetails On p.INVONO Equals pd.INVONO
                          Group By pd.ITEM Into Group
                          Select New With {
                              .ITEM = ITEM,
                              .TotalPIQ = Group.Sum(Function(x) x.QTY),
                              .TotalSIQ = 0
                          }

    ' 获取符合日期条件的销售明细汇总
    Dim salesSummary = From s In Sales
                       Where s.DATEINVO >= New Date(2024, 1, 1)
                       Join sd In SalesDetails On s.INVONO Equals sd.INVONO
                       Group By sd.ITEM Into Group
                       Select New With {
                           .ITEM = ITEM,
                           .TotalPIQ = 0,
                           .TotalSIQ = Group.Sum(Function(x) x.QTY)
                       }

    ' 合并采购和销售汇总，按商品分组计算最终数据
    Dim finalSummary = From item In purchaseSummary.Union(salesSummary)
                       Group By item.ITEM Into Group
                       Select New ItemCards3 With {
                           .ITEM = ITEM,
                           .PIQ = Group.Sum(Function(x) x.TotalPIQ),
                           .SIQ = Group.Sum(Function(x) x.TotalSIQ),
                           .BLC = Group.Sum(Function(x) x.TotalPIQ) - Group.Sum(Function(x) x.TotalSIQ)
                       }

    DataGridView1.DataSource = finalSummary.ToList()
End Sub

修改说明 #

1. 使用Join关联采购单与采购明细、销售单与销售明细，确保仅获取符合日期条件的单据对应的明细。
2. 先对采购和销售数据分别按ITEM分组汇总，再合并后再次分组，计算每个商品的总采购量、总销售量及库存余额。
3. 使用New Date(2024,1,1)替代CDate("01/01/2024")，避免日期格式解析问题。

内容的提问来源于stack exchange，提问作者user22579796