Query and use dynamic/anonymous object from Dapper in VB.Net

.net dapper dynamic vb.net

Question

I have a query in which I perform complex calculations, including NULL handling and special cases for division by 0. This is a simplified example, provided only as a context for the problem :

SELECT
    CASE
        WHEN SUM(f.AmountA) > 0 AND SUM(f.AmountB) > 0 THEN --Perform calculations...
        WHEN SUM(f.AmountB) < 0 THEN --Perform calculations...
        WHEN SUM(f.AmountB) = 0 THEN --Perform calculations...
    END AS Result
FROM Foo f
JOIN Bar b ON b.BarID = f.BarID

To improve maintainability and readability, I would like to leave the calculations to be performed by .Net code. The query would be similar to this example :

SELECT SUM(f.AmountA) AS SumA, SUM(b.AmountB) AS SumB
FROM Foo f
JOIN Bar b ON b.BarID = f.BarID

Then, I would need to query those fields (with Dapper), which are not defined in a specific POCO. In C#, I could do something similar to what's done in this answer and its comments. However, in VB.Net with Option Strict On, I'm not able to do any of this.

To better describe what I'm trying to do, this is what it could look like, if it wasn't of the error Option Strict On disallows late binding on u.SumA and u.SumB within the New With statement. Note that I set Option Infer On at the top of my class :

Using connection As New SqlClient.SqlConnection(myConnectionString)

    Dim result = connection.Query(myQuery).Select(Function(u) New With {.SumA = u.SumA, .SumB = u.SumB}).FirstOrDefault()

    If result.SumA > 0 AndAlso result.SumB > 0 Then
        'Perform calculations...
    End If
    'Other cases...

End Using

This probably has to do with the lack of the dynamic type in VB. Therefore, the non-generic Query override returns an IEnumerable(Of Object) instead of its C# counterpart, IEnumerable<dynamic>.

My question is: Is there a way to do what I want to do with Dapper and VB.Net?

Popular Answer

I just wrote a simple (private/inner) data class, which has all the properties you need to map. I know it's sad, but thats vb.net...



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why