Linq question on Linqpad Core API query

Question

This is more of a linq question, but it involves the Core API so I'll post it here. I'm running the linq query listed below via LinqPad. That query runs, but I want to add a Where clause to only return records where the ModifiedDate >PublishedDate. Where does it go? I can't seem to add one after the select, and also I can't refer to my column alias'. But if I add the Where before the select, don't I end up doing a Tridion.Read() multiple times?

What is the most efficient way of doing this?

Tridion
  .GetListXml("tcm:99-9999", new UsingItemsFilterData())
  .Elements()
  .Take(25)
  .Select (elm => new {
    ID=elm.Attribute("ID").Value,
    Title=elm.Attribute("Title").Value,
    ModifiedDate=Tridion.Read(elm.Attribute("ID").Value,new ReadOptions()).VersionInfo.RevisionDate,
    PublishedDate=Tridion.GetListPublishInfo(elm.Attribute("ID").Value).Select(pi => pi.PublishedAt).LastOrDefault()==DateTime.MinValue? "":Tridion.GetListPublishInfo(elm.Attribute("ID").Value).Select(pi => pi.PublishedAt).LastOrDefault().ToString(),
  })

Answer 1

What you're doing is already very heavy-weight: you're doing a full read plus 2 times GetListPublishInfo for each item in the list (well, fortunately only on the first 25 items).

I would recommend to not try to do so much in a single LINQ query. Determining whether an item is published or not can be done based on the list data (IsPublished attribute; specify ListBaseColumns.Extended to get that attribute in the list data). Retrieving the modification date should be done in bulk using RepositoryLocalObjectsFilterData with ItemIds property set.