[nhusers] NHibernate HasOne with PropertyRef - Select N+1 issue

[Inge]

Hello,
following sample was tested on NH 4.0.4.4000, 5.1.2, 5.1.3.
I have following data model: Person can have one CurrentSpouse and this
current spouse can have a car. Data model is only for demonstration.

Person

public class BDOPerson
{
public virtual long? OID { get; set; }
public virtual string FirstName { get; set; }
public virtual string SureName { get; set; }
public virtual BDOCurrentSpouse CurrentSpouse { get; set; }
}

public class PersonMap : ClassMap<BDOPerson>
{
public PersonMap()
{
Table("T0001_Person");
Id(x => x.OID).Column("T0001OID").GeneratedBy.Increment();
Map(x => x.FirstName).Not.Nullable().Column("T0001Firstname");
Map(x => x.SureName).Not.Nullable().Column("T0001Surename");

References(x => x.CurrentSpouse).Nullable().Column(
"T0002_CurrentSpouseOID").Unique(); //HasOne opossite
}
}


CurrentSpouse

public class BDOCurrentSpouse
{
public virtual long? OID { get; set; }
public virtual string FirstName { get; set; }
public virtual string SureName { get; set; }

public virtual BDOPerson Person { get; set; }
public virtual BDOCar Car { get; set; }
}

public class CurrentSpouseMap : ClassMap<BDOCurrentSpouse>
{
public CurrentSpouseMap()
{
Table("T0002_CurrentSpouse");
Id(x => x.OID).Column("T0002OID").GeneratedBy.Increment();
Map(x => x.FirstName).Not.Nullable().Column("T0002Firstname");
Map(x => x.SureName).Not.Nullable().Column("T0002Surename");

HasOne(x => x.Person).PropertyRef(x => x.CurrentSpouse);
References(x => x.Car).Nullable().Column("T0003_CarOID");
}
}

Car

public class BDOCar
{
public virtual long? OID { get; set; }
public virtual string PlateId { get; set; }
}

public class CarMap : ClassMap<BDOCar>
{
public CarMap()
{
Table("T0003_Car");
Id(x => x.OID).Column("T0003OID").GeneratedBy.Increment();
Map(x => x.PlateId).Not.Nullable().Column("T0003PlateId");
}
}


Data fill:

using (var session = _sessionFactory.OpenSession())
{
var isolationLevel = IsolationLevel.ReadCommitted;
using (var transaction = session.BeginTransaction(isolationLevel))
{
var currentSpouse = new BDOCurrentSpouse { FirstName = "Ema",
SureName = "NiceLooking" };
var person = new BDOPerson { FirstName = "John", SureName =
"Doe", CurrentSpouse = currentSpouse};

var car = new BDOCar { PlateId = "1234567" };
session.Save(car);
car = new BDOCar { PlateId = "336699" };
session.Save(car);

currentSpouse.Car = car;
session.Save(currentSpouse);
session.Save(person);

transaction.Commit();
}
}


Selecting data - Issue 1


using (var session = _sessionFactory.OpenSession())
{
var isolationLevel = IsolationLevel.ReadCommitted;
using (var transaction = session.BeginTransaction(isolationLevel))
{
var v = session.Query<BDOPerson>().Fetch(x => x.CurrentSpouse).
ToList();
transaction.Commit();
}
}

Resulting queries of select

select bdoperson0_.T0001OID as t1_2_0_, bdocurrent1_.T0002OID as
t1_1_1_, bdoperson0_.T0001Firstname as t2_2_0_,
bdoperson0_.T0001Surename as t3_2_0_, bdoperson0_.T0002_CurrentSpouseOID
as t4_2_0_, bdocurrent1_.T0002Firstname as t2_1_1_,
bdocurrent1_.T0002Surename as t3_1_1_, bdocurrent1_.T0003_CarOID as
t4_1_1_ from T0001_Person bdoperson0_ left outer join
T0002_CurrentSpouse bdocurrent1_ on
bdoperson0_.T0002_CurrentSpouseOID=bdocurrent1_.T0002OID


SELECT bdoperson0_.T0001OID as t1_2_0_, bdoperson0_.T0001Firstname as
t2_2_0_, bdoperson0_.T0001Surename as t3_2_0_,
bdoperson0_.T0002_CurrentSpouseOID as t4_2_0_ FROM T0001_Person
bdoperson0_ WHERE bdoperson0_.T0002_CurrentSpouseOID=?


What is the reason for getting second select? This second select creates
N+1 issue - for each row from DB as result of first select, special second
select is done.

For testing purposes I tried to Fetch Person

using (var session = _sessionFactory.OpenSession())
{
var isolationLevel = IsolationLevel.ReadCommitted;
using (var transaction = session.BeginTransaction(isolationLevel))
{
var v = session.Query<BDOPerson>().Fetch(x => x.CurrentSpouse).
ThenFetch(x => x.Person).ToList();
transaction.Commit();
}
}

But this did not solved an issue:

select bdoperson0_.T0001OID as t1_2_0_, bdocurrent1_.T0002OID as
t1_1_1_, bdoperson2_.T0001OID as t1_2_2_, bdoperson0_.T0001Firstname as
t2_2_0_, bdoperson0_.T0001Surename as t3_2_0_,
bdoperson0_.T0002_CurrentSpouseOID as t4_2_0_,
bdocurrent1_.T0002Firstname as t2_1_1_, bdocurrent1_.T0002Surename as
t3_1_1_, bdocurrent1_.T0003_CarOID as t4_1_1_,
bdoperson2_.T0001Firstname as t2_2_2_, bdoperson2_.T0001Surename as
t3_2_2_, bdoperson2_.T0002_CurrentSpouseOID as t4_2_2_ from T0001_Person
bdoperson0_ left outer join T0002_CurrentSpouse bdocurrent1_ on
bdoperson0_.T0002_CurrentSpouseOID=bdocurrent1_.T0002OID left outer join
T0001_Person bdoperson2_ on
bdocurrent1_.T0002OID=bdoperson2_.T0002_CurrentSpouseOID

SELECT
bdoperson0_.T0001OID as t1_2_0_, bdoperson0_.T0001Firstname as t2_2_0_,
bdoperson0_.T0001Surename as t3_2_0_, bdoperson0_.T0002_CurrentSpouseOID
as t4_2_0_ FROM T0001_Person bdoperson0_ WHERE
bdoperson0_.T0002_CurrentSpouseOID=?

What should I do to prevent extra select for an object which is set as
HasOne in mapping?

Please note that I need PropertyRef in real application and OIDs in tables
in HasOne relation may not be same.

Thanks