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 !