c# - 如何在 LINQ 和 C# 中使用 LEFT JOIN、GROUP BY 和 SUM

Question

我有 4 个表（Group、Student、Mark、StudentFile，并且想在 C# 中使用 LINQ 编写我的 SQL 查询。

这是我的查询

SELECT
    S.Id,
    S.FirstName,
    S.LastName,
    S.MiddleName,
    SUM(M.CountOfPasses) CountOfPasses,
    (SELECT SUM(CountOfPassesWithARespectful) FROM StudentFile WHERE StudentId = S.Id) WithARespectful
FROM Student S
     LEFT JOIN Mark M ON S.Id = M.StudentId
GROUP BY S.Id, S.FirstName, S.LastName, S.MiddleName

我已经尝试过这样的事情：

var  students = (from G in context.Group
                        let v = G.Id
                        from S in context.Student.Where(x => v == x.GroupId)
                        from F in context.StudentFile.Where(x => x.StudentId == S.Id).DefaultIfEmpty()
                        from M in context.Mark.Where(x => x.StudentId == S.Id).DefaultIfEmpty()                              
                        group new
                        {                                
                            F.CountOfPassesWithArespectful,
                            M.CountOfPasses,
                            S.Id
                        }
                        by new
                        {
                            S.Id,
                            S.FirstName,
                            S.LastName,
                            S.MiddleName,
                            S.StartCourse,
                            G.Name,
                            S.Alphagroup                               
                        } into GSF
                        select new DTOStudent
                        {
                            Id = GSF.Key.Id,
                            FirstName = GSF.Key.FirstName,
                            LastName = GSF.Key.LastName,
                            MiddleName = GSF.Key.MiddleName,
                            CountOfPasses = (int)GSF.Sum(p=>p.CountOfPasses),
                            WithRespectful = (int)GSF.Sum(x => x.CountOfPassesWithArespectful),
                            WithOutRespectful = (int)GSF.Sum(x => x.CountOfPasses) - (int)GSF.Sum(x => x.CountOfPassesWithArespectful),
                            Course = ClassMethods.GetAgeFromDates((DateTime)GSF.Key.StartCourse).ToString() +
                                                 ClassMethods.GetShortGroupName(GSF.Key.Name) + GSF.Key.Alphagroup.ToUpper()
                        }).Distinct().ToList();

标签： c#sqllinq