此查询的 LINQ 等效项

Question

SELECT StudentHistoryId, StudentId, Grade, CreatedAt, ModifiedAt, ModifiedBy, Active
FROM TABLENAME TN
     INNER JOIN ( SELECT StudentId, MAX(ModifiedAt) AS ModifiedAt FROM TABLENAME GROUP BY StudentId) M
          ON TN.StudentId = M.StudentId AND TN.ModifiedAt = M.ModifiedAt

Answer 1

这是一个直接的翻译：

var subquery = from tn in dc.TABLENAME
               group tn by tn.StudentId into g
               select new { StudentId = g.Key, ModifiedAt = g.Max(x => x.ModifiedAt) };
var query = from tn in dc.TABLENAME
            join m in subquery
                on new { tn.StudentId, tn.ModifiedAt }
                equals new { m.StudentId, m.ModifiedAt }
            select new
            {
                tn.StudentHistoryId,
                tn.StudentId,
                tn.Grade,
                tn.CreatedAt,
                tn.ModifiedAt,
                tn.ModifiedBy,
                tn.Active
            };