StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>So if there is no "Joe" sponsor for a project, then its ProjectSponsors collection will be empty. Is that right?</p> <p>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.</p> <p>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: </p> <pre><code>Session.Query<Project>().FetchMany(r => r.ProjectSponsors).ThenFetch(r => r.Sponsor).Where(s => s.Name == "Joe") </code></pre> <p>what kind of SQL would it generate? Something like this:</p> <pre><code>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' </code></pre> <p>That would, however, result into selecting only those projects which have at least one Sponsor named Joe.</p> <p>What you want, actually, is to filter ProjectSponsors to only select those which have their Sponsor named Joe.</p> <p>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:</p> <pre><code><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> </code></pre> <p>With that, I'd use your original statement after enabling and setting the filter:</p> <pre><code>Session.EnableFilter("SponsorName").SetParameter("name", "Joe"); var projects = Session.Query<Project>().FetchMany(r => r.ProjectSponsors).ThenFetch(r => r.Sponsor); </code></pre> <p>That would generate SQL like this:</p> <pre><code>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 </code></pre> <p>which would select all Projects but for them only such ProjectSponsor combinations which have Joe as Sponsor.</p> <p>I'm not sure if this is the best approach but hope it will help.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload