NHibernate Collection Property from Query (Non-Fluent)

Question

Is it possible to have a read-only collection on an entity where the collection is populated by a custom SQL query?

I have 3 classes - Village, Report, and Army - each backed by a table. I want a Village to have a property "ReportsAsDefender", which is a collection of Reports where the defending Army in that Report belongs to the given Village:

SELECT
    village.Id as VillageId, report.*
FROM
    armies army

    join reports report
        on report.DefendingArmyId = army.Id
    join villages village
        on village.Id = army.VillageId

I want the results of this query accessible as a collection on my Village class. This may be doable using only XML mapping but I can't find anything that would be helpful here.

I'm not using Fluent.

Edit 1: I've uploaded my current source code and sample SQL data so that my issues can be reproduced: https://github.com/tylercamp/NHTW

I've also updated the SQL query above to match my current changes.

With the changes suggested by @RadimKöhler, I get an exception when invoking someVillage.ReportsAsDefender.ToList(): could not initialize a collection:, followed by some nonsensical MySQL:

SELECT
    reportsasd0_.VillageId as villageid11_4_1_,
    reportsasd0_.Id as id1_4_1_,
    reportsasd0_.Id as id1_4_0_,
    reportsasd0_.TribalWarsId as tribalwarsid2_4_0_,
    reportsasd0_.WorldId as worldid3_4_0_,
    --- ... and a for more nonsensical "selects"

FROM Reports reportsasd0_ WHERE reportsasd0_.VillageId=?

I have no clue where "asd" comes from, nor any of the other formatting. The string "asd" does not appear anywhere in my codebase.

The relevant entities from my .hbm.xml file contains:

  <class name="Village" table="Villages">
    <id name="Id">
      <generator class="increment" />
    </id>

    <property name="TribalWarsId" />
    <property name="WorldId" />
    <property name="Name" />
    <property name="OwnerId" />

    <!-- Where "villa_reports_as_defender" is the name of the view query shown above -->
    <bag name="ReportsAsDefender" table="villa_reports_as_defender" mutable="false" lazy="true" inverse="true">
      <key column="VillageId" />
      <one-to-many class="Report"/>
    </bag>
  </class>

  <!-- Report -->
  <class name="Report" table="Reports">
    <id name="Id">
      <generator class="increment" />
    </id>

    <property name="TribalWarsId" />
    <property name="WorldId" />

    <property name="AttackingArmyId" />
    <property name="DefendingArmyId" />
    <property name="RemainingAttackingArmyId" />
    <property name="RemainingDefendingArmyId" />
    <property name="DodgedArmyId" />

    <property name="OccurredAt" />
    <property name="LaunchedAt" />
  </class>

My Village class has the following property:

public virtual ICollection<Report> ReportsAsDefender { get; set; }

The easiest way would be - map your special select as a view to a standard entity. Make it immutable. All benefits of NHibernate will be included ... ; — Radim Köhler, Apr 25 '18 at 05:28
Thank you @RadimKöhler, that's gotten me most of the way there. I've made the view and added a mapping via . After querying for an entity with some data in the view, I'm not yet getting any data in my ISet. Is it an issue that the columns in my view don't perfectly match those in my data type? The view has an extra column to match the village ID against. — Tyler Camp, Apr 26 '18 at 02:16
Added some details... if you'd follow it, you should make it. Or extend your question with what you have ... I am ready to assist... — Radim Köhler, Apr 26 '18 at 05:34
Thank you very much for your help @RadimKöhler, I'll look over this soon and get back to you! — Tyler Camp, Apr 27 '18 at 14:08

score 1 · Answer 1 · answered Apr 26 '18 at 05:33

Any related data should be mapped as a standard entity. Even if that would be a view (question could be how effective data loading we will experience...)

Such view must have a relation column (e.g. Parent_ID). That could be expressed as a many-to-one in the child mapping:

<many-to-one name="Parent" column="Parent_ID" ... />

and exactly that column we must use in the parent mapping

<bag name="Children"
       lazy="true" 
       inverse="true" 
       batch-size="25" 
       cascade="all-delete-orphan" >
    // This columns is the same as for many-to-one
    <key column="Parent_ID" />
    <one-to-many class="Child" />
</bag>

Check all the details here:

Minimal and correct way to map one-to-many with NHibernate

Thank you, I've attempted the changes but now get an exception when enumerating the collection. I've updated the main post. EDIT: I now realize that my use case appears to be a bit different from what you described. I'm not sure what approach to take here. I've provided my source on GitHub since that would be more effective than copy/pasting code to SO. — Tyler Camp, Apr 28 '18 at 15:39

NHibernate Collection Property from Query (Non-Fluent)

1 Answers1

Minimal and correct way to map one-to-many with NHibernate