Fluent Nhibernate Many to Many relationship issue

Question

I have a situation where I have three tables in our database. One to contain customers, one contains customer groups, and then a table to link them together.

I am having issues where my nHibernate query wont join them correctly, and I am not sure if I am missing something.

Mapping for the Customer Groups

public CustomerGroupMapping() {
      Table("tbCustomerGroups");
      // TODO: Revisit Lazy loading
      //LazyLoad();
      Id(x => x.customerGroupId)
        .GeneratedBy.Identity()
        .Column("Customer_Group_ID");
      Map(x => x.customerGroup)
        .Column("Customer_Group")
        .Length(50);
      Map(x => x.editDisabled)
        .Column("EditDisabled")
        .Not.Nullable();

      HasManyToMany<CustomerModel>(x => x.customers)
        .Table("tbCustomerGroupsRelationship")
        .ParentKeyColumn("Customer_Group_ID")
        .ChildKeyColumn("Customer_ID")
        .Inverse();
    }

Mapping for the Customers

public CustomerMapping() {
  Table("tbCustomers");
  // TODO: Revisit Lazy load
  LazyLoad();
  Id(x => x.customerId)
    .GeneratedBy.Identity()
    .Column("Customer_ID");
  Map(x => x.customerTypeId)
    .Column("Customer_Type_ID")
    .Not.Nullable()
    .Precision(10);

  // A Customer has many users
  HasMany<UserModel>(x => x.users)
    .KeyColumn("Customer_ID");

  // A Customer can belong to many groups
  HasManyToMany<CustomerGroupModel>(x => x.groups)
    .Table("tbCustomerGroupsRelationship")
    .ParentKeyColumn("Customer_ID")
    .ChildKeyColumn("Customer_Group_ID")
    .Not.Inverse();
}

The issue seems to be that when I get a customer, I want to see that customer's groups customers. ( IE give me customer 10, get that customer group which has customer 10, and then give me all other customers in that group)

Is there a way to change these mappings to have this correctly load without generating a ton of select statements because that is what Log4net is showing me.

What you are trying to do is querying. NHibernate provides several ways, including QueryOver, HQL. Entity model does not optimize queries to that level. — Low Flying Pelican, Feb 13 '15 at 22:55

score 1 · Answer 1 · edited May 23 '17 at 12:27

What you should use is batch-fetching.

19.1.5. Using batch fetching

In a nutshell, this optimization setting will reduce lot of queries into few batches. It imporves performance and supports querying over one root entity (no need for FETCH strategies as SELECT)

Extend your class, and collection mapping with .BatchSize(xx). Classes should have this mapping:

public CustomerGroupMapping() 
{
    Table("tbCustomerGroups");
    BatchSize(25);
    ...

public CuustomerMapping() 
{
    Table("tbCustomers");
    BatchSize(25);
    ...

Collections should be extended

HasManyToMany<CustomerModel>(x => x.customers)
   ...
   .BatchSize(25)

HasManyToMany<CustomerGroupModel>(x => x.groups)
   ...
   .BatchSize(25)

Also check these for similar stuff:

Also, my suggestion would be - do not use many-to-many. I would prefer pairing table being first level object... maybe check these:

Fluent Nhibernate Many to Many relationship issue

1 Answers1

Linked