NHibernate 4 upgrade - Cannot simultaneously fetch multiple bags

Question

I tried to upgrade NH 3.3.1.4000 to the latest version NH 4.0.2.4000 and I had an issue with FetchMany and ThenFetchMany.

In this post I learned that this old functionality is no longer valid, Breaking changes with NHibernate 4 upgrade.

What is the correct way to do this kind of fetching on the new NH version?

Code Example:

var IdsList = new List { /* Some Ids */ };
session.Query<A>()
.FetchMany(x=>x.B_ObjectsList)
.ThanFetchMany(x=>x.C_ObjectsList)
.Where(x=>IdsList.Contains(x=>x.Id))
.ToList();

Classes:

Public Class A
{
    public int Id {get;set;}
    public IList<B> B_ObjectsList{get;set;}
}

Public Class B
{
    public int Id {get;set;}
    public IList<C> C_ObjectsList {get;set;}
}

Public Class C
{
    public int Id {get;set;}
}

Mapping:

<class name="A" table="A">
<id name="Id" type="int" column="Id" unsaved-value="0">
  <generator class="identity" />
</id>
<bag name="B" table="B" inverse="false" lazy="true"
cascade="all-delete-orphan">
</class>

<class name="B" table="B">
<id name="Id" type="int" column="Id" unsaved-value="0">
  <generator class="identity" />
</id>
<bag name="C" table="C" inverse="false" lazy="true"
cascade="all-delete-orphan">
</class>


<class name="C" table="C">
<id name="Id" type="int" column="Id" unsaved-value="0">
  <generator class="identity" />
</id>
</class>

score 2 · Accepted Answer · answered Jan 14 '15 at 15:52

2

probably

var IdsList = new List { /* Some Ids */ };
var results = session.Query<A>()
    .FetchMany(x => x.B_ObjectsList)
    .Where(x=>IdsList.Contains(x.Id))
    .ToList();

// initialize C_ObjectsList
var bIds = results.SelectMany(x => x.B_ObjectsList).Select(b => b.Id).Distinct().ToList();
session.Query<B>()
    .FetchMany(x => x.C_ObjectsList)
    .Where(b => bIds.Contains(b.Id))
    .ToList();

return results;

answered Jan 14 '15 at 15:52

Firo

30,626
4
55
94

Thank you, i checked and it works. But if I'm using further it doesnt work, can i do this kind of fetching without querying the database for each join? – Matan Shidlov Jan 20 '15 at 12:42
1

with sql there are only 2 possibilities: large cartesian product or a query for each association level. The first often leads to large results easyly so that's probably the reason why it was removed. if the associations are all on the same level you can use Futures to reduce the roundtrips to the database – Firo Jan 20 '15 at 13:06
Too bad it was blocked if this change causes more approaches for the Data Base, and I am guessing it will also be more difficult to access relations mapped with where filter. Thank you, much appreciate. – Matan Shidlov Jan 20 '15 at 14:23

score 0 · Answer 2 · answered Feb 02 '17 at 12:13

If B has a reference to A you can do:

var IdsList = new List { /* Some Ids */ };
var results = session.Query<A>()
                     .Fetch(a => a.B_ObjectsList)
                     .Where(a => IdsList.Contains(a.Id))
                     .ToList();

// initialize C_ObjectsList
var aQuery = session.Query<A>()
                    .Where(x => IdsList.Contains(x.Id));

session.Query<B>()
       .Fetch(b => b.C_ObjectsList)
       .Where(b => aQuery.Contains(b.A)
       .Prefetch();

This has the advantage of not being limited by the max parameters of the DB, which in SQL server defaults to 2100. Instead of ToList(), I use this extension method:

static public void Prefetch<T>(this IQueryable<T> query)
{
    // ReSharper disable once ReturnValueOfPureMethodIsNotUsed
    query.AsEnumerable().FirstOrDefault();
}

NHibernate 4 upgrade - Cannot simultaneously fetch multiple bags

2 Answers2