Nse Nse - 2 months ago 11
Vb.net Question

LINQ Sum Query on a Child element

I'm trying to aggregate the invoices in a month for my application. There are two objects; an

object, and an
object. The
object contains particulars regarding the invoice (date, customer, etc), while the
object contains the line items for each invoice (quantity, price, tax, etc). I want to calculate the revenue for each month based on the money coming in from invoices. I can aggregate the invoices by date using the
method, however I can't seem to add the line items in the
which would give me the total revenue for the month. The objects are listed below.

Invoice class:

Public Class Invoice

Public Property InvoiceID As Integer
Public Property InvoiceDate As Date
Public Property DueDate As Date
Public Property Details As List(Of InvoiceDetail)
Public Property Client As Client
Public Property ClientID As Integer

End Class

InvoiceDetails class:

Public Class InvoiceDetail

Public Property InvoiceDetailID As Integer
Public Property Description As String
Public Property UnitPrice As Decimal
Public Property Quantity As Integer
Public Property Subtotal As Decimal
Public Property Tax As Decimal
Public Property Total As Decimal
Public Property Invoice As Invoice

End Class

My current LINQ query is as follows:

Public Function GetRevenueByMonth(Year As Integer, Month As Integer) As DataSourceResult

Dim StartDate As New Date(Year, Month, 1)
Dim EndDate As Date = StartDate.AddMonths(1).AddSeconds(-1)

Dim Revenue = _db.Invoices.Include(Function(i) i.InvoiceDetails
.Select(Function(invd) invd.Total))
.Where(Function(i) i.InvoiceDate >= StartDate And i.InvoiceDate <= EndDate)
.GroupBy(Function(i) i.InvoiceDate.Date)
.Select(Function(r) New With {
.Date = r.Key, .Revenue = r.Sum(Function(invd) invd.Total)

End Function

The function looks fine until the second last line. I get the error of 'Total' is not a member of 'InvoiceDetail'. I would expect that
would reference the child element, but instead it's referencing the parent element. How can I make this LINQ query work?


You can do the following

 Dim Revenue = _db.InvoicesDetails
        .Where(Function(i) i.Invoice.InvoiceDate >= StartDate And i.Invoice.InvoiceDate <= EndDate)
        .GroupBy(Function(i) i.Invoice.InvoiceDate.Date)
        .Select(Function(r) New With {
            .Date = r.Key, .Revenue = r.Sum(Function(invd) invd.Total)

Hope this will help you