The sequence contains no elements - LINQ, MVC, Average

Question

I ran into this error. I see that the reason is that the average value returned at times is 0.00, which is accurate in terms of data. This SQL query works fine, but that's because it automatically puts 0.00.

LINQ complains, and so I tried using DefaultIfEmpty (), but it says it expects 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 an SQL query that does the same thing that I need VB.

 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) 

Answer 1

The problem is that all scalar LINQ methods (Average, Max, etc.) throw an exception if the IEnumerable(Of T) input does not contain any elements. It seems that calls to g.Where lead to an empty collection leading to an exception.

What you can do is write a method that handles an empty case and returns a default value.

Answer 2

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

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