Search code examples
vb.netlinqgroup-bydapper

how to use Linq groupby and where with date conditions in vb.net

I'm trying to use Linq groupby and where with date conditions in vb.net

Please Guide Me.

PIQ is total purchase quantity

SIQ is total sales quantity

BLC is total purchase quantity minus total sales quantity

Thanks

Public Class Form3
    Private Purchase, Sales As New List(Of Invoice)
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadData()
    End Sub
    Private PurchaseDetails,SalesDetails As New List(Of Detail)
    Private Function CreateConnection() As String
        Return ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIAL2.accdb;Persist Security Info=False;")
    End Function
Private Sub LoadData()
        Using Connection = New OleDbConnection(CreateConnection())
 Purchase = CType(Connection.Query(Of Invoice)("SELECT * FROM PURCHASE"), List(Of Invoice))
            PurchaseDetails = CType(Connection.Query(Of Detail)("SELECT * FROM PURCHASEDETAILS"), List(Of Detail))
 Sales = CType(Connection.Query(Of Invoice)("SELECT * FROM SALES"), List(Of Invoice))
            SalesDetails = CType(Connection.Query(Of Detail)("SELECT * FROM SALESDETAILS"), List(Of Detail))
End Using
        Dim ps = From p In Purchase
                 Where p.DATEINVO >= CDate("01/01/2024")
                 From pd In PurchaseDetails
                 Select
             pd.ITEM,
             PIQ = pd.QTY,
             SIQ = 0,
             BLC = pd.QTY
                 Order By ITEM
        Dim ss = From s In Sales
                 Where s.DATEINVO >= CDate("01/01/2024")
                 From sd In SalesDetails
                 Select
            sd.ITEM,
            PIQ = 0,
            SIQ = sd.QTY,
            BLC = -sd.QTY
                 Order By ITEM
 Dim Card_temp = ps.Union(ss).OrderBy(Function(w) w.ITEM)
 Dim Card As New List(Of ItemCards3)
        Dim RunningBalance As Integer = 0
        For Each ct In Card_temp
            Dim sc As New ItemCards3
            With sc
                .ITEM = ct.ITEM
                .PIQ = ct.PIQ
                .SIQ = ct.SIQ
                .BLC = ct.PIQ - ct.SIQ
                '.BLC = RunningBalance + ct.BLC
                'RunningBalance = .BLC
            End With
            Card.Add(sc)
        Next
        DataGridView1.DataSource = Card
    End Sub

End Class
Public Class Invoice
    Property INVONO() As String
    Property DATEINVO() As Date
    Property CUSTCODE() As String
End Class

Public Class Detail
    Public Property INVONO() As String
    Public Property NOD() As Integer
    Public Property ITEM() As String
    Public Property QTY() As Integer
End Class
Public Class ItemCards3
    Public Property ITEM() As String
    Public Property PIQ As Integer
    Public Property SIQ As Integer
    Public Property BLC As Integer
End Class

Table PURCHASE

INVONO DATEINVO CUSTCODE
PI1000 25-Jan-23 001
PI1001 29-Jan-23 002
PI1002 29-Jan-24 003
PI0003 30-Jan-24 004

Table PURCHASEDETAILS

INVONO NOD ITEM QTY
PI1000 1 TEST 1000 10
PI1000 2 TEST 2000 20
PI1001 1 TEST 3000 15
PI1002 1 TEST 1000 20
PI1003 1 TEST 1000 15

Table SALES

INVONO DATEINVO CUSTCODE
SI1000 25-Jan-23 001
SI1001 29-Jan-23 002
SI1002 29-Jan-24 005
SI1003 30-Jan-24 006
SI1004 29-Jan-24 004
SI1005 30-Jan-24 003

Table SALESDETAILS

INVONO NOD ITEM QTY
SI1000 1 TEST 1000 10
SI1000 2 TEST 2000 20
SI1001 1 TEST 8000 35
SI1002 1 TEST 1000 27
SI1003 1 TEST 1000 15
SI1004 1 TEST 2000 2
SI1005 1 TEST 2000 2

the result of the code above

ITEM PIQ SIQ BLC
TEST 1000 15 0 15
TEST 1000 10 0 25
TEST 1000 20 0 45
TEST 1000 0 10 35
TEST 1000 0 27 8
TEST 1000 0 15 -7
TEST 2000 20 0 13
TEST 2000 0 20 -7
TEST 2000 0 2 -9
TEST 3000 15 0 6
TEST 8000 0 35 -29

Desired result

ITEM PIQ SIQ BLC
TEST 1000 35 42 -7
TEST 2000 4 -4

result from answer @TN

update result answer code

result after update answer form @TN

result after update answer form @TN

Solution

  • The first issue I see is that your Purchase/PurchaseDetails query is referencing both tables, but is not providing a join condition. This will result in a cross join where every Purchase row joins to every PurchaseDetails row. The same applied to the Sales/SalesDetails query.

    I believe this can be corrected using the following Join ... On ... LINQ syntax.

    Dim ps = From p In Purchase
         Where p.DATEINVO >= CDate("01/01/2024")
         Join pd In PurchaseDetails On pd.INVONO Equals p.INVONO
         Select
             pd.ITEM,
             PIQ = pd.QTY,
             SIQ = 0,
             BLC = pd.QTY
         Order By ITEM
Dim ss = From s In Sales
         Where s.DATEINVO >= CDate("01/01/2024")
         Join sd In SalesDetails On sd.INVONO Equals s.INVONO
         Select
             sd.ITEM,
             PIQ = 0,
             SIQ = sd.QTY,
             BLC = -sd.QTY
         Order By ITEM

    Once you have those two queries defined, you can combine, group, and calculate the resulting sums with a combination of .Concat(), .GroupBy(), .Select(), and .Sum(). Within the .Select(), the group-by ITEM value is accessed via the grouping .Key property, while the other values are calculated using .Sum() to iterate through the members of each grouping.

    The .Concat() function is used instead of .Union() because we do not want to eliminate duplicates. The .Concat() function unconditionally includes all rows.

    The result would be something like:

    Dim Card_temp = ps.Concat(ss) _
    .GroupBy(Function(w) w.ITEM) _
    .Select(Function(g) New ItemCards3 With {
        .ITEM = g.Key,
        .PIQ = g.Sum(Function(m) m.PIQ),
        .SIQ = g.Sum(Function(m) m.SIQ),
        .BLC = g.Sum(Function(m) m.BLC)
    }) _
    .OrderBy(Function(ic) ic.ITEM) _
    .ToList()

    or

    Dim Card_temp = (From w In ps.Concat(ss)
                 Group By w.ITEM Into g
                 Select New ItemCards3 With {
                     .ITEM = g.ITEM,
                     .PIQ = g.Sum(Function(m) m.PIQ),
                     .SIQ = g.Sum(Function(m) m.SIQ),
                     .BLC = g.Sum(Function(m) m.BLC)
                 }
                 Order By ITEM
                ) _
                .ToList()

    The .OrderBy() and .ToList() are present to organize the results.

    Note: I am a C# programmer, not a VB programmer, so I prepared the above code snippets in C# and translated to VB with the assistance of a translator tool. Not sure if I have the syntax exactly right - particularly with respect to required vs optional line continuations characters.