SQL Server query with subquery to LINQ query

Question

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.

Hi @stuartd, Sorry I am new to LINQ but I just want get the count of 'closed tickets' in linq format. I'm able to get my desired output in SQL query but I cannot figure out how to write this in linq. — LeonardDM
– LeonardDM, Commented Mar 23, 2021 at 2:21
What is the output from the current query and what are you not happy with? - Some example records and values would help paint the picture, but overall this doesn't seem to hard — Chris Schaller
– Chris Schaller, Commented Mar 23, 2021 at 2:49

Chris Schaller · Accepted Answer · 2021-03-23 06:30:30Z

This won't be the same SQL but it should produce the same result in memory:

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

Two techniques have been implemented here:

Use Fluent Query to specify the filter to apply for the ClosedCount set, you can combine Fluent and Query syntax to your hearts content, they each have pros and cons, in this instance it just simplifed the syntax to do it this way.
Focus the DB query on only bringing back the data that you need, the rest can be easily calculated in member after the initial DB execution. That is why there are 2 projections here, the first should be expressed purely in SQL, the rest is evaluated as Linq to Objects

The general assumption is that traffic over the wire and serialization are generally the bottle necks for simple queries like this, so we force Linq to Entities (or Linq to SQL) to produce the smallest payload that is practical and build the rest or the values and calculations in memory.

UPDATE:

Svyatoslav Danyliv makes a really good point in this answer

The logic can be simplified, from both an SQL and LINQ perspective by using a CASE expression on the TicketStatus to return 1 or 0 and then we can simply sum that column, which means you can avoid a nested query and can simply join on the results.

Thank you Chris! It worked. Earlier I'm getting a error saying Where.(.... The LINQ expression... could not be translated (maybe a bug in EF Core 3.1) but placing .AsEnumerable() after the first where clause fixed it. Thank you again!

Svyatoslav Danyliv · Accepted Answer · 2021-03-23 05:57:30Z

1

Original query can be simplified to this one:

SELECT *,
       (ClosedCount * 100 / TicketCount) AS ClosureRate
FROM (
         SELECT
             COUNT(Id)                               AS TicketCount,
             MONTH(InsertDate)                       AS MonthNumber,
             DATENAME(MONTH, E1.InsertDate)          AS MonthName,
             SUM(CASE WHEN TicketStatus = 'CLOSED' THEN 1 ELSE 0 END) AS 'ClosedCount'
         FROM EvaluationHistoryTable E1
         WHERE YEAR(E1.InsertDate) = 2021
         GROUP BY MONTH(InsertDate), DATENAME(MONTH, E1.InsertDate));

Which is easily convertible to server-side LINQ:

var grouped =
    from eh in _context.EvaluationHistoryTable
    where eh.InsertDate.Value.Year == DateTime.Now.Year
    group eh by new { eh.InsertDate.Value.Month }
    select new 
    {
        g.Key.Month,
        ItemCount = g.Count(),
        ClosedCount = g.Sum(t => t.TicketStatus == "CLOSED" ? 1 : 0)
    };

var query = 
    from x in grouped
    orderby x.Month
    select new YtdTicketClosureRateModel
    {
        MonthName = DateTimeFormatInfo.CurrentInfo.GetAbbreviatedMonthName(x.Month),
        MonthNumber = x.Month,
        ItemCount = x.ItemCount,
        ClosedCount = x.ClosedCount,
        ClosureRate = x.ClosedCount * 100D / x.ItemCount
    };

var result = query.ToList();

answered Mar 23, 2021 at 5:57

Svyatoslav Danyliv

28.3k4 gold badges24 silver badges46 bronze badges

1 Comment

LeonardDM Over a year ago

Thank you. It is good see a different approach to write a linq code. :)

Collectives™ on Stack Overflow

SQL Server query with subquery to LINQ query

2 Answers 2

UPDATE:

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATE:

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related