如何翻译这个Queryable linq函数

Question

我正在努力尝试在正确的T-SQL函数中生成这个LINQ函数。

请检查以下句子:

// determine the max count of exams applied by students
IQueryable query = (from at in Database.Current.AnsweredTests
                    where at.TestId == id
                    group at by at.StudentId into s
                    select s.Count()).Max();

可以看到这个函数在语法上是错误的，因为Max扩展返回int。所以我想要完成的是生成一个正确的T-SQL。

像这样:

MAX(SELECT x.COUNT()
FROM...
GROUP BY StudentId)

我这样做只是因为我想要一个好的性能，这是执行一个低性能。所以我的问题是我如何用MAX和COUNT这样的聚合函数写一个正确的LINQ句子。

更新:

SELECT [GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    [Extent1].[StudentId] AS [K1], 
    COUNT(1) AS [A1]
    FROM [dbo].[AnsweredTests] AS [Extent1]
    WHERE  CAST( [Extent1].[TestId] AS int) = @p__linq__0
    GROUP BY [Extent1].[StudentId]
)  AS [GroupBy1]

这就是生成IQueryable(当然，如果我删除了max扩展)。我想知道是否有一种方法可以将聚合函数MAX包含在T-SQL查询中，以提高服务器端的性能。

Answer 1

你也可以用下面的方式表达你的查询:

SELECT TOP 1 COUNT(*)
FROM AnsweredTests
WHERE TestId = @id
GROUP BY StudentId
ORDER BY COUNT(*) DESC

按照这个逻辑，这个(未经测试)应该是你要找的:

var result = (from at in Database.Current.AnsweredTests
              where at.TestId == id
              group at by at.StudentId into s
              orderby s.Count() descending
              select s.Count()).First()

Answer 2

您可以先执行ORDER BY DESCENDING，然后再执行

var Max = (from at in Database.Current.AnsweredTests
           where at.TestId == id
           group at by at.StudentId into s
           select new { Count = s.Count() }).OrderByDescending(o=>o.Count).First();