CODEWITHSUNDEEP
c#entity-frameworklinqmaster-detail
omarmallat
omarmallat

Reputation: 734

Sum of child of child in LINQ

I need help in LINQ query in EF6.

the master table is called EXAM. its child is ExamResult. each ExamResult has a Question and the selected Answer. each Answer has power (0 if wrong, 1 if correct). if I want to know the total of correct answers, I simply run the command:

var examTotal = db.Exams.FirstOrDefault(ex => ex.ExamID == examId).ExamResults.Sum(er => er.Answer.Power);

my problem is when some questions were not answered, and I get NullReferenceException.

Upvotes: 1

Views: 1322

Answers (4)

Ivan Stoev
Ivan Stoev

Reputation: 205629

There are a couple problems with your "command".

First off, there are at least two queries:

(1) var exam = db.Exams.FirstOrDefault(ex => ex.ExamID == examId);

(2) var examTotal = exam.ExamResults.Sum(er => er.Answer.Power);

Note that the second really executes inside the LINQ to Objects context (eventually including some hidden db calls due to lazy loading). In that context, there are 2 possible places that can raise NRE

(A) exam.ExamResults if exam is null
(B) er.Answer.Powerif er.Answer is null

You can fix them by including null checks as proposed in other answers.

But better way would be make your command execute a single query inside the LINQ to Entities context where navigation properties have different meaning:

var examTotal = db.Exams.Where(ex => ex.ExamID == examId)
    .SelectMany(ex => ex.ExamResults)
    .Sum(er => (int?)er.Answer.Power) ?? 0;

The only trick needed is to project the field you want to sum to a nullable type (I've used int? assuming the Power field is of int type, change it to your type if different). This is letting the EF to always return the Sum regardless of whether er.Answer is null or ex.ExamResults is empty.

Upvotes: 4

Shazi
Shazi

Reputation: 1569

Some general nullchecks should do the trick

var exam = db.Exams.FirstOrDefault(ex => ex.ExamID == examId);
var examTotal = exam.ExamResults.Sum(er => er.Answer?.Power ?? 0);

...and just in case you're not using C# 6, here's Another version of it:

var exam = db.Exams.FirstOrDefault(ex => ex.ExamID == examId);
var examTotal = exam.ExamResults.Sum(er => er.Answer != null ? er.Answer.Power : 0);

Upvotes: 1

Slava Utesinov
Slava Utesinov

Reputation: 13488

Try this:

var examTotal = db.Exams.FirstOrDefault(ex => ex.ExamID == examId).ExamResults.Where(er => er.Answer != null).Sum(er => er.Answer.Power);

Upvotes: 1

Fᴀʀʜᴀɴ Aɴᴀᴍ
Fᴀʀʜᴀɴ Aɴᴀᴍ

Reputation: 6251

This should work:

var examTotal = db.Exams.FirstOrDefault(ex => ex.ExamID == examId).ExamResults.Count(er => er.Answer.Power == 1);

This will not use the value but instead see if it is equal to 1, thus not generate any NullReferenceException.

Upvotes: 0

Related Questions