CODEWITHSUNDEEP
vb.netentity-frameworklinqsumaggregate
Nse
Nse

Reputation: 305

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?

Upvotes: 1

Views: 579

Answers (1)

Monah
Monah

Reputation: 6784

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

Upvotes: 2

Related Questions