How to return first object of a collection from its parent

Question

I am looking to do the following with a single database query if possible.

public class Location
{
    public string URL {get;set;}
    public IList<Page> Pages {get;set;}
}

Page firstPage = Session.Linq<Location>()
       .Where(location => location.URL == "some-location-url")
       .Select(location => location.Pages).FirstOrDefault();

My aim is based on the current location url, return the first Page object from its pages collection.

I have tried a number of different ways now and they all seem to execute many queries to get the desired Page object out.

Any help is appreciated!

Dave the Ninja

Answer 1

This might be what your looking for:

Page firstPage = Session.Linq<Page>()
.OrderBy(page => page.Index)
.FirstOrDefault(page=> page.Location.URL == "some-location-url");

I'm making the assumption that the page has a Location property that relates back to the Location it belongs to and the .Index would be the property you want to order with.

Answer 2

Run the query against Page instead of against Location: you don't need to return the Location record at all.

(from p in AllPages
where p.Location.URL == "some-location-url"
select p).FirstOrDefault();

[Almost always when I get stuck with writing a LINQ query, I find it helps to start building the query from the bottom-most object in the parent-child relationships involved.]

Answer 3

Could you not add a order by directly to the mapping of the collection. You could then do

Page firstPage = Session.Linq<Location>()
              .Where(location => location.URL == "some-location-url")
              .Select(location => location.Pages.FirstOrDefault()).FirstOrDefault();

The FirstOrDefault may stop NHibernate making all the selects.

No Guarantee on this as I haven't got linq to nhibernate set up to test it.