Linq选择最大值数据集

debugcn 发表于 Dev

阿南德

我有3个与主要和外国概念相关的表。

模型-> studentRecord

public class studentRecord
{
    public string studentName{ get; set; }
    public int year{ get; set; }
}

表1->学生

studentId  studentName
----------------------
 1             Sam
 2             Mani
 3             rajah

表2->主题

subjectid    subjectName
------------------------
  1            english
  2             maths
  3            physics

表3->寄存器

 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)

我收到错误消息。有没有表现良好的方法。提前致谢

丹尼尔·杜谢克（DanielDušek）

已经在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();
}

分析器显示以下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等中

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。