CODEWITHSUNDEEP
nhibernatenhibernate-mapping
Tyler Camp
Tyler Camp

Reputation: 197

NHibernate Collection Property from Query (Non-Fluent)

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

Upvotes: 0

Views: 131

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

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

Upvotes: 1

Related Questions