Geek Answers Handbook

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

I have this query that I run to pre-join between these tables:

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

here is the nhibernate 3 linq request that I run to make the connection:

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

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

I remember that the selection always came at the end of the linq nhibernate request, but it seems to me that I need to do where after "ThenFetch" to do this work?

Can someone please let me know how I can add the equivalent of "where clause" to the Sponsor

Note:

I cannot have a simple many many direct mapping from Project to Sponsor due to the isPrimarySponsor field that is required in the bridge table.

+5
source share
2 answers

, . ProjectSponsors, , , ProjectSponsors .

, "Joe" , ProjectSponsors . ?

, . , , Fetch.

, "Where". , , . :

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

SQL ? - :

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'

, , , .

ProjectSponsors, , Joe.

, , . ProjectSponsors Project :

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

:

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

SQL :

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

, ProjectSponsor, Joe .

, , , .

+7

NHibernate Linq :

IEnumerable<Project> list = (
            from p in Session.Query<Project>()
            from s in p.Sponsors
            where s.Name == "SponsorName"
            select p)
            .FetchMany(r => r.Sponsors)
    .ThenFetch(r => r.Sponsor).ToList();
0

More articles:


All Articles