How do I use Fluent Nhibernate many-to-many for optimal performance?

Question

I have a product table that has a many-to-many relation to itself (using a two-column many-to-many table) and I have set it up in Fluent NHibernate with the following code:

public class ProductConfiguration : ClassMap<Product>
{
    public ProductConfiguration()
    {
        Table("Product");
        Id(p => p.Id).GeneratedBy.Guid();

        Map(p => p.Name).Not.Nullable().Length(254);
        Map(p => p.Description).Not.Nullable().Length(1000);
        Map(p => p.CreatedAt).Not.Nullable();

        HasManyToMany(p => p.CrossSell)
            .Table("ProductCrossSell")
            .ParentKeyColumn("Id")
            .ChildKeyColumn("ProductId"); 
    }
}

My MVC application has two pages that uses this setup:

• Index - Uses a generic repository GetAll method to display all products.
• Detail - Uses a generic repository GetById method to display one product and any related cross sell products setup in the many-to-many realation.

It looks like NHibernate is set to LazyLoad the many-to-many by default so when I fire up the application and watch it in profiler I can see that it does LazyLoad the many-to-many with the following alert "Use of implicit transactions is discouraged".

1. How do I get rid of this alert? I couldn't find any information on how to wrap a LazyLoad inside a transaction to get rid the alert. Is it even possible?
2. Is there a way to not lazyload this by telling NHibernate that whenever I ask for GetById make sure to join the tables a get everything in one query? I tried using .Fetch.Join() in the many-to-many mapping but that also affected my GetAll query which now displays a joined result set as well which is incorrect.

What is the best apprach for this kind of simple scenario?

Thanks

Answer 1

All collections are lazily loaded in NHibernate by default.

You must be triggering loading with a call of some kind (maybe even with the debugger watches)

Answer 2

1. The way to get rid of the warning is to access the object graph and fully populate the UI elements inside a single transaction.

2. Not by configuration. You can create an HQL query that eager fetches the association and use that query for a specific view. I would stick with lazy loading and not make that optimization unless needed. The HQL would be:

---

return session.CreateQuery("from ProductionConfiguration pc join fetch pc.CrossSell where pc.Id = ?")
    .SetGuid(0, id)
    .List<ProductConfiguration>();