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

Chris Canal · Accepted Answer · 2009-02-12T14:37:18.840

0

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.

edited Feb 12 '09 at 14:37

answered Feb 12 '09 at 14:22

Chris Canal

4,824
9
35
45

Yes, however the reason for loading from the Locations collection is that the Pages are an ordered List. I need to be able to return the 1st page from that ordered list. – Dave The Ninja Feb 12 '09 at 14:28
I had implemented this before by exposing the Index as a get only (set by Access.AsCamelCaseField(Prefix.Underscore)) Not ideal but looks like the only way forward with this one. Cheers for confirming Chris – Dave The Ninja Feb 12 '09 at 14:45

score 0 · Answer 2 · answered Feb 12 '09 at 14:23

0

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.]

answered Feb 12 '09 at 14:23

teedyay

23,293
19
66
73

Thanks for the fast response. As I commented on Chris's answer, the Page collection is an ordered list so I need to be sure im getting the first Page from the order specified within the Locations pages collection – Dave The Ninja Feb 12 '09 at 14:31

score 0 · Answer 3 · answered Feb 12 '09 at 15:17

0

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.

answered Feb 12 '09 at 15:17

pythonandchips

2,175
2
25
28

Yeh, as soon as FirstOrDefault is executed there is a query to the DB. – Dave The Ninja Feb 12 '09 at 15:26

How to return first object of a collection from its parent

3 Answers3