How could I remove repetition from this linq-to-entities query?

Question

This linq query works, and it does what I want it to do, but is there any other way I could improve the query so I'm not repeating a.AnswerRevisions.OrderByDescending(r => r.DateCreated).FirstOrDefault()?

IQueryable<Article> query = from a in _db.Articles
 orderby a.DateCreated descending
 where a.AnswerRevisions.Count > 0 &&
 (a.AnswerRevisions.OrderByDescending(r => r.DateCreated).FirstOrDefault().UpVotes -
 a.AnswerRevisions.OrderByDescending(r => r.DateCreated).FirstOrDefault().DownVotes < 0)
 select a;

• ANeves: I'd rather do .UpVotes < .Downvotes.

Answer 1

Use the let statement and also do the final orderby after the where clause, this will increase efficiency, as less entries will have to be sorted

IQueryable<Article> query = from a in _db.Articles 
 let rev = a.AnswerRevisions.OrderByDescending(r => r.DateCreated).FirstOrDefault()
 where a.AnswerRevisions.Count > 0 && 
 (rev.UpVotes - rev.DownVotes < 0) 
 orderby a.DateCreated descending 
 select a; 

• dormisher: Ok thanks that's great - never used the let clause before, works really well in this situation. Kudos on the orderby too, I was under the impression that with linq-to-entities the orderby had to be before the where but I was wrong on that front!
• sara: I'm pretty sure the query generated by EF will account for the order of ordering/filtering, and if not, the underlying DB should when executing the query. I haven't actually tested it though, so don't quote me on that!
• Olivier Jacot-Descombes: It might make no difference with EF, but if you have linq-to-objects it will. In SQL the order by comes always after where. It feels better to have order by at the end.