Linq选择最大值数据集
本文关键字:数据集 最大值 选择 Linq | 更新日期: 2023-09-27 18:17:30
我有三个表与primary和foreign概念相关。
model -> studentRecord
public class studentRecord
{
public string studentName{ get; set; }
public int year{ get; set; }
}
Table 1 -> student
studentId studentName
----------------------
1 Sam
2 Mani
3 rajah
表2 -> subject
subjectid subjectName
------------------------
1 english
2 maths
3 physics
表3 -> register
registerId studentId subjectid Year
--------------------------------------------
1 1 1 1
2 1 2 1
3 1 3 1
4 1 1 2
5 1 2 2
6 1 3 2
我想要这个学生第二年的成绩。
我的linq代码
var op = (from student in db.student.where(x => x.studentId == 1)
join register in db.register
on student.studentId equals register.studentId
select new studentRecord{studentName = student.studentName, year = register.Year}).ToList<studentRecord>().Max(x => x.Year)
我得到错误。有没有什么方法能有好的效果?提前感谢
已经在Linqpad中测试了查询,下面的查询对我来说工作得很好。
void Main()
{
var op = (from student in Students.Where(x => x.StudentId == 1)
join register in Registers
on student.StudentId equals register.StudentId
select new {student = student.StudentName, Year = register.Year})
.Max(x => x.Year);
op.Dump();
}
和profiler显示以下SQL。
exec sp_executesql N'SELECT MAX([t1].[Year]) AS [value]
FROM [student] AS [t0]
INNER JOIN [register] AS [t1] ON [t0].[studentId] = [t1].[studentId]
WHERE [t0].[studentId] = @p0',N'@p0 int',@p0=1
无论如何,你可能想要的是:
from student in Students.Where(s => s.StudentId == 1)
join register in Registers.Where(r => r.Year == Registers.Max(x => x.Year))
on student.StudentId equals register.StudentId
select new studentRecord
{
studentName = student.StudentName,
year = register.Year
})
.ToList<studentRecord>();
Students
在您的代码db.student
等