Is is possible to add .Where() on a child collection property using nhibernate linq?

Question

i have this query that i am running to do an upfront join between these tables:

Table - columns
- Project - id, name, description
- ProjectSponsor (bridge table) - id, sponsorid, projectid, isPrimarySponsor
- Sponsor - id, first, last

here is the nhibernate 3 linq query i am running to do a join:

 IEnumerable<Project> list = Session.Query<Project>().FetchMany(r => r.ProjectSponsors).ThenFetch(r => r.Sponsor);

how do i add a where clause to only include a sponsor where the sponsorName = "Joe"

i remember seeing that fetch always much come at the end of a nhibernate linq query but it seems like a i need to do a where after the "ThenFetch" to make this work?

Can anyone please let me know how i can add a "where clause" equivalent on the Sponsor

NOTE:

i can't just have a simple many to many direct mapping from Project to Sponsor because of the "isPrimarySponsor" field needed in the bridge table.

Answer 1

First let me show my understanding of what you are trying to achieve. You want to fetch ALL Projects and into these projects you want to fetch their corresponding ProjectSponsors which include Sponsors but you only want such ProjectSponsors that have a Sponsor with specific name.

So if there is no "Joe" sponsor for a project, then its ProjectSponsors collection will be empty. Is that right?

If so, you want to do left join between projects and sponsors. Also, you want to fetch it all at once, that is the reason you want to use these Fetch methods.

As far as I could find out, there really is no way to add a Where into your statement. But even if there was, I think it would not help you. If you could write:

Session.Query<Project>().FetchMany(r => r.ProjectSponsors).ThenFetch(r => r.Sponsor).Where(s => s.Name == "Joe")

what kind of SQL would it generate? Something like this:

SELECT 
    * 
FROM 
    Project P
    LEFT JOIN ProjectSponsors PS ON PS.ProjectId = P.ProjectId
    LEFT JOIN Sponsors S ON S.SponsorId = PS.SponsorId
WHERE
    S.Name = 'Joe'

That would, however, result into selecting only those projects which have at least one Sponsor named Joe.

What you want, actually, is to filter ProjectSponsors to only select those which have their Sponsor named Joe.

Now I don't know if this is an acceptable solution for you, but this is how I would do it. I would define and set a filter in my mapping for ProjectSponsors association in Project entity like this:

<class name="Project" table="Project">
...
<set name="ProjectSponsors" table="ProjectSponsor">
  <key column="ProjectId" />
  <one-to-many class="ProjectSponsor" />
  <filter name="SponsorName" condition="EXISTS (SELECT * FROM Sponsor where Sponsor.SponsorId = SponsorId AND Sponsor.Name=:name)" />
</set>
</class>
<filter-def name="SponsorName">
    <filter-param name="name" type="String"/>
</filter-def>

With that, I'd use your original statement after enabling and setting the filter:

Session.EnableFilter("SponsorName").SetParameter("name", "Joe");
var projects = Session.Query<Project>().FetchMany(r => r.ProjectSponsors).ThenFetch(r => r.Sponsor);

That would generate SQL like this:

SELECT 
    * 
FROM 
    Project P
    LEFT JOIN ProjectSponsors PS ON PS.ProjectId = P.ProjectId AND EXISTS (SELECT * FROM Sponsor where Sponsor.SponsorId = PS.SponsorId AND Sponsor.Name = 'Joe')
    LEFT JOIN Sponsors S ON S.SponsorId = PS.SponsorId

which would select all Projects but for them only such ProjectSponsor combinations which have Joe as Sponsor.

I'm not sure if this is the best approach but hope it will help.