chrishendra93 chrishendra93 - 5 months ago 25
Vb.net Question

Performing aggregate sum on custom class IQueryable(Of T)

I've been trying to perform sum on vb.net using LINQ on a IQueryable of a custom class Aggregate_Table that I defined below. I'm not sure how to achieve this as the usual sum function gives me syntax error when applied to the IQueryable(Of T) of my custom class.

I started with the following query :

Dim initial_SIs = From si In database._58k_SIs
Group By si.From_Firm, si.From_Account_Number, si.To_Account_Number, si.To_Firm, si.Security_Code, si.Settlement_Ccy
Into Quantity = Sum(si.Quantity), Settlement_Amount = Sum(si.Settlement_Amount)


Which is a simple query from table _58k_SIs in datacontext object database. Since the type for this query result is anonymous data type, and I will need to create a function that takes in a specific table type, I went on to create a class name Aggregate_SI with the same attributes as the query result above .(Each of the attribute of this class has their own corresponding property that I omitted for brevity)

Public Class Aggregated_SI
Private _From_Firm As String
Private _From_Account_Number As String
Private _To_Account_Number As String
Private _To_Firm As String
Private _Security_Code As String
Private _Quantity As Integer
Private _Settlement_Amount As Decimal
Private _Settlement_Ccy As String

Public Property Quantity
Get
Return _Quantity
End Get
Set(value)
_Quantity = value
End Set
End Property
End Class


I created a list of Aggregated_SI object and transfer all the query result to that list since list(Of T) object can be used for LINQ query.

Dim test_List = New List(Of Aggregated_SI)
For Each si In initial_SIs
test_List.Add(New Aggregated_SI(si.From_Firm, si.From_Account_Number, si.To_Account_Number, si.To_Firm, si.Security_Code, si.Quantity, si.Settlement_Amount, si.Settlement_Ccy))
Next


The aggregation :

Dim outflow = From si In test_List
Group By si.From_Account_Number, si.Security_Code
Into Total_outflow = Sum(si.Quantity)


gives me an error message

Error BC36594 Definition of method 'Sum' is not accessible in this context.

Error BC30519 Overload resolution failed because no accessible 'Sum' can be called without a narrowing conversion:
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Integer)) As Integer' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Integer'.
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Integer?)) As Integer?' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Integer?'.
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Long)) As Long' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Long'.
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Long?)) As Long?' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Long?'.
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Single)) As Single' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Single'.
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Single?)) As Single?' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Single?'.
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Double)) As Double' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Double'.
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Double?)) As Double?' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Double?'.
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Decimal)) As Decimal' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Decimal'.
Extension method 'Public Function Sum(selector As Func(Of Aggregated_SI, Decimal?)) As Decimal?' defined in 'Enumerable': Return type of nested function matching parameter 'selector' narrows from 'Object' to 'Decimal?'. Settlement_Optimisation C:\Users\chris\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb 22 Active


I've been trying to work around this problem for a few hours without avail. Anyone has any suggestion on how can I approach this problem?

Answer

You've declared the Quantity property as Public Property Quantity instead of Public Property Quantity As Integer. This causes Quantity to cast the result to object and there is no Sum() extension that can summarize objects. Use the proper return type and it should work:

Public Class Aggregated_SI
   Private _From_Firm As String
   Private _From_Account_Number As String
   Private _To_Account_Number As String
   Private _To_Firm As String
   Private _Security_Code As String
   Private _Quantity As Integer
   Private _Settlement_Amount As Decimal
   Private _Settlement_Ccy As String

   Public Property Quantity As Integer
    Get
        Return _Quantity
    End Get
    Set(value As Integer)
        _Quantity = value
    End Set
    End Property
 End Class

PS: it's been a long time since I wrote VB.Net, I am not sure about including the type in the setter's value parameter, but you can remove it if compiler complains

Comments