Nhibernate Queryover multi level model

Question

lets say i have this database schema

[User] -1----n-> [Resource] -1----n-> [ResourceVersion]

and i want to select this using Nhibernate in one database roundtrip for user by username but select resourceVersions with future doesn work. How to hydrate collection of collections in one roundtrip using Futures? I prefer QueryOver or Criteria over HQL. I am using nHibernate 4.0.

public virtual User GetUserResources(string username)
  using (ISession session = GetSession())
  {
    Resource resAlias = null;           
    User userAlias = null;

    var result = session.QueryOver(() => userAlias)
     .JoinQueryOver(x => x.Resources, () => resAlias)
     .JoinQueryOver(() => resAlias.Versions)
     .Where(() => userAlias.Login == username)
     .Future<User>(); //THIS DOESNT WORK


      var user = session.QueryOver<User>()
       .Fetch(x => x.Resources).Eager
       .Where(x => x.Login == username)
       .SingleOrDefault<User>();//with this i can select user and resources

      return user;
   }

Mappings:

USER:
<class name="User" table="[User]">
  <id name="Id" type="Int32">
    <generator class="identity" />
  </id>

  <property name="Name">
    <column name="Name" sql-type="varchar(100)" />
  </property>

  <property name="Email">
    <column name="Email" sql-type="varchar(255)" />
  </property>

  <property name="Login">
    <column name="Login" sql-type="varchar(50)" />
  </property>

  <property name="PasswordHash">
    <column name="PasswordHash" sql-type="varchar(100)" />
  </property>

  <property name="CreateDate">
    <column name="CreateDate" sql-type="datetime" />
  </property>

  <bag name="Resources" lazy="true" fetch="subselect" cascade="all-delete-orphan">
    <key column="UserResource"/>
    <one-to-many class="Resource" />
  </bag>
</class>

RESOURCE:
<class name="Resource" table="[Resource]" abstract="true">
  <id name="Id" type="Int64">
    <generator class="identity" />
  </id>

  <discriminator column="Type"
               not-null="true"
               type="String" />

  <bag name="Versions" cascade="all-delete-orphan" inverse="true" lazy="true" order-by="ActiveFrom DESC">
    <key column="ResourceId" not-null="true"/>
    <one-to-many class="Version"/>
  </bag>

  <subclass name="Resource1" discriminator-value="Res1" />

  <subclass name="Resource2" discriminator-value="Res2" />
</class>

VERSION:
<class name="Version" table="Version">

<id name="Id" type="long">
  <!--<column name="Id" sql-type="bigint"/>-->
  <generator class="identity" />
</id>
...
<many-to-one name="Resource"
             class="Resource"
             column="ResourceId"/>

 <property name="ActiveFrom">
   <column name="ActiveFrom" sql-type="datetime" />
 </property>

 <property name="ActiveTo">
   <column name="ActiveTo" sql-type="datetime"/>
 </property>
...

Only query executed according to intelli trace in visual studio is this one:

SELECT this_.Id AS Id0_1_ , 
   this_.Name AS Name0_1_ , 
   this_.Email AS Email0_1_ , 
   this_.Login AS Login0_1_ , 
   this_.PasswordHash AS Password5_0_1_ , 
   this_.CreateDate AS CreateDate0_1_ , 
   resource2_.UserResource AS UserResource3_ , 
   resource2_.Id AS Id3_ , 
   resource2_.Id AS Id4_0_ , 
   resource2_.Type AS Type4_0_
FROM
   [User] this_ LEFT OUTER JOIN [Resource] resource2_
   ON this_.Id
      = 
      resource2_.UserResource
WHERE this_.Login    
        =
       @p0;

and in @p0 is username i pass to method. No sign of versions at all which i find a little odd.

It simply don't ask DB for any query which contains versions. Also version collection on resource throw lazy initialization exception. — LightCZ, Feb 09 '15 at 03:12

Firo · Answer 1 · 2015-02-25T14:35:53.123

0

you are never iterating the IEnumerable returned by the future so it never executes it. I don't have NH 4.0 here right now but the following might work

public virtual User GetUserWithResources(string username)
{
    using (ISession session = GetSession())
    {
      Resource resAlias = null;           

      return session.QueryOver<User>()
          .Where(user => user.Login == username)
          .Left.JoinQueryOver(x => x.Resources)
              .Left.JoinQueryOver(res => res.Versions)
          .TransformUsing(Transformers.DistinctRootEntity)
          .List<User>().SingleOrDefault();
    }
}

edited Feb 25 '15 at 14:35

answered Feb 10 '15 at 12:52

Firo

30,626
4
55
94

I did try that but it fails on that SingleOrDefault() part because result is more than one user.i thought that result would be cartesian product when i load multiple collection at one query. Imagine that one user have 2 resources and each of them have ... lets say 5 versions... Result from this scenario is (without single or default) 10 USERS each one of them have Resources lazy initializable exception... not to mention versions... – LightCZ Feb 11 '15 at 11:52
ah sorry. forgot to set the transformer, fixed – Firo Feb 11 '15 at 15:40
Ok so i tried it with distinctRootEntitiy Transformer it wont select user.Resources this part in SQL query there is no sign of joining on resources and on versions so user.Resources throws Lazy initialization exception – LightCZ Feb 13 '15 at 13:50
i tried myself again and i was missing a .Left in the code. However i tested this with NH 3.3.1 so it might have changed. If i get to it again i will try it with NH 4.0 – Firo Feb 25 '15 at 14:37

Nhibernate Queryover multi level model

1 Answers1