nhibernate
In single fetch running multiple select queries because of child mapping.
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);
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 SubOrderItem : SubclassMap<SubOrderItem>
{
public TowerOrderItemMap()
{
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.
- In the use of default_batch_fetch_size in the configuration, how does batch fetching work?
- In NHibernate, is the N+1 problem resolved or not? Why are the parent and child entities getting separate select statements?
- Is this configuration correct, or do I need to override anything in the mapping or configuration?
- I need an explanation for using batch fetching. Why are the select statements run separately?
This is the output in Profiler:
@hazzik @fabiomaulo @fredericDelaporte
@NandhaSaiS I had a similiar issue, can you provide the class definitions?
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?
@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.
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?