none
Linq Aggregate query to allow for null value in VB.NET RRS feed

  • Question

  • Hi All,

    I'm sure this is a pretty simple one, but I can't seem to find an answer anywhere...

    I'd like to perform a simple sum in VB.net using the Entity Framework, using:

    Dim TotalAmount As Nullable(Of Decimal) = (Aggregate t In ModelContext.SomeItems
                             Where t.ForeignID = 123
                             Into Sum(t.Price))
    

    This works fine if there are values, but it throws the following error when no rows are returned:

    The cast to value type 'Decimal' failed because the materialized value is null. 
    Either the result type's generic parameter or the query must use a nullable type.

    I've seen examples of how to get around this in C# using (decimal?) but obviously that doesn't work in VB.net. I've tried CType, IIF etc with no success.

    Any help would be greatly appreciated. Many Thanks.

    Thursday, September 2, 2010 8:37 AM

Answers

  • Hi,

     

    I finally get the workaround of this issue.  We can write the query like this:

    ===========================================================================

    Dim TotalAmount As Nullable(Of Decimal) = (Aggregate t In ModelContext.SomeItems

                                                                        Where t.ForeignID = 123

                                    Into Sum(CType(t.Price Integer?)))

    ===========================================================================

     

    Does it work for you?  

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 9, 2010 5:58 AM
    Moderator
  • Excellent, it works!

    Just needed to add the comma for the CType function:

    ===========================================================================
    
    Dim TotalAmount As Nullable(Of Decimal) = (Aggregate t In ModelContext.SomeItems
                          Where t.ForeignID = 123
                          Into Sum(CType(t.Price, Integer?)))
    ===========================================================================
    
    

    Thanks for your help.

    • Marked as answer by Bias Friday, September 10, 2010 6:49 AM
    Friday, September 10, 2010 6:49 AM

All replies

  • Hello,

     

    Welcome to EF forum!

     

    Is the Price property in type of Nullable(Of Decimal)?   If so, could you please use the If method? 

    ==================================================================================

    Dim TotalAmount As Nullable(Of Decimal) = (Aggregate t In ModelContext.SomeItems

                                    Where t.ForeignID = 123

                                    Into Sum(If(t.Price Is Nothing, 0, t.Price)))

    ==================================================================================

     

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 3, 2010 6:20 AM
    Moderator
  • Hi,

    No the Price property cannot be null - I believe the problem is that SQL Server returns a null value when no rows exist that match the criteria.

    So something like this is what I need:

    Dim TotalAmount As Nullable(Of Decimal) = (Aggregate t In ModelContext.SomeItems
                                    Where t.ForeignID = 123
                                    Into If(Sum(t.Price) Is Nothing, 0, Sum(t.Price)))

    Unfortunately the above example doesn't work. Any other ideas?

    Thanks again.

    Friday, September 3, 2010 7:30 AM
  • Hi,

     

    I will consult the product team to see if they have any ideas about this issue and get back to you as soon as possible. 

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, September 6, 2010 12:41 PM
    Moderator
  • Hi,

     

    I finally get the workaround of this issue.  We can write the query like this:

    ===========================================================================

    Dim TotalAmount As Nullable(Of Decimal) = (Aggregate t In ModelContext.SomeItems

                                                                        Where t.ForeignID = 123

                                    Into Sum(CType(t.Price Integer?)))

    ===========================================================================

     

    Does it work for you?  

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 9, 2010 5:58 AM
    Moderator
  • Excellent, it works!

    Just needed to add the comma for the CType function:

    ===========================================================================
    
    Dim TotalAmount As Nullable(Of Decimal) = (Aggregate t In ModelContext.SomeItems
                          Where t.ForeignID = 123
                          Into Sum(CType(t.Price, Integer?)))
    ===========================================================================
    
    

    Thanks for your help.

    • Marked as answer by Bias Friday, September 10, 2010 6:49 AM
    Friday, September 10, 2010 6:49 AM