时间戳

首页 > 解决方案 > 带有子查询的 SQL Server 查询到 LINQ 查询

c# - 带有子查询的 SQL Server 查询到 LINQ 查询

问题描述

我编写了一个 SQL 查询，它将获取票数、已关闭票数及其关闭率 (%) 并按月（当年）对其进行分组，但我想将其表示为 LINQ 查询以达到相同的结果。

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));

这是我正在处理的代码：

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();

我有 rtouble 试图以 linq 格式表示已关闭票证( ClosedCount) 的计数，我需要计数来计算ClosureRate.

标签： c#sql-serverlinqentity-framework-core

解决方案

这不会是相同的 SQL，但它应该在内存中产生相同的结果：

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();

这里实现了两种技术：

  1. 使用Fluent Query指定过滤器以应用 ClosedCount 集，您可以将Fluent和Query语法结合到您的内心深处，它们各有优缺点，在这种情况下，它只是简化了语法来做到这一点。

  2. 将数据库查询集中在只带回您需要的数据上，其余的可以在初始数据库执行后在 member 中轻松计算。这就是为什么这里有2个投影，第一个应该纯粹用SQL表达，其余的被评估为Linq to Objects

一般假设是，网络上的流量和序列化通常是此类简单查询的瓶颈，因此我们强制Linq to Entities（或 Linq to SQL）生成实用的最小有效负载并构建其余部分或值和内存中的计算。

更新：

Svyatoslav Danyliv 在这个答案中提出了一个非常好的观点

从 SQL 和 LINQ 的角度来看，可以通过在返回or上使用CASE表达式来简化逻辑，然后我们可以简单地对该列求和，这意味着您可以避免嵌套查询并且可以简单地连接结果。TicketStatus10

推荐阅读