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

Invoice
object, and an
InvoiceDetails
object. The
Invoice
object contains particulars regarding the invoice (date, customer, etc), while the
InvoiceDetails
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
GroupBy
method, however I can't seem to add the line items in the
InvoiceDetails
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
invd
would reference the child element, but instead it's referencing the parent element. How can I make this LINQ query work?

Answer

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