How to use translate Max(value) and Group By (Value/100) in VB LINQ?

61 Views Asked by At

I'm currently stuck trying to translate a "simple" SQL Query to a LINQ Query in VB.

Here's my Table :

    SELECT ID_FRUIT, MAL, numero 
    FROM MyTable
    where ID_SITE = 9 AND ID_DRT = 106

    ID_FRUIT    MAL     numero
    9           106     100
    9           106     201
    9           106     202
    9           106     203
    9           106     204
    9           106     205
    9           106     206
    9           106     207
    9           106     208
    9           106     209
    9           106     300
    9           106     301
    9           106     302
    9           106     303
    9           106     304
    9           106     305
    9           106     306
    9           106     307
    9           106     200
    9           106     200
    9           106     400
    9           106     401
    9           106     402
    9           106     403

My goal is to retrieve the largest number from each hundred (column numero).

Here's my SQL Query :

SELECT MAX(NUMERO) FROM MyTable WHERE ID_FRUIT = 9 AND MAL = 106 GROUP BY NUMERO/100

ID_FRUIT, MAL and NUMERO are 3 integer non nullable. Result :

    100
    209
    307
    403

Now I'm trying to code it in VB, I've got something like that, I think I'm not that far but still not close :

    Public Function SelectionNumero(ID_FRUIT As Integer) As List(Of Integer)
            Dim MAL As Integer = 106
            Return db.MyTable.Where(Function(s) s.ID_FRUIT = ID_FRUIT).GroupBy(Function(s) s.Numero / 100).Select(Function(s) New With {
                Key .numero = s.Max(Function(m) m.Numero)
                }).ToList()
        End Function

I'm currently missing a WHERE clause, and I got the error ID 'BC30311' : Value of type 'List(Of )' cannot be converted to 'List(Of Integer)'.

Thanks a lot for your help !

0

There are 0 best solutions below