Linq-to-NHibernate - How to do a Join with a SelectMany

Question

Is it possible in Linq-to-NHibernate to do a Join with a SelectMany within the same query?

It might look weird, but the idea would be to generate the following query:

select * from State
join (
select CityId, StateId 
from State 
inner join City on State.StateId=City.StateId
) as City on City.StateId = State.StateId

The reason is that we are using a repository pattern and I do not want to use 2 different repository to do my join.

In code, I have tried the following block, but I get a not supported exception

var states = Session.Query<State>();

var query = states.Join(states.SelectMany(x => x.Cities), state => state.StateId,
city => city.State.StateId, (state, city) => new {state, city});

var result = query.ToArray(); // <- Throws a not supported exception

I have tried the same thing with two repositories and it works:

var states = Session.Query<State>();
var cities = Session.Query<City>();

var query = states.Join(cities , state => state.StateId,
city => city.State.StateId, (state, city) => new {state, city});

var result = query.ToArray(); // <- This works perfectly fine

Thanks in advance!

Answer 1

Ah! I found out that I wasn't thinking the right way. I can do the equivalent by starting with a SelectMany, then do a select of both.

This is supported and will do the same thing as a join but even more optimized:

var states = Session.Query<State>();

var query = states.SelectMany(x => x.Cities).Select(city => new {city.State, city});

var result = query.ToArray(); // It works!

And with that, I don't even need to get a repo of City.