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:
I had the same exact problems. Thanks for your BLOG entry. My problem is now solved. Cheers!
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)
Post a Comment