Wednesday, August 27, 2008

LINQ Aggregates – Max() and Min() with no results from Where clause

I started getting the error “Sequence contains no elements” today and tracked the issue down to a LINQ statement in my code. It turns out that this was coming from the Max aggregate function that was operating on a filtered row set that had no matches.
Here is a simplified recreation of the problem I was having.
Module Module1
    Private Structure test
        Public Key As String
        Public Value As Integer
    End Structure

    Sub Main()
        Dim myList As New List(Of test)
        myList.Add(New test() With {.Key = "A", .Value = 1})
        myList.Add(New test() With {.Key = "A", .Value = 2})
        myList.Add(New test() With {.Key = "A", .Value = 3})
        myList.Add(New test() With {.Key = "B", .Value = 1})
        myList.Add(New test() With {.Key = "B", .Value = 2})
        myList.Add(New test() With {.Key = "C", .Value = 1})

        'Get Max Value of the A values -- Res1 is set to 3
        Dim Res1 = Aggregate t In myList Where t.Key = "A" Into Max(t.Value)

        'Get Max Value of the D value, which doesn't exist in the data collection
        Dim Res2 = Aggregate t In myList Where t.Key = "D" Into Max(t.Value)
        'This will fail with "Sequence contains no elements" error

        'Yet the SUM aggregate function works without error and sets Res3 to 0
        Dim Res3 = Aggregate t In myList Where t.Key = "D" Into Sum(t.Value)
    End Sub
End Module
As you can see on Res2 the “Sequence contains no elements” error occurs because the WHERE clause eliminates any rows for the Max function to process. Also this error occurs with the Min and Average functions as well. However other Aggregate functions such as Any, All, and surprisingly Sum all work without complaining. The Sum function just returns a 0 if there are no values. I wish Min and Max did the same.
My initial fix was to abandon the Max function and just sort the results descending and take the first result.
Module Module2 'Forget Max and just sort results and take first value
    Private Structure test
        Public Key As String
        Public Value As Integer
    End Structure

    Sub Main()
        Dim myList As New List(Of test)
        myList.Add(New test() With {.Key = "A", .Value = 1})
        myList.Add(New test() With {.Key = "A", .Value = 2})
        myList.Add(New test() With {.Key = "A", .Value = 3})
        myList.Add(New test() With {.Key = "B", .Value = 1})
        myList.Add(New test() With {.Key = "B", .Value = 2})
        myList.Add(New test() With {.Key = "C", .Value = 1})

        'Create a blank list for testing
        Dim blankList As New List(Of test)

        'Res1 will be 3
        Dim Res1 = (From t In myList Where t.Key = "A" Order By t.Value Descending Select t.Value).FirstOrDefault()

        'Res2 will be 0 because there is no "D" key
        Dim Res2 = (From t In myList Where t.Key = "D" Order By t.Value Descending Select t.Value).FirstOrDefault()

        'Res3 will be 0 because the list is blank
        Dim Res3 = (From t In blankList Where t.Key = "A" Order By t.Value Descending Select t.Value).FirstOrDefault()

        'Rewrite Res2 example using the Take clause.
        'Res4 will be 0 because there is no "D" key
        Dim Res4 = (From t In myList Where t.Key = "D" Order By t.Value Descending Take 1 Select t.Value).SingleOrDefault()
    End Sub
End Module
But then I thought there has got to be a better way. How about calling the Max function directly and controlling what gets passed by using a Lambda expression?
Module Module3 'Calling Max directly with Lambda Expressions
    Private Structure test
        Public Key As String
        Public Value As Integer
    End Structure

    Sub Main()
        Dim myList As New List(Of test)
        myList.Add(New test() With {.Key = "A", .Value = 1})
        myList.Add(New test() With {.Key = "A", .Value = 2})
        myList.Add(New test() With {.Key = "A", .Value = 3})
        myList.Add(New test() With {.Key = "B", .Value = 1})
        myList.Add(New test() With {.Key = "B", .Value = 2})
        myList.Add(New test() With {.Key = "C", .Value = 1})

        'Create a blank list for testing
        Dim blankList As New List(Of test)

        'Res1 will be 0
        Dim Res1 = myList.Max(Function(p As test) If(p.Key = "D", p.Value, 0))

        'Res2 will error on empty list with "Sequence contains no elements"
        Dim Res2 = blankList.Max(Function(p As test) If(p.Key = "D", p.Value, 0))

        'Res3 will be nothing since list is blank and Lamda returns nullable integer type
        Dim Res3 = blankList.Max(Function(p As test) If(p.Key = "D", p.Value, New Integer?(0)))
    End Sub
End Module
But this still was kind of sticky if the function was called with an empty list. I noticed that the Max function is overloaded and can take a Nullable integer type. I created Res3 in the above example but then I thought about making a full circle back to where I started from. So here is my final example and what I based my fix on.
Module Module4 'Take 2 on Aggregate clause by forcing null values
    Private Structure test
        Public Key As String
        Public Value As Integer
    End Structure

    Sub Main()
        Dim myList As New List(Of test)
        myList.Add(New test() With {.Key = "A", .Value = 1})
        myList.Add(New test() With {.Key = "A", .Value = 2})
        myList.Add(New test() With {.Key = "A", .Value = 3})
        myList.Add(New test() With {.Key = "B", .Value = 1})
        myList.Add(New test() With {.Key = "B", .Value = 2})
        myList.Add(New test() With {.Key = "C", .Value = 1})

        'Create a blank list for testing
        Dim blankList As New List(Of test)

        'Res1 will be nothing since D does not exist and the overloaded
        '  Max function is called with a Nullable integer type.
        Dim Res1 = Aggregate t In myList Where t.Key = "D" Into Max(New Integer?(t.Value))

        'Same example but forcing the Integer Default value of 0
        'Res2 will be 0
        Dim Res2 = (Aggregate t In myList Where t.Key = "D" Into Max(New Integer?(t.Value))).GetValueOrDefault

        'And calling on an empty list works too!
        Dim Res3 = (Aggregate t In blankList Where t.Key = "D" Into Max(New Integer?(t.Value))).GetValueOrDefault


    End Sub
So as you can see, forcing the call to Max that processes the Null integer type looks to be the best answer.

2 comments:

Anonymous said...

I had the same exact problems. Thanks for your BLOG entry. My problem is now solved. Cheers!

Sidhu said...

I am using slightly different version of MAX, how can i make it work

Dim MaxProgramgross As Decimal = Aggregate MaxVal In Ctx.LLW_T_CAP_ACTIVITY_ESTIMATEs Where MaxVal.C_ACTIVITY_NAME.Contains("R30") AndAlso MaxVal.IS_SELECTED_SCHEME = "Y" AndAlso (MaxVal.I_DESIGN_GROSS.HasValue OrElse MaxVal.I_OTHER_CONSTRUCTION_COST.HasValue) Into Max(MaxVal.I_OTHER_CONSTRUCTION_COST.Value + MaxVal.I_DESIGN_GROSS.Value)