c# - 使用 SUM 和 IS NOT NULL 对 LINQ for MVC 进行 SQL 查询

Question

有人可以帮我将此查询转换为 LINQ，因为我刚开始使用 Linq，然后它将用于 Google 图表信息。

Select Question.SubSectionName, SUM(Answers.RatingAnswer) AS Ratings 
FROM Question,Answers,Response,Section 
Where Answers.QuestionID = Question.QuestionID 
AND Answers.ResponseID = Response.ResponseID 
AND Question.SectionID=Section.SectionID 
AND Section.SectionID = 2 
AND Response.ResponseID = @0 
AND Question.SubSectionName IS NOT Null 
GROUP BY Question.SubSectionName;

到目前为止我得到了什么:

var submitted = (from ans in db.Answers join ques in db.Questions on 

ans.QuestionID equals ques.QuestionID 
join resp in db.Responses on ans.ResponseID equals resp.ResponseID 
join sec in db.Sections on ques.SectionID equals sec.SectionID 
where sec.SectionID == 2 && resp.ResponseID == model.ResponseID 
&& ques.SubSectionName!= null
select ques.SubSectionName && ans.RatingAnswer)

感谢您的帮助。

Answer 1

根据您的评论，这应该输出一组评级答案的总和:

var submitted =
    (from ans in db.Answers
        join ques in db.Questions on ans.QuestionId equals ques.QuestionId
        join resp in db.Responses on ans.ResponseId equals resp.ResponseId
        join sec in db.Sections on ques.SectionId equals sec.SectionId
        where sec.SectionId == 2 && resp.ResponseId == model.ResponseID && ques.SubSectionName != null
        select new { SubSectionName = ques.SubSectionName, RatingAnswer = ans.RatingAnswer })
    .GroupBy(a => a.SubSectionName)
    .Select(a => new { SectionName = a.Key, Sum = a.Sum(s => s.RatingAnswer) });

可能有一种更有效的写法。

我还要指出，对我来说，数据结构似乎有缺陷。也就是说，它似乎没有完全标准化或不正确。这当然需要你自己去锻炼。