Solved

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

Posted on 2014-11-21
4
1,277 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 62

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
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.

773 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