Fluent Mapping Many-Many with sorting

Question

I'm trying to get a many to many relationship to work using Fluent Nhibernate.

I have a Product and a RelatedProduct Table

Product {Id, Name...}
ProductRelated{Id, ProductId, RelatedProductId, relOrder}

and a Product class

The mapping looks like

  HasManyToMany(x => x.RelatedProducts)
                .Table("ProductRelated")
                .ReadOnly()
                .ChildOrderBy("relOrder asc")
                .ParentKeyColumn("ProductId")
                .ChildKeyColumn("RelatedProductId");

When a query is done for Product and the RelatedProducts are lazy loaded I can see that the sorting is applied correctly using the relOrder on the join table.

Session.Query<Product>()
       .FetchMany(p => p.Categories)
       .FetchMany(p => p.Departments)

Once I add in eager loading of the related products NHibernate tries to sort by a relOrder column on the product itself instead of on the join table.

Session.Query<Product>()
       .FetchMany(p => p.Categories)
       .FetchMany(p => p.Departments)
       .FetchMany(p => p.RelatedProducts)

Any ideas of whats going on here?

Answer 1

Well to answer your question what's going on here?, I would say, you are using: "not-together fitting features" of NHibernate.

A snippet from documentation 6.6. Sorted Collections:

Setting the order-by attribute tells NHibernate ...

Note: that lookup operations on these collections are very slow if they contain more than a few elements.

Note: that the value of the order-by attribute is an SQL ordering, not a HQL ordering!

So, this could be applied only for "standard" lazy loading, becuase this kind of a feature is applied only on a DB side. It is not managing order in the memory.

And the eager fetching, as the counter-part, is a different way how to generate and issue the SQL Statement to DB.

So, eager and order-by will never work together.

*

My NOTE: I simply have to append this. I can't help myself

I.

The Eager fetching is the feature which should be avoided (I never use it, but it's me). There is a better solution and it is setting the BatchSize(), which will reduce the 1+N into 1+(a few) and will keep all the (lazy) featrues, including order-by. Check these if interested:

• NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
• Is this the right way to eager load child collections in NHibernate
• https://stackoverflow.com/questions/18419988/

BatchSize() is supported for HasManyToMany as well: ToManyBase:

/// <summary>Specify the select batch size </summary>
/// <param name="size">Batch size</param>
public T BatchSize(int size) { ...

II.

The many-to-many mapping, while fancy at first look, is not the way I'd suggest. Try to rethink your model and introduce the first-level-citizen: PairingEntity - for the pairing object. It will then use many-to-one and one-to-many mapping which could give us more... e.g. improved querying like Subqueries... try to check these:

• How to create NHibernate HasManyToMany relation
• many-to-many with extra columns nhibernate
• Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships?