NHibernate: Combine a collection other than the composite keys specified in the mapping

Question

In the old database, I have to work with nested tables that are linked using compound keys. Translated to NHibernate, I have, for example, the FcoTransportation class, which has a collection of children of the FcoConsignment class. However, in one situation, I would like to load a collection based on only one of the components of the composite key and ignore the other component.

The display is as follows:

<?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 am trying to create this query using the usual NHibernate, SQL, and HQL criteria. This is what I got so far in HQL, which at least least loads transports with no errors:

  String queryString = "select ft from FcoTransportation as ft"; queryString += " join ft.Consignments as fc on fc.FK_TransportationID = :ID"; var query = session.CreateQuery(queryString); transports = query .SetMaxResults(100) .List<FcoTransportation>(); 

However, the collection of lots remains empty! How to solve this?

As an extra, I would like to weed out any duplicate children, preferring those records with the highest values ​​in the two Modified and Modified Time columns, respectively.

---

As a last resort, I am considering removing the composite key mapping alltogether. In this case, I still have to remove duplicates based on the last changed mode ChangedDate / ChangedTime ...

---

UPDATE I tried to remove the compound key mappings, but then I get an error that seems to be triggered because the compound foreign keys are enforced even when I try to ignore them. So, what is the trick of persuading NHibernate not to apply this, since I can easily write an SQL query in SQL Srv Mgt Studio by doing this:

 SELECT TOP 100 * FROM [FCO_EVENT] AS e INNER JOIN [FCO_TRANSPORTATION] AS t ON e.FK_TransportationID = t.ID --children: LEFT OUTER JOIN [FCO_CONSIGNMENT] AS c ON c.FK_TransportationID = t.ID LEFT OUTER JOIN [FCO_CONSIGNMENT_LINES] AS cl ON cl.FK_ConsignmentID = c.ID 

---

UPDATE . It was suggested to use a connection choice that looks promising, but no children have been selected so far:

  String queryString = "select ft from FcoTransportation as ft where ft.ID ='" + guid + "'"; queryString += " join fetch ft.Consignments as fc on fc.FK_TransportationID = '" + guid + "'"; 

---

UPDATE It was also proposed to make the so-called union "theta-style", which looks like below, but here the collection of children is not filled:

 String queryString = "select ft from FcoTransportation as ft, FcoConsignment as fc" + " where ft.ID = fc.FK_TransportationID" + " and ft.ID = '" + guid + "'"; 

---

NOTE I only need to get the data, not save it again. I already have a request for each transport lot (which is ten times larger, but usually several, and in some individual cases several hundred). I just want to be economical with the number of database calls. That is why I would like the goods to be delivered while receiving transports.

Answer 1

I think you're wrong about that. A one-to-many relationship mapping is the object-oriented equivalent of a foreign key relationship in a relational database. The ability to filter or get a different view of the child collection does not make sense in this context. Assuming you can do this, how can NHibernate save changes to the filtered child collection? If NHibernate cannot save it, then it is not modeled correctly. Your desire also to "filter out any duplicate children, preferring those records with the highest values ​​in the two columns" Modified data "and" Modified period ", confirms this conclusion.

I would just create a request to return the necessary loads. Using Future, you can transfer it to a method that returns an FcoTransportation object, and the query results in one trip to the database.

Another option is to add the FcoTransportation method, which filters the child collection. If the number of Consignments is reasonable (<10000?), And you often need to filter the collection this way, then I would choose this option.

Answer 2

You are currently just joining to not get any children, so try using "join fetch" instead of just joining:

 select ft from FcoTransportation as ft join fetch ft.Consignments as fc on fc.FK_TransportationID = :ID 

That should do the trick.

Answer 3

try it

 select ft from FcoTransportation as ft, FcoConsignment as fc where ft.Id = fc.Transportation.Id 

I do not know the name of the navigation property from FcoConsignment for FcoTransportation, so I called it a vehicle.