In single fetch running multiple select queries because of child mapping.

[NandhaSaiS]

I am using NHibernate v5.4.9 and FluentNHibernate v3.4.0.
In this version, I am using batch fetching to improve performance.

Configuration:

Fluently.Configure().Database(MsSqlConfiguration.MsSql2012
.Dialect("MyApp.Repositories.NH.MsSqlConfiguration2012, MyAppp.Repositories")
.AdoNetBatchSize(100).ShowSql()
.ExposeConfiguration(cfg =>
{
cfg.SetProperty(NHibernate.Cfg.Environment.GenerateStatistics, "true");
cfg.SetProperty(NHibernate.Cfg.Environment.DefaultBatchFetchSize, "100");
cfg.SetProperty(NHibernate.Cfg.Environment.BatchFetchStyle, BatchFetchStyle.Dynamic.ToString());
}).BuildSessionFactory();

Mapping:
OrderMap:

public OrderMap()
        {
            Table("Order");
            Id(x => x.Id);
            Map(x => x.Name);
            Map(x => x.ProjectId, "SiteId");
            HasMany(x => x.OrderItems).KeyColumn("OrderId").AsSet().Inverse().Not.LazyLoad();
            Map(x => x.OptionalWBSNumber);
           Map(x => x.UseWeight);
            References(x => x.Contractor).Column("ContractorId").Fetch.Join().NotFound.Ignore();
}

OrderItemMap:

public OrderItemMap()
        {
            Table("OrderItem");
            Id(x => x.Id);
            Map(x => x.OrderId);
           
            DiscriminateSubClassesOnColumn<int>("OrderItemDescriminator", 0);
        }
        public class SubOrderItemMap : SubclassMap<SubOrderItem>
       {
            public SubOrderItemMap()
            {
                DiscriminatorValue(1);

                Map(x => x.Name);
                Map(x => x.Address);
                References(x => x.ParentOrderItem).Column("OrderItemId").Fetch.Join().NotFound.Ignore();
                HasMany(x => x.OrderItemGroups).KeyColumn("OrderItemId").AsSet().Inverse().Not.LazyLoad();
         }
      }

Issue:

In this configuration, I am trying to retrieve all data in a single select statement (using batch fetching). However, while the order data is fetched in one select statement, each order item executes a separate select statement. Why are separate select statements being executed despite using batch size?. If there is any special reason for this one. If anyone knows, please let me know.

1. In the use of default_batch_fetch_size in the configuration, how does batch fetching work?
2. In NHibernate, is the N+1 problem resolved or not?
   Why are the parent and child entities getting separate select statements?
3. Is this configuration correct, or do I need to override anything in the mapping or configuration?
4. I need an explanation for using batch fetching. Why are the select statements run separately?

This is the output in Profiler:

@hazzik @fabiomaulo @fredericDelaporte

[rodrigo-web-developer]

@NandhaSaiS I had a similiar issue, can you provide the class definitions?

[NandhaSaiS]

Order Class:

namespace MyApp.Model.Orders
{
    [DataContract]
    public class Order : IdentifiableEntity<Order>, IEntityHasParent
    {
        private string _name;
        [DataMember]
        public virtual string Name
        {
            get { return _name; }
            set
            {
                SetPropertyValue("Name", ref _name, value);
            }
        }
       public virtual ICollection<OrderItem> InternalOrderItems
        {
            get { return OrderItems; }
            set
            {
                OrderItems = new OrderItemCollection(this);
                OrderItems.LazyFill(value);
            }
        }
        private int _projectId;
        [DataMember]
        public virtual int ProjectId
        {
            get { return _projectId; }
            set { SetPropertyValue("ProjectId", ref _projectId, value); }
        }

       OrderItemCollection _orderItems;
        [DataMember]
        public virtual OrderItemCollection OrderItems
        {
            get
            {
                return _orderItems;
            }
            set
            {
                _orderItems = value;
            }
        }
       private Contractor _contractor;
        [DataMember]
        public virtual Contractor ShippingContractor
        {
            get { return _contractor; }
            set
            {
                SetPropertyValue("ShippingContractor", ref _contractor, value);
            }
        }
     }
}

private bool _useWeight;
 [DataMember]
  public virtual bool UseWeight
   {
         get { return _useWeight; }
         set{
                if (OrderItems != null && OrderItems.Count > 0)
                {
                  UseWeight = value;
                }
                UseWeight  = false;
    }
}

OrderItem Class:

namespace MyApp.Model.Orders
{
    [KnownType(typeof(SubOrderItem ))]
    [DataContract]
    public class OrderItem : IdentifiableEntity<OrderItem>, IEntityHasParent, IOrderableEntity
    {

        [DataMember]
        public override int Id
        {
            get
            {
                return base.Id;
            }

            set
            {
                base.Id = value;
                NotifyPropertyChanged("ItemIdUI");
            }
        }

       private int _orderID;
        [DataMember]
        public virtual int OrderId
        {
            get {
              if(Order != null)
                 return Order.Id;
              else
                return _orderID; }
          set
            {
                _orderID = value;
            }
        }

    }
}

@rodrigo-web-developer hear the class.
And also, can you explain to me how the batch size and default batch fetch are working?

[rodrigo-web-developer]

@NandhaSaiS, sorry, but your code results in compiling errors. What is the definition of TowerOrderItemMap class and what the definition of SubOrderItem class?

I tried to figure out what is the definition of each class, but it has some props that I didnt know how to implement.

[NandhaSaiS]

SubOrderItem Class:

namespace MyApp.Model.Orders
{
   [DataContract]
   public class SubOrderItem : OrderItem
   {
     private string _name;
      [DataMember]
      public virtual string Name
      {
          get { return _name; }
          set
          {
              SetPropertyValue("Name", ref _name, value);
          }
      }
    
    private string _address;
      [DataMember]
      public virtual string Address
      {
          get { return _address; }
          set
          {
              SetPropertyValue("Address", ref _address, value);
          }
      }

     private SubOrderItem _parentOrderItem;
      [IgnorePropertyOnBeginEditAttribute]
      [ExcludeEntityAttribute]
      [DataMember]
      public virtual SubOrderItem ParentOrderItem
      {
         get { return _parentOrderItem; }
         set { SetPropertyValue("ParentOrderItem", ref _parentOrderItem, value); }
      }
    }

    public virtual IList<OrderItemGroup> InternalOrderItemGroups
      {
         get { return OrderItemGroups; }
         set
         {
            OrderItemGroups = new EntityCollection<OrderItemGroup>(this);
            OrderItemGroups.LazyFill(value);
         }
      }

       private EntityCollection<OrderItemGroup> _orderItemGroups;
      [DataMember]
      public virtual EntityCollection<OrderItemGroup> OrderItemGroups
      {
         get { return _orderItemGroups; }
         set
         {
            _orderItemGroups = value;
            OrderItemGroups_CollectionChanged(null, null);
            if (_orderItemGroups != null)
            {
               if (EntityBase.IsCopyingProperties == false)
               {
                  OrderItemGroups.CollectionChanged += OrderItemGroups_CollectionChanged;
                  OrderItemGroups.EntityPropertyChanged += OrderItemGroups_EntityPropertyChanged;
               }
            }

         }
      }
}

@rodrigo-web-developer, Sorry, I forgot to add. and I changed the mapping and model also. Can you recheck once?
I found one thing in this Order.cs file i have one condition like this:

Before:

private bool _useWeight;
 [DataMember]
  public virtual bool UseWeight
   {
         get { return _useWeight; }
         set{
                if (OrderItems != null && OrderItems.Count > 0)
                {
                  UseWeight = value;
                }
                UseWeight  = false;
              }
    }

After:

private bool _useWeight;
 [DataMember]
  public virtual bool UseWeight
   {
         get { return _useWeight; }
         set{
                if (OrderItems != null)
                {
                  UseWeight = value;
                }
                UseWeight  = false;
              }
    }

If I change this, I'm getting two select statements only. Why? Do you have any idea about this?