Unable to figure out how to do Joins within IQueryable

Question

Here is what I am trying:

IQueryable query = this.MyRepository.GetShippingCollection();

IList<SomeListType> myList = query.Where(x => x.Settings
                                      .Where(y => y.SelectorID.Equals(5))
                                      .Count() > 0)
                                   .OrderBy(x => x.Order)
                                   .ToList();

Produces this error: could not resolve property: Settings.ID

If I do it this way it works, but causes over 3,000 queries on my SQL Server:

IList<SomeListType> myList = this.MyRepository.GetShippingCollection().ToList();
myList = myList.Where(x => x.Settings
                        .Where(y => y.SelectorID.Equals(5))
                        .Count() > 0)
               .OrderBy(x => x.Order)
               .ToList();

I know the solution resides within using a "Join".

I have been looking at examples for the last couple hours and can only find Join examples within the Mapping file. I am also finding examples for "ICriteria".

I don't want to have to create seporate entries for all my complex queries in the mapping file so the join within that file will not work.

Since I am using Fluent NHibernate, I am not using "ICriteria". I am using "IQueryable". How do you join multiple tables within "IQueryable"?

Any help with this would be greatly appreciated. Thanks in advance.

Answer 1

If the second query is executing 3,000 queries, it is almost certainly lazy-loading the Settings collection. As you iterate over the list, you access this collection, and each time NHibernate goes back to the database to fetch it. Try setting the fetch mode for the Settings property to eager load in the mapping.

Beyond that, the LINQ provider could be an issue. What version of NHibernate are you using? The 2.x LINQ provider has some real limitations. It has been reimplemented in the 3.0 trunk, but you'll have to download and compile it from the source.

By the way, ICriteria vs IQueryable is not related to Fluent NHibernate. Criteria API and LINQ are two providers through which you can create queries. Fluent NHibernate is an alternative way to perform configuration.