How to Implement Batch Fetching with Fluent Nhibernate When Working with Oracle

Fluent nhibernate map multiple tables

There is no way how to generate such SELECT statement.

I would suggest to use batch fetching. See these for more details:

• How to Eager Load Associations without duplication in NHibernate?
• How to implement batch fetching with Fluent NHibernate when working with Oracle?

The adjusted mapping:

public UserEntityMap : ClassMap<UserEntity>
{
   //Other properties
   HasMany(x => x.Addresses)
       .KeyColumn("User_id").Fetch.Join()
       .BatchSize(100);
   HasMany(x => x.Roles)
       .KeyColumn("User_id").Fetch.Join()
       .BatchSize(100);
}

This will allow to query the root entity and with just few SELECTS get also their collections (no 1 + N issue)

Also check What is the solution for the N+1 issue in hibernate?

NHibernate Filtered Child Collection Lazy Loaded even with eager fetch specified

I would like to share my approach, maybe not the answer...

I. avoid fetching one-to-many (collections)

When creating any kind of complex queries (ICriteria, QueryOver) we should use (LEFT) JOIN only on a start schema. I.e. on many-to-one (References() in fluent). That leads to expected row count from the perspective of paging (there is always only ONE row per root Entity)

To avoid 1 + N issue with collections (but even with many-to-one in fact) we have the NHiberante powerful feature:

19.1.5. Using batch fetching

NHibernate can make efficient use of batch fetching, that is, NHibernate can load several uninitialized proxies if one proxy is accessed (or collections. Batch fetching is an optimization of the lazy select fetching strategy)...

Read more here:

• How to Eager Load Associations without duplication in NHibernate?
• How to implement batch fetching with Fluent NHibernate when working with Oracle?

So, in our case, we would adjust mapping like this:

public PricingIncrementMap()
    : base("PricingIncrement")
{
    Map(x => x.IncrementYear);
    HasMany<OptionPrice>(x => x.OptionPrices)
        .KeyColumn("OptionIdentifier_id")
        .Cascade.None()
        .Inverse() // I would use .Inverse() as well
        // batch fetching
        .BatchSize(100);
}

II. collection filtering

So, we managed to avoid 1 + N issue, and we also query only star schema. Now, how can we load just filtered set of items of our collection? Well, we have native and again very powerful NHibernate feature:

18.1. NHibernate filters.

NHibernate adds the ability to pre-define filter criteria and attach those filters at both a class and a collection level. A filter criteria is the ability to define a restriction clause very similiar to the existing "where" attribute available on the class and various collection elements...

Read more about it here:

• how to assign data layer-level filters
• Limit collection to retrieve only recent entries for readonly entity

So in our case we would define filter

public class CollFilter : FilterDefinition
{
    public CollFilter()
    {
        WithName("CollFilter")
            .WithCondition("PricingIncrement_id = :pricingIncrementId")
            .AddParameter("pricingIncrementId",NHibernate.Int32);
    }
} 

And we would need to extend our mapping again:

HasMany<OptionPrice>(x => x.OptionPrices)
    .KeyColumn("OptionIdentifier_id")
    .Cascade.None()
    .Inverse()
    // batch fetching
    .BatchSize(100)
    // this filter could be turned on later
    .ApplyFilter<CollFilter>();

Now, before our query will be executed, we just have to enable that filter and provide proper ID of the year 2015:

// the ID of the PricingIncrement with year 2015
var pricingIncrementId thes.Session
     .QueryOver<PricingIncrement>()
     .Where(x => x.IncrementYear == 2015)
     .Take(1)
     .Select(x => x.ID)
     .SingleOrDefault<int?>();

this.Session
   .EnableFilter("CollFilter")
   .SetParameter("pricingIncrementId", pricingIncrementId);

// ... the star schema query could be executed here

III. Sub-query to filter root entity

Finally we can use sub-query, to restrict the amount of root entities to be returned with our query.

15.8. Detached queries and subqueries

Read more about it here:

• Query on HasMany reference
• NHibernate Criteria Where any element of list property is true

so, our subquery could be

// Subquery
var subquery = DetachedCriteria.For<OptionPrice >()
    .CreateAlias("Increment", "i", JoinType.InnerJoin)
    .Add(Restrictions.Eq("i.IncrementYear", 2015))
    .SetProjection(Projections.Property("Option.ID"));

// root query, ready for paging, and still filtered as wanted
ICriteria pagedCriteria = this.Session.CreateCriteria<OptionIdentifier>()
    .Add(Subqueries.PropertyIn("ID", subquery))
    .SetResultTransformer(CriteriaSpecification.DistinctRootEntity);

Summary: We can use lot of features, which are shipped with NHibernate. They are there for a reason. And with them together we can achieve stable and solid code, which is ready for further extending (paging at the first place)

NOTE: maybe I made some typos... but the overall idea should be clear

How to make one to one mapping to respect parent/owner object's batching in Fluent nHibernate?

You are almost there. All the batch-size mapping you've done is correct, but BatchSize could be set not only to the Collections, but to classes as well.

public ActorMap()
{
    Id(x => x.ActorId, "actor_id");
    Map(x => x.FirstName, "first_name");
    BatchSize(100);
}

NOTE: My experience is to use it almost for every class, exactly for a reason you mentioned here... We'll gain lazy loading with more effective batching

nhibernate QueryOver select one table after join

One way would be, to use Transformers.DistinctRootEntity For example:

var query = Session.QueryOver(() => parentAlias)
    .Left.JoinAlias(x => x.Children, () => childAlias)
    .Where(whereClause)

    // Here is the trick
    .TransformUsing(Transformers.DistinctRootEntity)

See more in this Andrew Whitaker post:

QueryOver Series - Part 4: Transforming

The other way...

Well, I would not go, in fact I do go, do not use this approach. My really proved way is to:

1. Create query from Child and join parent (star schema)
2. If we have to query parent
   
   
   • for filtering through child use subselects
   • for loading children in batches use mapping setting batch-size

The first is obvious, and because we query star schema, we can easily use paging

The second would need some adjustments for filtering parent by child:

• Fetching Paginated Entity with Collection

For effective loading of child collection, we can profit from batch-size mapping. Check these:

• How to Eager Load Associations without duplication in NHibernate?
• How to implement batch fetching with Fluent NHibernate when working with Oracle?

---

Related Topics