LINQ聚合/SUM分组问题

发布于 2024-09-03 01:45:20 字数 704 浏览 9 评论 0原文

我无法理解将传统 SQL 聚合查询转换为 LINQ 查询。基本数据转储的工作原理如下：

    Dim result =
    (From i As Models.InvoiceDetail In Data.InvoiceDetails.GetAll
    Join ih As Models.InvoiceHeader In Data.InvoiceHeaders.GetAll On i.InvoiceHeaderID Equals ih.ID
    Join p As Models.Product In Data.Products.GetAll On i.ProductID Equals p.ID
    Join pg As Models.ProductGroup In Data.ProductGroups.GetAll On p.ProductGroupID Equals pg.ID
    Join gl As Models.GLAccount In Data.GLAccounts.GetAll On pg.GLAccountSellID Equals gl.ID
    Where (gl.ID = GLID)
    Select ih.Period,i.ExtendedValue)

我真正需要得到的是 ih.Period（从 1 到 12 的值）和 i.ExtendedValue 的相应聚合值。当我尝试对 ih 进行分组时，我收到有关 i 超出范围/上下文的错误，并且我不知道该怎么办。

原文

I'm having trouble getting my head around converting a traditional SQL aggregate query into a LINQ one. The basic data dump works like so:

    Dim result =
    (From i As Models.InvoiceDetail In Data.InvoiceDetails.GetAll
    Join ih As Models.InvoiceHeader In Data.InvoiceHeaders.GetAll On i.InvoiceHeaderID Equals ih.ID
    Join p As Models.Product In Data.Products.GetAll On i.ProductID Equals p.ID
    Join pg As Models.ProductGroup In Data.ProductGroups.GetAll On p.ProductGroupID Equals pg.ID
    Join gl As Models.GLAccount In Data.GLAccounts.GetAll On pg.GLAccountSellID Equals gl.ID
    Where (gl.ID = GLID)
    Select ih.Period,i.ExtendedValue)

What I need to really be getting out is ih.Period (a value from 1 to 12) and a corresponding aggregate value for i.ExtendedValue. When I try to Group ih I get errors about i being out of scope/context, and I'm not sure how else to go about it.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦初启 2024-09-10 01:45:20

为了简单起见，尝试将其有效地分成两个不同的查询。例子：

From result In
    (From i As Models.InvoiceDetail In Data.InvoiceDetails.GetAll
    Join ih As Models.InvoiceHeader In Data.InvoiceHeaders.GetAll On i.InvoiceHeaderID Equals ih.ID
    Join p As Models.Product In Data.Products.GetAll On i.ProductID Equals p.ID
    Join pg As Models.ProductGroup In Data.ProductGroups.GetAll On p.ProductGroupID Equals pg.ID
    Join gl As Models.GLAccount In Data.GLAccounts.GetAll On pg.GLAccountSellID Equals gl.ID
    Where (gl.ID = GLID) And (ih.FinancialYear = FinancialYear)
    Select ih.Period, i.ExtendedValue)
Group By result.Period Into Sum(result.ExtendedValue)

Try splitting it into effectively two different queries for simplicity. Example:

From result In
    (From i As Models.InvoiceDetail In Data.InvoiceDetails.GetAll
    Join ih As Models.InvoiceHeader In Data.InvoiceHeaders.GetAll On i.InvoiceHeaderID Equals ih.ID
    Join p As Models.Product In Data.Products.GetAll On i.ProductID Equals p.ID
    Join pg As Models.ProductGroup In Data.ProductGroups.GetAll On p.ProductGroupID Equals pg.ID
    Join gl As Models.GLAccount In Data.GLAccounts.GetAll On pg.GLAccountSellID Equals gl.ID
    Where (gl.ID = GLID) And (ih.FinancialYear = FinancialYear)
    Select ih.Period, i.ExtendedValue)
Group By result.Period Into Sum(result.ExtendedValue)

回复收藏 0 原文

~没有更多了~