2
\$\begingroup\$

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;
\$\endgroup\$
1
  • 3
    \$\begingroup\$ I'd rather do .UpVotes < .Downvotes. \$\endgroup\$
    – ANeves
    May 8 '12 at 13:14
4
\$\begingroup\$

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; 
\$\endgroup\$
3
  • \$\begingroup\$ 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! \$\endgroup\$
    – dormisher
    May 8 '12 at 15:03
  • \$\begingroup\$ 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! \$\endgroup\$
    – sara
    Jul 13 '16 at 18:15
  • \$\begingroup\$ 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. \$\endgroup\$ Jul 13 '16 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.