NHibernate: Join collection on other than composite keys specified in mapping -

NHibernate: Join collection on other than composite keys specified in mapping -

in legacy database have work nested tables associated through composite keys. translated nhibernate, have e.g. class fcotransportation has collection of children of class fcoconsignment. however, in 1 situation, load collection based on 1 of components of composite key , ignore other component.

the mapping looks this:

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false"> <class name="fcolib.fcotransportation, fcolib" table="fco_transportation"> <composite-id> <key-property name="id"/> <key-property name="fk_eventid"/> </composite-id> <!--...snip...--> <bag name="consignments" table="fco_consignment" lazy="false" cascade="save-update"> <key> <column name="fk_transportationid"/> <column name="fk_eventid"/> </key> <one-to-many class="fcolib.fcoconsignment, fcolib"/> </bag> <!--...snip...-->

i have been trying create query using normal nhibernate criteria, sql , hql. have got far in hql @ to the lowest degree least loads transports without errors:

string querystring = "select ft fcotransportation ft"; querystring += " bring together ft.consignments fc on fc.fk_transportationid = :id"; var query = session.createquery(querystring); transports = query .setmaxresults(100) .list<fcotransportation>();

however, collection of consignments remains empty! how solve !?

as extra, weed out duplicate children preferring entries highest values in 2 columns "changeddate" , "changedtime" respectively.

as lastly resort, considering removing composite key mapping alltogether. in case, still have remove duplicates on basis of latest changeddate/changedtime...

update: have tried removing composite key mappings, receive error apparently thrown, because composite foreign keys enforced when seek ignore them. trick of convincing nhibernate not enforce this, since can write sql query in sql srv mgt studio doing this:

select top 100 * [fco_event] e inner bring together [fco_transportation] t on e.fk_transportationid = t.id --children: left outer bring together [fco_consignment] c on c.fk_transportationid = t.id left outer bring together [fco_consignment_lines] cl on cl.fk_consignmentid = c.id

update: has been suggested utilize bring together fetch, looks promising, still no children fetched:

string querystring = "select ft fcotransportation ft ft.id ='" + guid + "'"; querystring += " bring together fetch ft.consignments fc on fc.fk_transportationid = '" + guid + "'";

update: has been suggested so-called "theta-style" join, looks below, here, children collection not populated:

string querystring = "select ft fcotransportation ft, fcoconsignment fc" + " ft.id = fc.fk_transportationid" + " , ft.id = '" + guid + "'";

note: need info out, not save again. have query getting each transport's consignments (which dozens @ most, few , in isolated cases few hundred). want economic amount of roundtrips database. why consignments fetched while getting transports out @ same time.

i think you're taking wrong approach this. one-to-many relationship mapping object oriented equivalent of foreign key relationship in relational database. ability filter or different view of kid collection doesn't create sense in context. assuming it, how nhibernate persist changes filtered kid collection? if nhibernate can't persist it, it's modeled incorrectly. want "weed out duplicate children preferring entries highest values in 2 columns "changeddate" , "changedtime"" reinforces conclusion.

i create query homecoming consignments want. using future, wrap in method homecoming fcotransportation object , query results in single trip database.

another alternative add together method fcotransportation filters kid collection. if number of consignments reasonable (<10000?) , need filter collection in manner, take option.

nhibernate join hql composite-key