Solved

VB.NET Group and Sum Custom List(of T) LINQ

Posted on 2014-11-21
4
1,397 Views
Last Modified: 2016-02-16
Hi,

i have a class 'CustomerSales' below and two lists...

Private _customerSalesList As List(Of CustomerSales)
Private _priorYearSalesList As List(Of PriorSales)

Public Class CustomerSales
    Implements IComparable(Of CustomerSales)
    Public Sub New(ByVal customerNumber As Int32,
                   ByVal groupCode As String,
                   ByVal mtdSales As Integer,
                   ByVal mtdBudget As Integer,
                   ByVal qtdSales As Integer,
                   ByVal qtdBudget As Integer,
                   ByVal ytdSales As Integer,
                   ByVal ytdBudget As Integer)
        Me.CustomerNumber = customerNumber
        Me.GroupCode = groupCode
        Me.MtdSales = mtdSales
        Me.MtdBudget = mtdBudget
        Me.QtdSales = qtdSales
        Me.QtdBudget = qtdBudget
        Me.YtdSales = ytdSales
        Me.YtdBudget = ytdBudget
    End Sub

    Public Property CustomerNumber As Int32
    Public Property GroupCode As String
    Public Property MtdSales As Int32
    Public Property MtdBudget As Int32
    Public Property QtdSales As Int32
    Public Property QtdBudget As Int32
    Public Property YtdSales As Int32
    Public Property YtdBudget As Int32

    Public Function CompareTo(ByVal other As CustomerSales) As Integer Implements IComparable(Of CustomerSales).CompareTo
        Return -YtdSales.CompareTo(other.YtdSales)
    End Function
End Class

Open in new window


Basically I need to group by GroupCode (discarding CustomerNumber of course) and Sum the rest of the fields.  Then I need to join another list on GroupCode and add the PriorMTD/QTD/YTDSales as properties to the new object.

Public Class PriorSales
    Public Sub New(ByVal organizationalCode As String, ByVal priorMtdSales As Integer, ByVal priorQtdSales As Integer, ByVal priorYtdSales As Integer)
        Me.GroupCode = organizationalCode
        Me.PriorMtdSales = priorMtdSales
        Me.PriorQtdSales = priorQtdSales
        Me.PriorYtdSales = priorYtdSales
    End Sub

    Public Property GroupCode As String
    Public Property PriorMtdSales As Int32
    Public Property PriorQtdSales As Int32
    Public Property PriorYtdSales As Int32
End Class

Open in new window


Thanks
0
Comment
Question by:FamousMortimer
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 40458125
I always forget the exact syntax for grouping in VB.NET, but I believe it should be along the lines of:

Dim query = From customerSale In customerSalesCollection
            Group customerSale By customerSale.GroupCode Into Group
            Join priorSale In priorSalesCollection On GroupCode Equals priorSale.GroupCode
            Select New With
            {
                .MtdSalesTotal = Group.Sum(Function (item) item.MtdSales),
                .MtdBudgetTotal = Group.Sum(Function (item) item.MtdBudget),
                .QtdSalesTotal = Group.Sum(Function (item) item.QtdSales),
                .QtdBudgetTotal = Group.Sum(Function (item) item.QtdBudget),
                .YtdSalesTotal = Group.Sum(Function (item) item.YtdSales),
                .YtdBudgetTotal = Group.Sum(Function (item) item.YtdBudget),
                .PriorMtdSales = priorSale.PriorMtdSales,
                .PriorQtdSales = priorSale.PriorQtdSales,
                .PriorYtdSales = priorSale.PriorYtdSales
            }

Open in new window


** edited to correct a few bugs
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40458274
Hi FamousMortimer;

See if this is what you are looking for.

Dim custSalesList = (From cl As CustomerSales In _customerSalesList
                     Group By Code = cl.GroupCode Into clGroup = Group
                     Select New PriorSales(
                         organizationalCode:=Code,
                         priorMtdSales:=clGroup.Sum(Function(mtd) mtd.MtdSales),
                         priorQtdSales:=clGroup.Sum(Function(qtd) qtd.QtdSales),
                         priorYtdSales:=clGroup.Sum(Function(ytd) ytd.YtdSales)
                     )).ToList()

''  priorYearList contains those values that have the same GroupCode in both list with the new values for them
Dim priorYearList = (From pl As PriorSales In _priorYearSalesList
                     Join code In custSalesList On pl.GroupCode Equals code.GroupCode
                     Select New PriorSales(
                         organizationalCode:=pl.GroupCode,
                         priorMtdSales:=pl.PriorMtdSales + code.PriorMtdSales,
                         priorQtdSales:=pl.PriorQtdSales + code.PriorQtdSales,
                         priorYtdSales:=pl.PriorYtdSales + code.PriorYtdSales
                    )).ToList()

Open in new window

0
 
LVL 10

Author Closing Comment

by:FamousMortimer
ID: 40458284
Brilliant sir.
0
 
LVL 10

Author Comment

by:FamousMortimer
ID: 40458291
Sorry Fernando... i didn't get a chance to see your comment until after i verified that kaufmed's worked for me and assigned the points.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question