How do I retrieve only certain subobjects in LINQ?

Question

I have an entity object (Company) which has 1 or more subobjects (CompanyRevision) represented as a non-null FK relationship in the database. Using LINQ, I want to get all the Companies from the database, but I also only want the latest CompanyRevision for each company. This is how I do it today, but I have a feeling this could be done using one query.

IEnumerable<Company> companyList = from p in ctx.Company.Include("CompanyRevisions")
                                   select p;

foreach(Company c in companyList)
{
  CompanyRevision cr = (from p in c.CompanyRevisions
                        orderby p.Timestamp descending
                        select p).First();

  // Do something with c and cr...
}

As you can see, I would like to add this second LINQ query (the one that gets the latest CompanyRevision) into the first one, so that companyList[i].CompanyRevisions is basicly a list with just one entry (the latest one). I can't for the life of my figure out how to do this. Please help!

Thanks in advance

Answer 1

how about this: mixing the linq language and extension methods:

var results = from p in ctx.Company.Include("CompanyRevisions") 
select new {Company = p, 
            Revision = p.CompanyRevisions.OrderByDescending(cr => cr.Timestamp).First()
           }

Each result now has a Company and Revision member.

It's possible that you could also do this -

var results = from p in ctx.Company.Include("CompanyRevisions") 
select new {Company = p, 
            Revision = (from pcr in p.CompanyRevisions
                       orderby pcr.Timestamp descending
                       select pcr).First()
           }

To give the same results.

Although that's a guess - I haven't labbed that one out; but it's how I would try it first.