one-to-one causing N+1 SQL Select statements

[rodrigo-web-developer]

I see this problems occurs in #2716 #3292 #850.

But I didnt see any solution for the problem.

I'm using NHibernate v5.5.2 and has an one-to-one relation mapped.

I try to add batch-size in every XML mapping file. It seems to work only with many-to-one relations.

I reproduced this problem in the repository: https://github.com/rodrigo-web-developer/many-to-one-problem

Basically, I have a Product which has a Category (many-to-one). Product has a tax configuration (ProductTax, which is one-to-one).

public class Category
{
    public long Id { get; set; }
    public string Description { get; set; }
}

public class Product
{
    public long Id { get; set; }
    public string Description { get; set; }
    public double Price { get; set; }
    public Category Category { get; set; }
}

public class ProductTax
{
    public long Id { get; set; }
    public double Tax1 { get; set; }
    public double Tax2 { get; set; }
    public double Tax3 { get; set; }
    public Product Product { get; set; }
}

The mapping files looks like:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   namespace="ManyToOneProblem.Entidades" assembly="ManyToOneProblem">

  <class batch-size="100" name="Category" lazy="false" >
    <id name="Id" column="id" type="long">
      <generator class="native" />
    </id>
    <property     name="Description"  type="string"  length="5000"  not-null="false"/>
  </class>

  <class batch-size="100" name="Product" lazy="false" >
    <id name="Id" column="id" type="long">
      <generator class="native" />
    </id>
    <!-- ... other properties .... -->

    <many-to-one  name="Category" column="CategoryId" fetch="join" lazy="false" class="Category" foreign-key="Fk_Product_Category" index="Idx_Product_Category"/>
  </class>

  <class batch-size="100" name="ProductTax" lazy="false" >
    <id name="Id" column="id" type="long">
      <generator class="foreign">
        <param name="property">Product</param>
      </generator>
    </id>
    <!-- ... other properties .... -->
    <one-to-one class="Product" foreign-key="fk_producttax_product" fetch="join" name="Product" 
                lazy="false" cascade="all" />

  </class>
</hibernate-mapping>

As you can see I added lazy="false" and batch-size="100" to all mappings.

I made a bunch of query examples. Consider that I have 10 products:

    Console.WriteLine("====================== QUERY PRODUCTS FIRST ======================");
    var queryProducts0 = sessionQuery.Query<Product>().ToList(); // query all
    Console.WriteLine("====================== END of query ======================");

    Console.WriteLine("====================== Starting query ======================");
    var queryProducts = sessionQuery.Query<ProductTax>().ToList(); // query all
    Console.WriteLine("====================== END of query ======================");

    Console.WriteLine("====================== Starting query 2 - QUERYOVER ======================");
    var queryProducts2 = sessionQuery.QueryOver<ProductTax>().List(); // query all
    Console.WriteLine("====================== END of query ======================");

    Console.WriteLine("====================== Starting query 3 - IQUERYABLE ======================");
    var queryProducts3 = from p in sessionQuery.Query<ProductTax>()
                            join pt in sessionQuery.Query<Product>() on p.Id equals pt.Id
                            select new ProductTax
                            {
                                Id = p.Id,
                                Product = pt,
                                Tax1 = p.Tax1,
                                Tax2 = p.Tax2,
                                Tax3 = p.Tax3,
                            };
        ;
    var list = queryProducts3.ToList();
    Console.WriteLine("====================== END of query ======================");

The first one (product only) results in 2 selects, so batch-size solved many-to-one problem.

====================== QUERY PRODUCTS FIRST ======================
NHibernate: select product0_.id as id1_1_, product0_.Description as descriptio2_1_, product0_.Price as price3_1_, product0_.CategoryId as categoryid4_1_ from public.Product product0_
NHibernate: SELECT category0_.id as id1_0_0_, category0_.Description as descriptio2_0_0_ FROM public.Category category0_ WHERE category0_.id in (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9);:p0 = 1 [Type: Int64 (0:0:0)], :p1 = 2 [Type: Int64 (0:0:0)], :p2 = 3 [Type: Int64 (0:0:0)], :p3 = 4 [Type: Int64 (0:0:0)], :p4 = 5 [Type: Int64 (0:0:0)], :p5 = 6 [Type: Int64 (0:0:0)], :p6 = 7 [Type: Int64 (0:0:0)], :p7 = 8 [Type: Int64 (0:0:0)], :p8 = 9 [Type: Int64 (0:0:0)], :p9 = 10 [Type: Int64 (0:0:0)]
====================== END of query ======================

The second one (producttax) gives the N+1 selects:

====================== Starting query ======================
NHibernate: select producttax0_.id as id1_2_, producttax0_.Tax1 as tax2_2_, producttax0_.Tax2 as tax3_2_, producttax0_.Tax3 as tax4_2_ from public.ProductTax producttax0_
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 11 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 12 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 13 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 14 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 15 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 16 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 17 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 18 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 19 [Type: Int64 (0:0:0)]
NHibernate: SELECT product0_.id as id1_1_1_, product0_.Description as descriptio2_1_1_, product0_.Price as price3_1_1_, product0_.CategoryId as categoryid4_1_1_, category1_.id as id1_0_0_, category1_.Description as descriptio2_0_0_ FROM public.Product product0_ left outer join public.Category category1_ on product0_.CategoryId=category1_.id WHERE product0_.id=:p0;:p0 = 20 [Type: Int64 (0:0:0)]
====================== END of query ======================

The third one, which is one of the suggested in linked issues (using QueryOver makes query create full join select):

====================== Starting query 2 - QUERYOVER ======================
NHibernate: SELECT this_.id as id1_2_2_, this_.Tax1 as tax2_2_2_, this_.Tax2 as tax3_2_2_, this_.Tax3 as tax4_2_2_, product2_.id as id1_1_0_, product2_.Description as descriptio2_1_0_, product2_.Price as price3_1_0_, product2_.CategoryId as categoryid4_1_0_, category3_.id as id1_0_1_, category3_.Description as descriptio2_0_1_ FROM public.ProductTax this_ inner join public.Product product2_ on this_.id=product2_.id left outer join public.Category category3_ on product2_.CategoryId=category3_.id
====================== END of query ======================

The last one, forcing join with LINQ work as well:

====================== Starting query 3 - IQUERYABLE ======================
NHibernate: select producttax0_.id as col_0_0_, product1_.id as col_1_0_, producttax0_.Tax1 as col_2_0_, producttax0_.Tax2 as col_3_0_, producttax0_.Tax3 as col_4_0_, product1_.id as id1_1_, product1_.Description as descriptio2_1_, product1_.Price as price3_1_, product1_.CategoryId as categoryid4_1_ from public.ProductTax producttax0_ inner join public.Product product1_ on  (product1_.id=producttax0_.id)
====================== END of query ======================

I have an IRepository<T>, which is a generic interface with public IQueryable<T> Query() method, it calls .Query from ISession. I cant change this code base to use QueryOver because it returns IQueryOver (NHibernate specific interface), not an IQueryable and I dont know the impact of changing ALL ENTITIES to use QueryOver. So I want to use Query() which works fine, but in the case of one-to-one relation it is a problem.

There is a solution for one-to-one relation? If not, there is any work around for this kind of problem?

[NandhaSaiS]

@rodrigo-web-developer No idea about this one, but a similar issue I am also facing. Can you help me with this issue #3615, and do you have anything about this issue? Please let me know