SQL Server query with subquery to LINQ query

I wrote a SQL query that will get the count of tickets, closed tickets and its closure rate (%) and group it monthly basis (current year), but I would like to express this as a LINQ query to achieve the same result.

SELECT *, (ClosedCount * 100 / TicketCount) AS ClosureRate  FROM (  SELECT COUNT(Id) as TicketCount, MONTH(InsertDate) as MonthNumber, DATENAME(MONTH, E1.InsertDate) as MonthName,      (SELECT COUNT(Id) FROM EvaluationHistoryTable E2 WHERE TicketStatus = 'CLOSED' AND YEAR(E2.InsertDate) = '2021') AS 'ClosedCount'  FROM EvaluationHistoryTable E1  WHERE YEAR(E1.InsertDate) = 2021  GROUP BY MONTH(InsertDate), DATENAME(MONTH, E1.InsertDate));  

This is code that I'm working on:

var ytdClosureRateData = _context.EvaluationHistoryTable  .Where(t => t.InsertDate.Value.Year == DateTime.Now.Year)  .GroupBy(m => new  {      Month = m.InsertDate.Value.Month  })  .Select(g => new YtdTicketClosureRateModel  {      MonthName = DateTimeFormatInfo.CurrentInfo.GetAbbreviatedMonthName(g.Key.Month),      MonthNumber = g.Key.Month,      ItemCount = g.Count(),      ClosedCount = // problem      ClosureRate = // problem  }).AsEnumerable()     .OrderBy(a => a.MonthNumber)     .ToList();  

I am having rtouble trying to express the count of closed tickets (ClosedCount) in linq format, I need the count to calculate the ClosureRate.

https://stackoverflow.com/questions/66756178/sql-server-query-with-subquery-to-linq-query March 23, 2021 at 09:52AM