how to use linq with union multi table orderby item without duplicate with dapper in VB.NET

I'm trying to use linq with union multi table orderby item without duplicate with dapper in VB.NET.

But the result is not being duplicate is there something wrong with my code. Please Guide Me.

Thanks

Public Class Form1
    Private Purchase, RetSales, RetPurchase, Sales As New List(Of Invoice)

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadData()
    End Sub

    Private Laststock23, PurchaseDetails, RetSalesDetails, RetPurchaseDetails, SalesDetails As New List(Of Detail)
    Private Function CreateConnection() As String
        Return ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIAL.accdb;Persist Security Info=False;")
    End Function
    Private Sub LoadData()
        Using Connection = New OleDbConnection(CreateConnection())
            Laststock23 = CType(Connection.Query(Of Detail)("SELECT * FROM Laststock23"), List(Of Detail))
            Purchase = CType(Connection.Query(Of Invoice)("SELECT * FROM PURCHASE"), List(Of Invoice))
            PurchaseDetails = CType(Connection.Query(Of Detail)("SELECT * FROM PURCHASEDETAILS"), List(Of Detail))
            RetPurchase = CType(Connection.Query(Of Invoice)("SELECT * FROM RETPURCHASE"), List(Of Invoice))
            RetPurchaseDetails = CType(Connection.Query(Of Detail)("SELECT * FROM RETPURCHASEDETAILS"), 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))
            RetSales = CType(Connection.Query(Of Invoice)("SELECT * FROM RETSALES"), List(Of Invoice))
            RetSalesDetails = CType(Connection.Query(Of Detail)("SELECT * FROM RETSALESDETAILS"), List(Of Detail))
        End Using
        Dim ls = From lsd In Laststock23
                 Select
         lsd.ITEM,
         LSQ = lsd.QTY,
         PIQ = 0,
         SIQ = 0,
         RPQ = 0,
         RSQ = 0,
        BLC = lsd.QTY
                 Order By ITEM


        Dim ps = From p In Purchase
                 From pd In PurchaseDetails
                 Select
         pd.ITEM,
         LSQ = 0,
         PIQ = pd.QTY,
         SIQ = 0,
         RPQ = 0,
         RSQ = 0,
        BLC = pd.QTY
                 Order By ITEM

        Dim ss = From s In Sales
                 From sd In SalesDetails
                 Select
         sd.ITEM,
          LSQ = 0,
          PIQ = 0,
          SIQ = sd.QTY,
          RPQ = 0,
          RSQ = 0,
             BLC = -sd.QTY
                 Order By ITEM
                 Order By ITEM
        Dim rss = From rs In RetSales
                  From rsd In RetSalesDetails
                  Select
         rsd.ITEM,
          LSQ = 0,
          PIQ = 0,
         SIQ = 0,
         RPQ = 0,
         RSQ = rsd.QTY,
             BLC = rsd.QTY
                  Order By ITEM
        Dim rps = From rp In RetPurchase
                  From rpd In RetPurchaseDetails
                  Select
         rpd.ITEM,
         LSQ = 0,
         PIQ = 0,
         SIQ = 0,
         RPQ = rpd.QTY,
         RSQ = 0,
          BLC = -rpd.QTY


        Dim Card_temp = ls.Union(ps).Union(ss).Union(rps).Union(rss).OrderBy(Function(w) w.ITEM)
        Dim Card As New List(Of ItemCards)
        Dim RunningBalance As Integer = 0
        For Each ct In Card_temp
            Dim sc As New itemCards
            With sc
                .ITEM = ct.ITEM
                .LSQ = ct.LSQ
                .PIQ = ct.PIQ
                .SIQ = ct.SIQ
                .RPQ = ct.RPQ
                .RSQ = ct.RSQ
                .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 ItemCards
    Public Property ITEM() As String
    Public Property LSQ As Integer
    Public Property PIQ As Integer
    Public Property SIQ As Integer
    Public Property RSQ As Integer
    Public Property RPQ As Integer
    Public Property BLC As Integer
End Class

Result from code in datagridview

SAMPLE DATA

Table LASTSTOCK23

ITEM	QTY
TEST 1000	5
TEST 2000	10
TEST 9000	55

Table PURCHASE

INVONO	DATEINVO	CUSTCODE
PI1000	25-Jan-23	001
PI1001	29-Jan-23	002

Table PURCHASEDETAILS

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

Table SALES

INVONO	DATEINVO	CUSTCODE
SI1000	25-Jan-23	001
SI1001	29-Jan-24	002

Table SALESDETAILS

INVONO	NOD	ITEM	QTY
SI1000	1	TEST 1000	10
SI1000	2	TEST 2000	20
SI1001	1	TEST 8000	35

Table RETSALES

INVONO	DATEINVO	CUSTCODE
RS1000	25-Jan-23	001
RS1001	29-Jan-23	002

Table RETSALESDETAILS

INVONO	NOD	ITEM	QTY
RS1000	1	TEST 1000	10
RS1000	2	TEST 2000	20
RS1001	1	TEST 5000	25

Table RETPURCHASE

INVONO	DATEINVO	CUSTCODE
RP1000	25-Jan-23	001
RP1001	29-Jan-23	002

Table RETPURCHASEDETAILS

INVONO	NOD	ITEM	QTY
RP1000	1	TEST 1000	10
RP1000	2	TEST 2000	20
RP1000	1	TEST 4000	7

Desired Result

ITEM	LSQ	PIQ	SIQ	RSQ	RPQ	BLC
TEST 1000	5	10	10	10	10	5
TEST 2000	10	20	20	20	20	10
TEST 3000		15				15
TEST 4000					7	-7
TEST 5000				25		25
TEST 8000			35			-35
TEST 9000	55					55

Solution

Updated

You can use a simple query like this:

SELECT 
    ITEM, 
    SUM(LS) AS LSQ, 
    SUM(PI) AS PIQ, 
    SUM(SI) AS SIQ, 
    SUM(RS) AS RSQ, 
    SUM(RP) AS RPQ, 
    SUM(B)  AS BLC
FROM (
    SELECT ITEM, QTY AS LS,   0 AS PI,   0 AS SI,   0 AS RS,   0 AS RP,  QTY AS B FROM LastStock23
        UNION ALL
    SELECT ITEM,   0 AS LS, QTY AS PI,   0 AS SI,   0 AS RS,   0 AS RP,  QTY AS B FROM PurchaseDetails
        UNION ALL
    SELECT ITEM,   0 AS LS,   0 AS PI,   0 AS SI,   0 AS RS, QTY AS RP, -QTY AS B FROM RetPurchaseDetails
        UNION ALL
    SELECT ITEM,   0 AS LS,   0 AS PI, QTY AS SI,   0 AS RS,   0 AS RP, -QTY AS B FROM SalesDetails
        UNION ALL 
    SELECT ITEM,   0 AS LS,   0 AS PI,   0 AS SI, QTY AS RS,   0 AS RP,  QTY AS B FROM RetSalesDetails
    )  AS CARDEX
GROUP BY ITEM
ORDER BY ITEM

Its better if you keep all transaction in a single table and add a field for transaction type (purchase, detail, return-purchase, return-detail)

Update 2: Using LINQ

The LINQ codes have some unnecessary parts which should be removed.

First of all, the Purchase, RetPurchase, Sales, and RetSales tables are not needed in this case - only the details are needed.

Second, nested selects like Dim ps = From p In Purchase From pd In PurchaseDetails have negative impact on performance because of unneeded loops. Also any ordering before grouping is unnecessary and should be removed.

Third, instead of loading the raw tables and shaping the data as cards using LINQ in multiple steps, it is better to load primary cards with appropriate simple queries and then use a single simple LINQ code.

Public Class ItemCards
    Public Property ITEM() As String
    Public Property LSQ() As Integer
    Public Property PIQ() As Integer
    Public Property SIQ() As Integer
    Public Property RSQ() As Integer
    Public Property RPQ() As Integer
    Public Property BLC() As Integer
End Class

Private Laststock23, PurchaseDetails, RetSalesDetails, RetPurchaseDetails, SalesDetails As New List(Of ItemCards)

Const Qry_LS = "SELECT ITEM, QTY AS LSQ,   0 AS PIQ,   0 AS SIQ,   0 AS RSQ,   0 AS RPQ,  QTY AS BLC FROM LastStock23"
Const Qry_PI = "SELECT ITEM,   0 AS LSQ, QTY AS PIQ,   0 AS SIQ,   0 AS RSQ,   0 AS RPQ,  QTY AS BLC FROM PurchaseDetails"
Const Qry_RP = "SELECT ITEM,   0 AS LSQ,   0 AS PIQ,   0 AS SIQ,   0 AS RSQ, QTY AS RPQ, -QTY AS BLC FROM RetPurchaseDetails"
Const Qry_SI = "SELECT ITEM,   0 AS LSQ,   0 AS PIQ, QTY AS SIQ,   0 AS RSQ,   0 AS RPQ, -QTY AS BLC FROM SalesDetails"
Const Qry_RS = "SELECT ITEM,   0 AS LSQ,   0 AS PIQ,   0 AS SIQ, QTY AS RSQ,   0 AS RPQ,  QTY AS BLC FROM RetSalesDetails"

Using Connection = New OleDbConnection(My.Settings.Cons)
    With Connection
        Laststock23 = .Query(Of ItemCards)(Qry_LS)
        PurchaseDetails = .Query(Of ItemCards)(Qry_PI)
        RetPurchaseDetails = .Query(Of ItemCards)(Qry_RP)
        SalesDetails = .Query(Of ItemCards)(Qry_SI)
        RetSalesDetails = .Query(Of ItemCards)(Qry_RS)
    End With
End Using

Dim Cardex =
    From card In Laststock23.Union(PurchaseDetails).Union(RetPurchaseDetails).Union(SalesDetails).Union(RetSalesDetails)
    Group card By card.ITEM Into Group
    Let LSQ = (From x In Group Select x.LSQ).Sum
    Let PIQ = (From x In Group Select x.PIQ).Sum
    Let SIQ = (From x In Group Select x.SIQ).Sum
    Let RPQ = (From x In Group Select x.RPQ).Sum
    Let RSQ = (From x In Group Select x.RSQ).Sum
    Let BLC = (From x In Group Select x.BLC).Sum
    Select ITEM, LSQ, PIQ, SIQ, RPQ, RSQ, BLC
    Order By ITEM

DGV_Cardex.DataSource = Cardex.ToList