error Unable to cast object of type 'System.Collections.Generic.List` to type 'System.Collections.Generic.IList` via bindinglist in VB.NET

Question

I have 2 errors, namely :

1. Unable to cast object of type 'System.Collections.Generic.List`1[VB$AnonymousType_15`7[System.String,System.String,System.String,System.Object,System.Object,System.Object,System.String]]' to type 'System.Collections.Generic.IList`1

   in line code

   bindingSource = New BindingSource With {.DataSource = New BindingList(Of ItemCards2)(CType(Cardex.ToList(), IList(Of ItemCards2)))}
   

2. Cannot infer a common type because more than one type is possible

   in line code:

   PIQ = If(PIQ <> CInt("0"), PIQ, ""),
   SIQ = If(SIQ <> CInt("0"), SIQ, ""),
   BLC = If(BLC <> CInt("0"), BLC, ""),
   

is there something wrong with my code please guide me

Thanks

Public Class Form2
    Private bindingSource As BindingSource = Nothing
    Private MasterItem,
        PurchaseDetails,
        SalesDetails As New List(Of ItemCards2)
    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadLINQ()
    End Sub

    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 LoadLINQ()
        Const Qry_MST = "SELECT * FROM MASTERITEM"

        Const Qry_PI = "SELECT a.ITEM, '' AS PRODUCTIONNAME, '' AS BRAND, 0 AS LSQ, 0 AS TPIQ, a.QTY AS PIQ, 0 AS TSIQ, 0 AS SIQ, 0 AS TRSQ, 0 AS RSQ, 0 AS TRPQ, 0 AS RPQ,  0 AS BLC FROM (PurchaseDetails a INNER JOIN" &
                       " Purchase b ON a.INVONO = b.INVONO) INNER JOIN" &
                       " CUSTCODE c On b.CUSTCODE = c.CUSTCODE" &
                       " WHERE b.DATEINVO >= CDate('01/01/2024')" &
                       " AND c.GROUP IS NULL"
        Const Qry_SI = "SELECT a.ITEM, '' AS PRODUCTIONNAME, '' AS BRAND, 0 AS LSQ, 0 AS TPIQ,   0 AS PIQ, 0 AS TSIQ, a.QTY AS SIQ, 0 AS TRSQ, 0 AS RSQ, 0 AS TRPQ, 0 AS RPQ, 0 AS BLC FROM (SalesDetails a INNER JOIN" &
                       " Sales b ON a.INVONO = b.INVONO) INNER JOIN" &
                       " CUSTCODE c ON b.CUSTCODE = c.CUSTCODE" &
                       " WHERE b.DATEINVO >= CDATE('01/01/2024')" &
                       " AND c.GROUP IS NULL"
        Using Connection = New OleDbConnection(CreateConnection())
            With Connection
                MasterItem = CType(Connection.Query(Of ItemCards2)(Qry_MST), List(Of ItemCards2))
                PurchaseDetails = CType(Connection.Query(Of ItemCards2)(Qry_PI), List(Of ItemCards2))
                SalesDetails = CType(Connection.Query(Of ItemCards2)(Qry_SI), List(Of ItemCards2))
            End With
        End Using
        Dim Cardex =
           From card In PurchaseDetails.Union(SalesDetails)
           Join mst In MasterItem On card.ITEM Equals mst.ITEM
           Group card By card.ITEM, mst.PRODUCTIONNAME, mst.BRAND Into Group
           Let PIQ = (From x In Group Select x.PIQ).Sum
           Let SIQ = (From x In Group Select x.SIQ).Sum
           Let BLC = (PIQ) - (SIQ)
           Let STATUS = If(BLC < 24, "NEED TO PRODUCE", "")
           Select ITEM,
                  PRODUCTIONNAME,
                  BRAND,
                  PIQ = If(PIQ <> CInt("0"), PIQ, ""),
                  SIQ = If(SIQ <> CInt("0"), SIQ, ""),
                  BLC = If(BLC <> CInt("0"), BLC, ""),
                  STATUS
           Order By ITEM
        bindingSource = New BindingSource With {.DataSource = New BindingList(Of ItemCards2)(CType(Cardex.ToList(), IList(Of ItemCards2)))}
        DataGridView1.DataSource = bindingSource
    End Sub
End Class
Public Class ItemCards2
    Public Property ITEM() As String
    Public Property PRODUCTIONNAME As String
    Public Property BRAND As String
    Public Property PIQ As Integer
    Public Property SIQ As Integer
    Public Property BLC As Integer
    Public Property STATUS As String
End Class

Answer 1

1. Note that List(Of T) implements IList(Of T). Therefore, it is not necessary to do the conversion. However, you seem to be creating an anonymous class. Create an ImtemCards2 explicitly in the Select statement instead.

2. You are mixing strings and integers. PIQ <> CInt("0") makes no sense. If you want an Integer constant, just write PIQ <> 0. If PIQ is an integer, you cannot assign it a string "". If you want to display 0 as an empty string, do this by formatting the cell or textbox appropriately. The Format would be "#" or "#,#" (with thousands separator), or if you have a floating point type "#,#.##".

   Another possibility would be to declare PIQ, SIQ and BLC as Nullable(Of Integer) or simply Integer?. Then you can assign Nothing to them.

With:

Public Class ItemCards2
    Public Property ITEM As String
    Public Property PRODUCTIONNAME As String
    Public Property BRAND As String
    Public Property PIQ As Integer?
    Public Property SIQ As Integer?
    Public Property BLC As Integer?
    Public Property STATUS As String
End Class

We can write

Dim Cardex =
    From card In PurchaseDetails.Union(SalesDetails)
    Join mst In MasterItem On card.ITEM Equals mst.ITEM
    Group card By card.ITEM, mst.PRODUCTIONNAME, mst.BRAND Into Group
    Let PIQ = (From x In Group Select x.PIQ).Sum
    Let SIQ = (From x In Group Select x.SIQ).Sum
    Let BLC = PIQ - SIQ
    Let STATUS = If(BLC < 24, "NEED TO PRODUCE", "")
    Order By ITEM
    Select New ItemCards2 With {
        .ITEM = ITEM,
        .PRODUCTIONNAME = PRODUCTIONNAME,
        .BRAND = BRAND,
        .PIQ = If(PIQ <> 0, PIQ, Nothing),
        .SIQ = If(SIQ <> 0, SIQ, Nothing),
        .BLC = If(BLC <> 0, BLC, Nothing),
        .STATUS = STATUS
    }

bindingSource = New BindingSource With {
    .DataSource = New BindingList(Of ItemCards2)(Cardex.ToList())
}

---

Another problematic point is casting the result of Dapper's Query method to a List(Of T). Depending on whether the command is buffered or not, Dapper will either return a true IEnumerable(Of T) with deferred execution or a List(Of T). Therefore it is better to use Dapper's .AsList() (not to be confused with LINQ's .ToList()) extension method which will create a new list only when necessary.

Using Connection = New OleDbConnection(CreateConnection())
    With Connection
        MasterItem = .Query(Of ItemCards2)(Qry_MST).AsList()
        PurchaseDetails = .Query(Of ItemCards2)(Qry_PI).AsList()
        SalesDetails = .Query(Of ItemCards2)(Qry_SI).AsList()
    End With
End Using

A side note: Since we have With Connection we can omit the word Connection inside the With-statement (this is the very point of the With-statement).

For informational purposes only, this is how AsList was implemented: (C#)

public static List<T> AsList<T>(this IEnumerable<T>? source) => source switch
{
    null => null!,
    List<T> list => list,
    _ => Enumerable.ToList(source),
};

This code is not directly convertible to VB because it uses a switch expression and pattern matching. In VB we would have to write:

<Extension()>
Public Function AsList(Of T)(source As IEnumerable(Of T)) As List(Of T)
    If IsNothing(source) Then
        Return Nothing
    ElseIf TypeOf source Is List(Of T) Then
        Return DirectCast(source, List(Of T))
    Else
        Return Enumerable.ToList(source)
    End If
End Function

Do not copy this code! It is for informational purposes only.

Answer 2

The problem reported by the error is here:

Select ITEM,
    PRODUCTIONNAME,
...

You are "selecting" an anonymous type that happens to have the same property names of the type you want. VB.NET does not support "duck typing" where you can convert from one type to another just because they have the same properties. You need to specify the type name if you want to use the defined type:

Select New ItemCards2 With {
    .ITEM = ITEM,
    .PRODUCTIONNAME = PRODUCTIONNAME,
...

Although I suspect you're going to have trouble converting the single ITEM result into the array that the type defines.

I would also recommend using the more standard PascalCase for your property names unless you're using it to interface with another API that requires all caps:

Select New ItemCards2 With {
    .Item = Item,
    .ProductionName = ProductionName,
...

Answer 3

Complete side note the question (so community wiki), but there are two changes I suggest to the code as is that will make it easier to read/maintain. First, remove the parentheses around the JOINs and change how you format it so the command (FROM, JOIN, APPLY, etc) to add another table always starts on a new line. The result will be the same (I promise), and the query will be much easier to read/understand. You can do a similar change for the SELECT clause, to avoid excessive text off to the right, so things fit on the screen better.

Second, VB.Net has multiline string literals now. This makes it easier to add the extra lines from the prior suggestion, and also makes it easier to do things like copy/paste the query to and from a tool like SSMS for testing and refinement.

Using Qry_PI as an example, you'd put it all together like this:

Const Qry_PI = "
SELECT a.ITEM, '' AS PRODUCTIONNAME, '' AS BRAND, 0 AS LSQ, 0 AS TPIQ
    , a.QTY AS PIQ, 0 AS TSIQ, 0 AS SIQ, 0 AS TRSQ, 0 AS RSQ, 0 AS TRPQ
    , 0 AS RPQ,  0 AS BLC 
FROM PurchaseDetails a 
INNER JOIN Purchase b ON a.INVONO = b.INVONO
INNER JOIN CUSTCODE c On b.CUSTCODE = c.CUSTCODE
WHERE b.DATEINVO >= #2024-01-10#
    AND c.GROUP IS NULL"