CODEWITHSUNDEEP
c#entity-frameworklinq
AdrianD
AdrianD

Reputation: 279

LINQ - get parent based on last child condition

I have the current entities:

Students (Id, Name,..)

Grades (Id, Student_Id, Grade)

A student can have more of no grades..

Using LINQ (Entity Framework) how can I get a list of all students that have the last grade different than 10!

I was trying: 

var students = db.students.Where(c => c.Grades.LastOrDefault().Grade != 10) .ToList();

The problem is since I may not have any grades, the LastOrDefault can be null.

Upvotes: 1

Views: 1396

Answers (3)

Gert Arnold
Gert Arnold

Reputation: 109119

Nobody seems to notice that Last(Ordefault) is not supported in LINQ to Entities. So you have to use another approach:

var students = db.students
                 .Where(c => c.Grades.OrderByDescending(g => g.Date)
                 .FirstOrDefault().Grade != 10).ToList();

I have to guess that there is some useful property by which you can order the Grades (a Date?). You don't want to depend on the order the database happens to produce them.

As said in a comment, null values don't throw exceptions in SQL, the language in which this expression is evaluated.

Upvotes: 3

Mauro
Mauro

Reputation: 2070

You can simply check for existence of Grades before calling Last() as in

var students = db.students
    .Where(c => c.Grades.Count() > 0 && c.Last().Grade != 10)
    .ToList()

Or as pointed out in the comment, you can take advantage of the Any() function

var students = db.students
    .Where(c => c.Grades.Any() && c.Last().Grade != 10)
    .ToList()

Upvotes: 1

Marcel B
Marcel B

Reputation: 518

If you are using C# 6 (.NET 4.6 or higher), you can use the new Elvis Operator .? together with the Null coalesing operator.

var students = db.students.Where(c => c.Grades.LastOrDefault()?.Grade != 10 ?? false).ToList();

Basically the elvis operator will return null, if LastOrDefault() is null, else it will return the value of Grade

Two other possible ways: Check if c.Grades has any values or to check if LastOrDefault() returns null. Way 1:

var students = db.students.Where(c => c.Grades.LastOrDefault() == null ? false : c.Grades.LastOrDefault().Grade != 10).ToList();

Way 2:

var students = db.students.Where(c => c.Grades.Any() && c.Grades.LastOrDefault().Grade != 10).ToList();

In all 3 ways the result for students is a list where every student has at least 1 grade where the last one is not 10.

Upvotes: 1

Related Questions