从SQL到C＃LINQ

  • Z时代
  • 2024-01-10
  • 分类：问答

我在将SQL翻译为LINQ时出现了一些问题，恰恰与Group by和Average混合在一起。我试着用不同的方式解决它，但没有预期的结果。从SQL到C＃LINQ

SQL：

SELECT TOP 3 
    AVG([Grade]) 
    ,[User].FirstName 
    ,[User].Surname 
    ,[Student].StudentID 
    ,[Student].ClassID 
FROM [Szkola].[dbo].[School_Class] 
JOIN [Szkola].[dbo].[Student] 
ON School_Class.ClassID = Student.ClassID 
JOIN [Szkola].[dbo].[School_Grades] 
ON Student.StudentID = School_Grades.StudentID 
JOIN [Szkola].[dbo].[Teacher] 
ON Teacher.TeacherID = School_Grades.TeacherID 
JOIN [Szkola].[dbo].[User] 
ON Student.UserID = [User].UserID 
WHERE [Teacher].[Subject] = 'Mathematics' 
GROUP BY [Student].[StudentID], [Student].[ClassID], [User].[FirstName], 
[User].[Surname] 
ORDER BY AVG([Grade]) DESC

你能还描述了如何准确适用您的解决方案？我阅读了LINQ和Group by，但我想我会更好地理解这个例子。

非常感谢。

回答：

使用类来模拟数据库

class Program 
    { 
     static void Main(string[] args) 
     { 
      List<School_Class> classes = new List<School_Class>(); 
      List<Student> students = new List<Student>(); 
      List<School_Grade> grades = new List<School_Grade>(); 
      List<Teacher> teachers = new List<Teacher>(); 
      List<User> users = new List<User>(); 
      var query = (from c in classes 
         join s in students on c.ClassID equals s.ClassID 
         join g in grades on s.StudentID equals g.StudentID 
         join t in teachers on s.StudentID equals t.TeacherID 
         join u in users on s.UserID equals u.UserID 
         select new { c = c, s = s, g = g, t = t, u = u }) 
       .Where(x => x.t.Subject == "Mathematics") 
       .GroupBy(x => new { 
        student = x.s.StudentID, 
        _class = x.c.ClassID, 
        firstname = x.u.FirstName, 
        surname = x.u.SurName }) 
       .OrderByDescending(x => x.FirstOrDefault().g.grades.Average()) 
       .Select(x => new { firstname = x.Key.firstname, 
            surname = x.Key.surname, 
            sid = x.Key.student, 
            cid = x.Key._class, 
            avg = x.FirstOrDefault().g.grades.Average() }) 
       .Take(3).ToList(); 
     } 
    } 
    public class School_Class 
    { 
     public int ClassID { get; set; } 
    } 
    public class Student 
    { 
     public int ClassID { get; set; } 
     public int StudentID { get; set; } 
     public int UserID { get; set; } 
    } 
    public class School_Grade 
    { 
     public int StudentID { get; set; } 
     public int TeacherID { get; set; } 
     public List<int> grades { get; set; } 
    } 
    public class Teacher 
    { 
     public int TeacherID { get; set; } 
     public string Subject { get; set; } 
    } 
    public class User 
    { 
     public string FirstName { get; set; } 
     public string SurName { get; set; } 
     public int UserID { get; set; } 
    }

以上是 从SQL到C＃LINQ 的全部内容， 来源链接： utcz.com/qa/257539.html

回到顶部