I needed a list of 5 students ordered by their upcoming birthdays. To make it a little harder, there is a Student table and a Person table where data such as birthday or name are stored. The query I came up with looks like this:
// upcoming birthdays
var geburtstage = (
from student in DbContext.Student
join person in DbContext.Person on student.PersonId equals person.PersonId
orderby
(new DateTime(DateTime.Today.Year, person.Geburtsdatum.Month, person.Geburtsdatum.Day) > DateTime.Now ? 1 : 0) descending,
(new DateTime(DateTime.Today.Year, person.Geburtsdatum.Month, person.Geburtsdatum.Day) > DateTime.Now) ? new DateTime(DateTime.Today.Year, person.Geburtsdatum.Month, person.Geburtsdatum.Day) : new DateTime(DateTime.Today.Year + 1, person.Geburtsdatum.Month, person.Geburtsdatum.Day) ascending
select new {
Geburtsdatum = (new DateTime(DateTime.Today.Year, person.Geburtsdatum.Month, person.Geburtsdatum.Day) > DateTime.Now) ? new DateTime(DateTime.Today.Year, person.Geburtsdatum.Month, person.Geburtsdatum.Day) : new DateTime(DateTime.Today.Year + 1, person.Geburtsdatum.Month, person.Geburtsdatum.Day),
student.StudentId,
person.PersonId,
person.Firstname,
person.Lastname
}
).Take(5);