Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sequence contains no elements - LINQ, MVC, Average

I am running into this error. I see that the reason is because the average returned at times is 0.00 which from a data stand point is accurate. This SQL query works fine, but that is because it puts in 0.00 automatically.

LINQ complains and so I tried using DefaultIfEmpty() but it says it is expecting my ViewModel.

Dim ticketCounts = From t In queue _
   Where _
    (t.StatusId = 2) And _
    (t.CloseDate.Year = Convert.ToDateTime(DateTime.Now).Year) And _
    (t.ResolutionDays > 0)
   Group t By _
    Column1 = CType(t.CloseDate.Month, Integer), _
    Column2 = CType(t.CloseDate.ToString("MMMM"), String) _
    Into g = Group _
   Order By Column1 _
   Select _
    Id = Column1, _
    Month = Column2, _
    Critical = g.Where(Function(t) t.PriorityId = 1).DefaultIfEmpty().Average(Function(t) t.ResolutionDays), _
    High = g.Where(Function(t) t.PriorityId = 2).DefaultIfEmpty().Average(Function(t) t.ResolutionDays), _
    Normal = g.Where(Function(t) t.PriorityId = 3).DefaultIfEmpty().Average(Function(t) t.ResolutionDays), _
    Low = g.Where(Function(t) t.PriorityId = 4).DefaultIfEmpty().Average(Function(t) t.ResolutionDays), _
    Total = g.Where(Function(t) t.Id <> Nothing).DefaultIfEmpty().Average(Function(t) t.ResolutionDays)

UPDATED! This is the SQL query doing the same thing I need VB to do.

SELECT
    DATENAME(MONTH,t.CloseDate) AS 'Month',
    AVG(CASE WHEN (t.PriorityId = 1) THEN CAST(t.ResolutionDays AS Decimal(18, 2)) ELSE 0 END) AS 'Critical',
    AVG(CASE WHEN (t.PriorityId = 2) THEN CAST(t.ResolutionDays AS Decimal(18, 2)) ELSE 0 END) AS 'High',
    AVG(CASE WHEN (t.PriorityId = 3) THEN CAST(t.ResolutionDays AS Decimal(18, 2)) ELSE 0 END) AS 'Normal',
    AVG(CASE WHEN (t.PriorityId = 4) THEN CAST(t.ResolutionDays AS Decimal(18, 2)) ELSE 0 END) AS 'Low',
    AVG(CAST(t.ResolutionDays AS Decimal(18, 2))) AS 'Monthly Average'
FROM
    tblMaintenanceTicket t
WHERE
    t.StatusId = 2
    AND YEAR(t.CloseDate) = year(getdate())
GROUP BY 
    MONTH(t.CloseDate),
    DATENAME(MONTH,t.CloseDate)
ORDER BY
    MONTH(t.CloseDate)
like image 285
Charlie Avatar asked Nov 08 '12 21:11

Charlie


2 Answers

Critical = g.Where(Function(t) t.PriorityId = 1).Select(Function(t) t.ResolutionDays).DefaultIfEmpty().Average()

Reference: http://geekswithblogs.net/SoftwareDoneRight/archive/2011/02/15/fixing-linq-error-sequence-contains-no-elements.aspx

like image 151
Lachlan Ennis Avatar answered Sep 27 '22 21:09

Lachlan Ennis


The problem is that all of the scalar LINQ methods (Average, Max, etc ...) throw an exception if the input IEnumerable(Of T) doesn't have any elements. It looks like the g.Where calls are resulting in an empty collection resulting in the exception.

What you may want to do is write a method which handles the empty case and returns a default value.

like image 41
JaredPar Avatar answered Sep 27 '22 20:09

JaredPar